Continuation of execution and commit for correctly executed statments

user41Sb 0
Viewed 36

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$;
postgresql
2 Answers

I explain you how to works PostgreSQL transactions in procedures. Firstly

  1. begin is a - "begin transaction";
  2. 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.

Ramin Faracov0

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';
  1. block 1 - success execute (success insert 1) - commit
  2. block 2 - execute with error (no inserted record) - rollback
  3. block 3 - execute success (success insert 3) - commit
  4. block 5 - execute success (success insert 5) - commit
  5. block 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
Ramin Faracov0
Related