Re: nested transactions

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Yes, locking is one possible solution, but no one likes that. One hack
> lock idea would be to create a subtransaction-only lock, so if you see
> the special 4-th xact state (about to be committed as part of a
> subtransaction) you have to wait on that lock (held by the backend
> twiddling the xact bits), then look again. That basically would
> serialize all the bit-twiddling for subtransactions. I am sure I am
> going to get a "yuck" from the audience on that one,

You sure are.

> but I am not sure
> how long that bit twiddling could take. Does xact twiddle every cause
> I/O?

Yes, if the page of pg_clog you need to touch is not currently in a
buffer. With a large transaction you might have hundreds of
subtransactions, which could take an unpleasantly long time to mark
all committed.

What's worse, I think the above proposal requires a *single* lock for
this purpose (if there's more than one, how shall the requestor know
which one to block on?) --- so you are serializing all transaction
commits that have subtransactions, with only one able to go through at
a time. That will really, really not do; the performance will be way
worse than the chaining idea we discussed before.

> You could store the backend slot id in pg_clog rather than the parent
> xid and look up the status of the outer xid for that backend slot. That
> would allow you to use 2 bytes, with a max of 16k backends.

This is also a bad idea, because backend slot ids are not stable (by the
time you look in PG_PROC, the slot may be occupied by a new, unrelated
backend process).

> But still, you have an interesting idea of just setting the bit to be "I
> am a child".

That bit alone doesn't help; you need to know *whose* child.

AFAICS, the objection to putting parent xact IDs into pg_clog is
basically a performance issue: bigger clog means more I/O. This is
surely true; but the alternatives proposed so far are worse.

regards, tom lane