Re: Nested transactions RFC

Tom,

reading my message again and your response, I see, that some points
were a bit unclear.

On Fri, 10 May 2002 13:12:21 +0200, I wrote:
|if it is acceptable for subtransactions to use up transaction numbers,
Of course, "use up" is nonsense, as it sounds like "use all
available"; this should have been "use" or "draw from the pool of".
Should have listened better to my English teacher :-)

On Sat, 11 May 2002 11:51:37 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:
>Manfred Koizar <mkoi-pg(at)aon(dot)at> writes:
>> TransactionId GetParentXact(TransactionId xnum) uses pg_subtrans to
>> find the parent transaction of xnum.
>
>This is not only extremely expensive, but in practice would cause
>infinite recursion: any attempt to validate the commit state of a
>row in pg_subtrans would result in a recursive attempt to search
>pg_subtrans. I don't think we can do table access from inside the
>tqual.c routines.

I wrote:
|It needs an additional system table
|pg_subtrans (child XactId PRIMARY KEY, parent XactId).

But no! "Table" is not the correct word for what I mean. I rather
want something living outside transactions and not accessed via normal
SQL statements. It is to be handled by highly specialized and
optimized routines, because fast access is crucial for the whole
proposal. That's why I called "a really fast implementation of
pg_subtrans" a challenge. I had pg_clog in mind, but didn't find the
right words.

>A practical implementation, which would cost little except tuple header
>space (and yes I know that won't make you happy) would require 3 fields
:-)

>instead of 2 for both the min and the max:
> transaction ID
> subtransaction ID
> command ID
This was my first attempt. I've dismissed it for several reasons.

>First check the transaction ID: if aborted or (in-progress and not
>mine), tuple is not visible.
I agree up to here.

>Next, if the subtransaction ID is not
>zero, similarly check it.
Now imagine
BEGIN 1;
BEGIN 2;
BEGIN 3;
INSERT tup3;
COMMIT 3;
ROLLBACK 2;
COMMIT 1;

Then in tup3 we would have xid==1 and subxid==3, both of which are
committed, but nevertheless tup3 is invisible, because xact 2 aborted.

>Finally, if xid and sub-xid are both mine,
>the command ID has to be checked.
>
>In this scenario, subtransactions commit or abort by marking their
>pg_clog entries, but no one else will care until the parent transaction
>commits. So there is no extra state anywhere except for the stack
>of active transaction numbers inside each backend.
A *stack* of _active_ transaction numbers is not sufficient, we need
the whole *tree* of _all_ transactions belonging to the current top
level transaction. This is, want I wanted to model in my pg_subtrans
"table". And pg_subtrans cannot be a private structure, because it
has to be inspected by other transactions too (cf. example above).

>PS: unfortunately, tuple validity checking is only a small part of what
>has to be done to support subtransactions. The really nasty part is
>in fixing error recovery inside the backend so that (most) errors can
>be dealt with by aborting only the innermost subtransaction.
Is this really related to subtransactions? The current behaviour is,
that an error not only aborts the offending command, but the whole
(top level) transaction. My proposal doesn't change anything
regarding this. Though I agree it would be desirable to have finer
grained error handling.

You have quoted only small parts of my posting. Do you agree to the
rest? Or didn't you bother to comment, because you considered the
whole proposal refuted by your counter-arguments? I'll be fine either
way, I just want to know.

BTW, there's something missing from my visibility checks:
| if (IsCurrentTransaction(xmin)) {
here we have to add "or xmin is one of my (grand)*parents".

And of course, it would be nice to have named savepoints:
BEGIN;
BEGIN foo;
BEGIN bar;
...
ROLLBACK foo;
COMMIT; -- top level transaction

Servus
Manfred