how to use COMMIT and ROLLBACK in a PostgreSQL function

postgresql postgresql-10
Rahul Gour picture Rahul Gour · Jan 16, 2018 · Viewed 26k times · Source

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.

Answer

Laurenz Albe picture Laurenz Albe · Jan 16, 2018

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.

Related questions

What is the most suitable hibernate.dialect for postgresql 10.5?

I am looking at hibernate documentation here: https://docs.jboss.org/hibernate/orm/5.0/javadocs/org/hibernate/dialect/package-summary.html to try to find which hibernate.dialect to use inside persistence.xml It does not mention a class for Postgres 10.x. …

Read More
Postgres Psycopg2 Create Table

i´m new to Postgres and Python. I´m trying to create a simple user table but i don´t know why it isn´t creating. The error message doesn´t appear, #!/usr/bin/python import psycopg2 try: conn = psycopg2.…

Read More
PostgreSQL error: set-returning functions are not allowed in CASE

I tried to run this query in PostgreSQL 10: select e.errordescription, CASE WHEN e.reworkempid is not null THEN get_empname(e.reworkempid) else null end from error_log_gs e where e.qcworkpackageid=3012175 and e.logno=1 Got the error: …

Read More