nested transactions

I am going to work on nested transactions for 7.4.

My goal is to first implement nested transactions:

BEGIN;
SELECT ...
BEGIN;
UPDATE;
COMMIT;
DELETE;
COMMIT;

and later savepoints (Oracle):

BEGIN;
SELECT ...
SAVEPOINT t1;
UPDATE;
SAVEPOINT t2;
DELETE;
ROLLBACK TO SAVEPOINT t2;
COMMIT;

I assume people want both.

As an implementation, I will hack xact.c to create a transaction status
stack so when you do a BEGIN inside a transaction, it saves the
transaction status, the transaction block status, and perhaps the
command counter. A COMMIT restores these values.

I also plan to modify the on commit/abort actions. On a subtransaction
commit, little has to be done, but on an ABORT, you must execute any
abort actions required by that subtransaction _and_ remove any on commit
actions for the subtransaction. There will need to be some code
reorganization because some on commit/abort activity assumes only one
transaction can be in process. A stack will need to be added in those
cases.

And finally, I must abort tuple changes made by the aborted
subtransaction. One way of doing that is to keep all relation id's
modified by the transaction, and do a sequential scan of the tables on
abort, changing the transaction id's to a fixed aborted transaction id.
However, this could be slow. (We could store tids if only a few rows
are updated by a subtransaction. That would speed it up considerably.)

Another idea is to use new transaction id's for the subtransactions, and
update the transaction id status in pg_clog for the subtransactions, so
that there is no transaction id renumbering required. One problem with
this is the requirement of updating all the clog transaction id statuses
atomically. One way to do that would be to do parent/child dependency
in clog so that if a child is looked up and it is marked as "in
process", a check could be done against the parent. Once the outer
transaction is committed/aborted, those subtransactions could be updated
so there would be no need to reference the parent any longer. This
would increase the clog size per transaction from 2 bits to 4 bytes
(two bits for status, 30 bits for offset to parent).

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073