PostgreSQL Transactions – Handling Nested Transactions in PostgreSQL 9.3

postgresqlpostgresql-9.3transaction

What behaviour would PostgreSQL display if for example the script below were called

BEGIN;
SELECT * FROM foo;
INSERT INTO foo(name) VALUES ('bar');
BEGIN; <- The point of interest
END;

Would PostgreSQL discard the second BEGIN or would a commit be implicitly decided on and then run the BEGIN END block at the end as a separate transaction?

Best Answer

What you would need is a so called "autonomous transaction" (a feature provided by oracle). At this point this is not possible in PostgreSQL yet. However, you can use SAVEPOINTs:

BEGIN;
INSERT ...
SAVEPOINT a;
some error;
ROLLBACK TO SAVEPOINT a;
COMMIT;

It is not entirely an autonomous transaction - but, it allows you get "every transaction" right. You can use it to achieve the thing you expect from autonomous transactions.

Otherwise there are no other reasonable solution at this point.

Related Question