Nested transactions in postgresql 8.2?

Question

I'm working on scripts that apply database schema updates. I've setup all my SQL update scripts using start transaction/commit. I pass these scripts to psql on the command line.

I now need to apply multiple scripts at the same time, and in one transaction. So far the only solution I've come up with is to remove the start transaction/commit from the original set of scripts, then jam them together inside a new start transaction/commit block. I'm writing perl scripts to do this on the fly.

Effectively I want nested transactions, which I can't figure out how to do in postgresql.

Is there any way to do or simulate nested transactions for this purpose? I have things setup to automatically bail out on any error, so I don't need to continue in the top level transaction if any of the lower ones fail.

Answer 1

Well you have the possibility to use nested transactions inside postgresql using SavePoints.

Take this code example:

CREATE TABLE t1 (a integer PRIMARY KEY);

CREATE FUNCTION test_exception() RETURNS boolean LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO t1 (a) VALUES (1);
   INSERT INTO t1 (a) VALUES (2);
   INSERT INTO t1 (a) VALUES (1);
   INSERT INTO t1 (a) VALUES (3);
   RETURN TRUE;
EXCEPTION
   WHEN integrity_constraint_violation THEN
      RAISE NOTICE 'Rollback to savepoint';
      RETURN FALSE;
END;$$;

BEGIN;

SELECT test_exception();
NOTICE:  Rollback to savepoint
 test_exception 
----------------
 f
(1 row)

COMMIT;

SELECT count(*) FROM t1;
 count 
-------
     0
(1 row)

Maybe this will help you out a little bit.

Answer 2

I've ended up 'solving' my problem out of band - I use a perl script to re-work the input scripts to eliminate their start transaction/commit calls, then push them all into one file, which gets it's own start transaction/commit.

Answer 3

http://www.postgresql.org/docs/8.3/interactive/tutorial-transactions.html