Re: Autonomous subtransactions

On Mon, Dec 19, 2011 at 05:52:40PM +0200, Marti Raudsepp wrote:
> On Sun, Dec 18, 2011 at 10:28, Gianni Ciolli
> <gianni(dot)ciolli(at)2ndquadrant(dot)it> wrote:
> >  http://wiki.postgresql.org/wiki/Autonomous_subtransactions
> >
> > It is meant to be an ongoing project, requesting comments and
> > contributions, rather than a conclusive document.
>
> In addition to what Jim Nasby said, this proposal seems a bit
> inflexible. In particular:
> 1. It limits us to exactly 2 autonomous transactions at any time (the
> main one and the "subtransaction").

Thanks for pointing this out; it shows me that the only example I
provided was too basic, so that I have added a second one, plus one
related remark.

The spec doesn't actually impose a limit of only 2 total transactions:
if we have the capability to pause the current transaction (T0) to
start a new one (T1), and then resume T0 after the conclusion of T1,
then we can apply that capability more than once in the same
transaction, resulting in a sequence of transactions T1, T2, ..., Tn
all subordinate to T0, possibly interspersed by statements from
T0. Also, if T0 is a function then it is possible peek at the (very)
near future and eliminate any empty interlude of T0 between T_k and
T_(k+1).

> 2. There's no reason why two autonomous transactions should have a
> "main / sub" relationship. They are autonomous -- they should not
> depend on the state of the "outer" transaction.

COMMIT/ROLLBACK of any of T1,T2,... does not depend on COMMIT/ROLLBACK
of T0; the parent transaction exists only to simplify the logical
model. It can happen that T0 contains zero statements, so that it is
very similar to a sequence of independent transactions.

The advantage to me is that we allow autonomous transactions, but
there is always one "master" transaction, so that it looks more like a
generalisation of the existing model as opposed to something radically
different (and therefore less likely to happen).

The behaviour is indeed similar to the one that can be obtained with
dblink: it allows for instance to have an "umbrella" transaction which
doesn't modify the database, and has the only purpose of executing
other transactions, which commit or rollback independently of each
other and of the umbrella transaction.

With respect to dblink, this spec brings a simpler syntax, which
doesn't require managing a second connection to the same db, and one
significant difference: you reuse the current session instead of
opening a new one (with some caveats, as noted in the Wiki).

Regards,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni(dot)ciolli(at)2ndquadrant(dot)it | www.2ndquadrant.it