Question 114649 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Continuation of execution and commit for correctly executed statments

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$;
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search