Nested Transactions

Posted Jul 8, 2004 2:38 UTC (Thu) by alvherre (subscriber, #18730) [Link]

Is "nested transactions" the same as "autonomous transactions"? For example, if you are in transaction A and begin transaction B, commit B - will B stay committed even if A is rolled back? Or is it that you can roll back to a certain step in a transaction and proceed from there?

The latter. The feature is much more like SQL-standard's savepoints than autonomous transactions, as you name them. In fact, there's no way to "commit" a nested transaction; it will be truly committed only when the parent transaction commits.

If the former, this is great news for database bigots such as myself. It allows, for example, logging within triggers, great for error logs, recovery, and debugging. It allows more business rules to be at the database level - where they should be, of course.

You are right, but sadly there are lots of things that are not there yet (and probably they won't be on the upcoming release, because we are now in feature freeze). For example, PL/pgSQL does not at present have support for exceptions --- the function aborts and you can't do anything else from within it. You can retain control of the transaction afterwards if you happened to run the function itself within a nested transaction, but this is less than ideal.

It will undoubtly be better on a future release, and we may yet see exceptions support in PL/pgSQL for the current release, but don't count on it.

Anyway, if you have not been following closely the latest PostgreSQL news, there are a lot of new/better things for this release. One of the things that I'm excited over is the new PL/Perl --- you can now actually do pretty much everything PL/pgSQL can do, including returning sets, writing triggers, and other cool stuff.