PostgreSQL: Rolling back a transaction within a plpgsql function?

10
votes

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;
4
postgresqltransactionsplpgsql
Can you post an example of a function that doesn't rollback everything as you would expect? PL/pgSQL functions do execute within the transaction context of the calling statement, but a BEGIN..EXCEPTION block can modify that behavior. Without seeing an example, it's hard to give the proper advice. – Matthew Wood
Edited to add an example. Thanks. – jamieb
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. – Matthew Wood
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. – Frank Heikens
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. – jamieb

4 Answers

16
votes

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

4
votes

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.

1
votes

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).

1
votes

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