Here's below my procedure. Inside u find a loop. Target is if any of single loop execution will throw exception i don't want to stop the process and want to continue and commit each loop query which was executed succesfully. Therefore i placed exception catch inside loop. As u can see i also got a commit at the end and some begin/end blocks. My question is whether did i correctly or maybe i should put additional commit inside begin/end inside the loop (just after execute mysql;)? Thank you in advance.
CREATE OR REPLACE PROCEDURE myProc()
LANGUAGE plpgsql
AS $procedure$
declare
mysql text;
tb_name text;
myTables CURSOR for
SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='dist';
begin
begin
call DoSomeJob();
for tb in myTables loop
tb_name := tb;
begin
mysql := format('delete from %I where somecol=2', tb_name);
execute mysql;
exception
when others then
raise notice '% %', SQLERRM, SQLSTATE;
end ;
end loop;
call doOtherJob();
exception
when others then
raise notice 'The transaction is in an uncommittable state. '
'Transaction was rolled back';
raise notice '%: %', SQLSTATE, sqlerrm;
end ;
commit;
end;
$procedure$
;
UPDATE:
CREATE OR REPLACE PROCEDURE myProc()
LANGUAGE plpgsql
AS $procedure$
declare
mysql text;
tb_name text;
myTables CURSOR for
SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='dist';
begin
begin
call DoSomeJob();
exception
when others then
raise notice 'The transaction is in an uncommittable state. '
'Transaction was rolled back';
raise notice '%: %', SQLSTATE, sqlerrm;
end;
RAISE EXCEPTION 'ERROR test';
for tb in myTables loop
tb_name := tb;
begin
mysql := format('delete from %I where somecol=2', tb_name);
execute mysql;
exception
when others then
raise notice '% %', SQLERRM, SQLSTATE;
end ;
end loop;
begin
call doOtherJob();
exception
when others then
raise notice 'The transaction is in an uncommittable state. '
'Transaction was rolled back';
raise notice '%: %', SQLSTATE, sqlerrm;
end;
commit;
end;
$procedure$;
CodePudding user response：
I explain you how to works PostgreSQL transactions in procedures. Firstly
- begin is a - "begin transaction";
- end is a - "commit transaction"
And when you using transaction in another transaction (sub transactions) when your first level transaction rollbacked then all sub transactions also will be rollbacked. For example:
begin --block call2
begin --block call1
call1;
exception
when others then
raise notice 'error call1'
end;
call2;
exception
when others then
raise notice 'error call2'
end;
Here when call2 failed then call1 also be failed. Because block call1 is in the block call2
And on your procedure - when doOtherJob() will be fail then all your inserted data will fail to. For solving this problem you can write your procedure as below:
CREATE OR REPLACE PROCEDURE myProc()
LANGUAGE plpgsql
AS $procedure$
declare
mysql text;
tb_name text;
myTables CURSOR for
SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='dist';
begin
call DoSomeJob();
for tb in myTables loop
tb_name := tb;
begin
mysql := format('delete from %I where somecol=2', tb_name);
execute mysql;
exception
when others then
raise notice '% %', SQLERRM, SQLSTATE;
end ;
end loop;
begin
call doOtherJob();
exception
when others then
raise notice 'The transaction is in an uncommittable state. '
'Transaction was rolled back';
raise notice '%: %', SQLSTATE, sqlerrm;
end;
commit;
end;
$procedure$;
You can set for block DoSomeJob() to the same logic.
CodePudding user response：
When using procedures in PostgreSQL, especially about transactions, you need to know and understand some details well. I will now explain to you the important details and you will understand how it works.
begin - this is start transaction (always)
exception
end - this is not always commit-transaction, because if exception is created then end will be rollback-transaction if else end will be commit-transaction
And remember that: each block end affects only its own block begin
At the same time, pay attention to this:
During rollback process, all other begin-exception-end blocks which is under between the begin block and the exception block will be rollback.
Please execute this block and view result: select * from table test1
DO
$body$
begin
CREATE TABLE test1 (
id int4 NOT NULL,
CONSTRAINT test1_pk PRIMARY KEY (id)
);
begin -- block 1
insert into test1 (id) values (1);
exception
when others then
raise notice 'exception ';
end;
begin -- block 2
insert into test1 (id) values (error);
exception
when others then
raise notice 'exception ';
end;
begin -- block 3
insert into test1 (id) values (3);
exception
when others then
raise notice 'exception ';
end;
begin --block 4
begin -- block 5
insert into test1 (id) values (5);
exception
when others then
raise notice 'exception ';
end;
insert into test1 (id) values (error);
exception
when others then
raise notice 'exception ';
end;
END;
$body$
LANGUAGE 'plpgsql';
block 1- success execute (success insert 1) - commitblock 2- execute with error (no inserted record) - rollbackblock 3- execute success (success insert 3) - commitblock 5- execute success (success insert 5) - commitblock 4- execute with error (no inserted record) - rollback
Since block 5 is inside block 4 so block 5 already rollbacked
Result: select * from test1
1
3