I was thinking about the nested transaction problem, and I came across an
interesting insight. While it is true you could use dblink to perform db
operations outside the transaction (which could be useful for logging, etc.)
what is lacking is a way to roll back the internal transactions when the
parent rolls back.
It also occured to me that the main problem with nested transactions is that
the hard part is this inherited rollback/commit, esp. with MVCC which places
constraints on how one could look at managing these commit/rollbacks without
paying huge performance costs even where, as in the majority of cases, this
feature is not used. I am assuming that part of the problem is how the
visibility/transaction information is handled via MVCC. Is my understanding
correct?
My final, albeit half-baked, conclusion is that one of the things that would
make nested transactions MUCH easier would be a two-phase commit (2PC)
framework which would be stored on the transaction level. Something like a
transaction status storage which contains the following information: Status
(in progress, committed, rolled back, pending commit as in 2PC), and
"depends on xid" where you can then have the pending commit become
'committed' when transaction xid is commited. Again this is just off the
top of my head.
Also an 2PC framework if added into the protocol would allow for true nested
transactions via DBLink.
Best Wishes,
Chris Travers
