CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
peter.petrov
peter.petrov

Reputation: 39477

Transactions not working OK in a PostgreSQL 11 stored procedure

Postgres version:
PostgreSQL 11.0 on x86_64-pc-linux-gnu,
compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

I have this stored procedure as shown below. This is just a test. In that procedure I have 2 transactions:

Also, yb.print_now is a simple function which is just logging (inserting) messages to another table.

When I run this stored procedure I am expecting the updates and the logging of messages done by the first transaction to be persisted in the database, even though the 2nd transaction failed.

But this is not what happens, both transactions seem to be rolled back.

  1. Why is this? Am I doing something wrong?

And 2 more questions which are very important to me. :

  1. When an error occurs (say like on line marked ***) and when control reaches/jumps to the EXCEPTION block, I have the feeling that the transaction I was in is already rolled back before I even reach the EXCEPTION block. So in the exception block I cannot do ROLLBACK or COMMIT or anything related to the transaction. Is that feeling correct?

  2. Say I want to commit all the stuff done, despite of the error, is there a way I can do that?
    That's exactly what I want here. The error is an error... OK, but I want everything which happened before I got the error to get committed.

How do I do this in Postgres 11?

    CREATE OR REPLACE PROCEDURE yb.test123()
     LANGUAGE plpgsql
    AS $procedure$ 

    DECLARE
        var_cnt int;
        c int;
    BEGIN

        START TRANSACTION; --- 1 ---

        raise notice '001.';

        PERFORM yb.print_now('===> 0010.');

        var_cnt = 0;

        update yb.mbb
        set the_price = the_price + 1   
        where 
        the_id = 23164;

        raise notice '002.';
        PERFORM yb.print_now('===> 0020.');

        raise notice '003.';
        PERFORM yb.print_now('===> 0030.');

        update yb.mbb
        set the_price = the_price + 1 
        where 
        the_id = 23164;

        COMMIT; --- 1 ---

        START TRANSACTION; --- 2 --- 

        c = cast('###a1e3Z' as int);  --- *** --- 

        raise notice '004.';
        PERFORM yb.print_now('===> 0040.');

        update yb.mbb
        set the_price = the_price + 1 
        where 
        the_id = 23164;

        -- insert into yb.mbb(the_id)
        -- values (23164); -- this will throw duplicate PK error

        raise notice '005.';
        PERFORM yb.print_now('===> 0050.');

        COMMIT; --- 2 ---



    EXCEPTION
        WHEN OTHERS THEN

        raise notice 'We are in the exception block now.';
        -- ROLLBACK;
        -- COMMIT;

        RETURN;

    END

    $procedure$;

Upvotes: 0

Views: 820

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

The error happens right at the start of your procedure, in the statement

START TRANSACTION;

As the documentation says:

A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command.

That should answer your first question.

As to the second, when you are in the exception branch, you have effectively rolled back the subtransaction that started with the BEGIN that belongs to the EXCEPTION clause (or after the last COMMIT). You are still in the transaction though, so you can issue COMMIT and ROLLBACK.

To your third question: No, there is no way to commit “everything up to the last exception”. You could only have that by wrapping every statement in a BEGIN ... EXCEPTION ... END block, but that would seriously hurt your performance (apart from making your code unreadable).

Use BEGIN ... EXCEPTION ... END blocks judiciously whenever you expect that a statement could fail.

Upvotes: 1

Related Questions