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
– Nick Barnes, Commented Jan 16, 2018 at 9:15
you can achieve it with dblinks, but I would rethink the design better — Vao Tsun
– Vao Tsun, Commented Jan 16, 2018 at 9:26

Laurenz Albe · Accepted Answer · 2020-05-11 07:21:06Z

37

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, 2020 at 7:21

answered Jan 16, 2018 at 9:16

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rahul Gour Over a year ago

posted it before testing , thanks for this information , i have done testing. it's working fine.

UmaShankar Over a year ago

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.

Laurenz Albe Over a year ago

@UmaShankar You can use dblink for that.

EngineerSpock Over a year ago

@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?

Laurenz Albe Over a year ago

@GujaratSantana I have added a quotation from the documentation.

|

godot · Accepted Answer · 2020-01-23 17:27:26Z

4

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

answered Jan 23, 2020 at 17:27

godot

1,56916 silver badges34 bronze badges

1 Comment

Sergey Menshov Over a year ago

You cannot use transaction statements like SAVEPOINT, COMMIT or ROLLBACK in a function.

Leonid Pavlov · Accepted Answer · 2022-08-29 14:15:48Z

3

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

edited Aug 29, 2022 at 14:15

Leonid Pavlov

90910 silver badges15 bronze badges

answered Aug 22, 2022 at 15:05

Sidharth Priyadarshi

416 bronze badges

Collectives™ on Stack Overflow

how to use COMMIT and ROLLBACK in a PostgreSQL function

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related