how to use COMMIT and ROLLBACK in a PostgreSQL function

Question

I am using three insert statements, and if there is an error in the third statement, I want to rollback the first and the second one. If there is no way to do this, please tell me a different approach to handle this in PostgresqQL.

If I use COMMIT or ROLLBACK, I get an error.

CREATE OR REPLACE FUNCTION TEST1 ()
   RETURNS VOID
   LANGUAGE 'plpgsql'
   AS $$
BEGIN 

    INSERT INTO table1 VALUES (1);

    INSERT INTO table1 VALUES (2);

    INSERT INTO table1 VALUES ('A');
    COMMIT;
EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
END;$$;

The above code is not working; COMMIT and ROLLBACK are not supported by PostgreSQL functions.

This is how Postgres functions behave by default (i.e. if any of your statements fails, none will be committed). — Nick Barnes, Jan 16 '18 at 09:15
you can achieve it with dblinks, but I would rethink the design better — Vao Tsun, Jan 16 '18 at 09:26

Laurenz Albe · Accepted Answer · 2020-05-11T07:21:06.963

36

You cannot use transaction statements like SAVEPOINT, COMMIT or ROLLBACK in a function. The documentation says:

In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK.

Ex negativo, since functions are not procedures that are invoked with CALL, you cannot do that in functions.

The BEGIN that starts a block in PL/pgSQL is different from the SQL statement BEGIN that starts a transaction.

Just remove the COMMIT from your function, and you have the solution: since the whole function is always run inside a single transaction, any error in the third statement will lead to a ROLLBACK that also undoes the first two statements.

edited May 11 '20 at 07:21

answered Jan 16 '18 at 09:16

Laurenz Albe

209,280
17
206
263

1

posted it before testing , thanks for this information , i have done testing. it's working fine. – Rahul Gour Jan 16 '18 at 10:29
1

But in my case I need to use commit after insertion of data for pstgresql_fdw operations. So, could you suggest how to commit the data after insertion. – UmaShankar Jul 07 '19 at 11:14
1

@UmaShankar You can use dblink for that. – Laurenz Albe Jul 08 '19 at 05:20
1

@LaurenzAlbe, so if I need to update some rows in one table and by the if statement decide either should I remove the corresponding rows in another table or rollback (I mean depending on some procedural logic) then there is no way to combine if with rollback inside a function? – EngineerSpock Feb 20 '20 at 11:04
1

this is interesting, any reference from this statement > You cannot use transaction statements like SAVEPOINT, COMMIT or ROLLBACK in a function. perhaps from the documentation ? @LaurenzAlbe – Gujarat Santana May 11 '20 at 04:27
2

@GujaratSantana I have added a quotation from the documentation. – Laurenz Albe May 11 '20 at 07:22

score 4 · Answer 2 · answered Jan 23 '20 at 17:27

Compared to other SQL languages, you should think that Postgres always takes care of the commit/rollback in case of error implicitly when you are inside a transaction.

Here is what the doc is saying:

Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

CREATE OR REPLACE FUNCTION TEST1 ()
   RETURNS VOID
   LANGUAGE 'plpgsql'
   AS $$
BEGIN 

    INSERT INTO table1 VALUES (1);

    INSERT INTO table1 VALUES (2);

    INSERT INTO table1 VALUES ('A');
    COMMIT;
EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
END;$$;

You cannot use transaction statements like SAVEPOINT, COMMIT or ROLLBACK in a function. — Sergey Menshov, Apr 22 '22 at 07:54

score 2 · Answer 3 · edited Aug 29 '22 at 14:15

For transaction control we use PROCEDURE (From postgresql11) instead of FUCTION.
FUNCTION does not support transaction inside the function. This is the main difference between FUNCTION and PROCEDURE in PostgreSQL.

Your code should be:

    CREATE OR REPLACE PROCEDURE TEST1 ()
    RETURNS VOID
    LANGUAGE 'plpgsql'
    AS $$
    BEGIN 

    INSERT INTO table1 VALUES (1);

    INSERT INTO table1 VALUES (2);

    INSERT INTO table1 VALUES ('A');
    COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    END;$$;

how to use COMMIT and ROLLBACK in a PostgreSQL function

3 Answers3

Linked