PostgreSQL: Rolling back a transaction within a plpgsql function?

Question

Coming from the MS SQL world, I tend to make heavy use of stored procedures. I'm currently writing an application uses a lot of PostgreSQL plpgsql functions. What I'd like to do is rollback all INSERTS/UPDATES contained within a particular function if I get an exception at any point within it.

I was originally under the impression that each function is wrapped in it's own transaction and that an exception would automatically rollback everything. However, that doesn't seem to be the case. I'm wondering if I ought to be using savepoints in combination with exception handling instead? But I don't really understand the difference between a transaction and a savepoint to know if this is the best approach. Any advice please?

CREATE OR REPLACE FUNCTION do_something(
         _an_input_var int
                ) RETURNS bool AS $$
        DECLARE
                _a_variable int;
        BEGIN
                INSERT INTO tableA (col1, col2, col3)
                        VALUES (0, 1, 2);

                INSERT INTO tableB (col1, col2, col3)
                        VALUES (0, 1, 'whoops! not an integer');

                -- The exception will cause the function to bomb, but the values 
                -- inserted into "tableA" are not rolled back.    

                RETURN True;
END; $$ LANGUAGE plpgsql;

Dartmoor

I'm running 8.4.2, created tableA and B with three int columns each, run your example (with ";" removed at the end of INSERT INTO tableB line) and it bombed. I checked both tables and they were both empty. I even added some debug code between the two to verify that the record was there before it failed, then it was gone after the failure. – Carlenacarlene

Each function will be in it's own transaction, it's impossible with the current example to add a new record in tableA and have an error on tableB. No way this will happen, impossible. And you don't need savepoints as mentioned, just do some propper testing and see how things are working. In PostgreSQL, everything is about data integrity, you don't have to worry about that. – Senator

Matthew -- You're correct. I overly reduced the complexity of my code snippet and in doing so, eliminated the apparent problem. I will continue to test it to try and locate the problem. Thanks for your time and assistance. – Dartmoor

Answer 1

A function does represent a transaction. You do not have to wrap a function in BEGIN/COMMIT.

Glochidiate

That is not true for PostgreSQL. A single SQL statement, when not executed as a part of explicit transaction (is not executed between BEGIN and COMMIT/END), is executed as a single transaction. But if such statement calls multiple functions, then all the functions are executed in one transaction. Also, when you have multiple statement transaction (explicit BEGIN, COMMIT) and those statements call some procedures, all of those will be executed in the single transactions. As others have said: savepoints are the way to go. – Hypesthesia

@Jacek: Joshua is right, a function does represent it's own transaction. You don't need a savepoint to rollback both inserts, if one fails, they both will fail. – Senator

@Frank: But that is because of the outer transaction. The inserts in other function called in the same statement or previous statements in the transaction will also fail. And you cannot rollback the inserts from the function without rolling back other results of the outer transaction, unless you use the savepoints. Function body is always executed within a transaction, but it is not a transaction by itself. There is just no way to call a function not starting a transaction. – Hypesthesia

Answer 2

You can't use commit or rollback command into the function, but you can use your function into a committed transaction,

BEGIN TRANSACTION; SELECT do_something(); COMMIT;

This SQL script only commits if there are no exceptions in do_something, then, it will rolling back the transaction of the function.

Crenulation

Answer 3

The docs say this:

A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.

They give examples too.

Edit:

You need to wrap a transaction in BEGIN and COMMIT commands.

a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands

Sardinia

I disagree that the docs are clear. The description of a "savepoint" sounds exactly what I know a "transaction" to be. Are savepoints atomic? – Dartmoor

As I understand it, they're just markers in a transaction that you can roll back to. The whole transaction is atomic; until it's committed no change is visible to any other transaction. – Sardinia

Answer 4

Savepoints can be used to emulate nested transactions. Because a postgresql transaction is a sequence of statements that will either be applied or discarded, savepoints can mark points within that sequence that allow rolling back to.

Since true nested transactions are not supported, this is your best bet (and a good one at that).

Lachesis

Answer 5

You can use SAVEPOINT and ROLLBACK TO SAVEPOINT inside a transaction:

    BEGIN;
        INSERT INTO table1 VALUES (1);
        SAVEPOINT my_savepoint;
        INSERT INTO table1 VALUES (2);
        ROLLBACK TO SAVEPOINT my_savepoint;
        INSERT INTO table1 VALUES (3);
    COMMIT;

Code above does not work in PL/PgSQL code, for example, procedure or function. But you can use workaround with "substransaction":

    DO $TEST$
    BEGIN
        -- here you can write DDL commands, for example, adding or deleting a table or its section
        -- and/or
        -- here you can write DML commands that modify data in tables and, thus, check the operation of triggers
     
        -- rollback all test queries
        raise exception using errcode = 'query_canceled';
     
    EXCEPTION WHEN query_canceled THEN
        --don't do anything
    END
    $TEST$;

Horn

Answer 6

A drawback of user-defined functions is that they cannot execute transactions. you cannot start a transaction, and commit or rollback it. PostgreSQL 11 introduced stored procedures that support transactions. To define a new stored procedure, you should use the create procedure statement.

For more You can also see this

Kassa