Thread: Re: [PATCHES] nested xacts and phantom Xids

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> As with the bufmgr.c original patch, I don't really know how to test
> that this actually works.  I fooled around with printing what it was
> doing during a subtrans commit/abort, and it seems OK, but that's about
> it.  In what situations can a transaction roll back with a nonzero
> reference count in a local buffer?

You need an active cursor, eg

    begin;
    declare c cursor for select * from tenk1;
    fetch 1 in c;
    ... now you've got an open buffer refcount to some page of tenk1

I forgot to mention to you that that code didn't work at all, btw.
I have fixed some of the problems in my local version but there's still
a fairly large issue, which is what exactly we think the semantics of a
cursor declared in a subtransaction ought to be.  With bufmgr set up to
consider open reference counts as a bug, we cannot hold such a cursor
open past subtrans commit.

One possible approach is to consider subxact commit the same as main
xact commit as far as cursors are concerned: materialize anything
declared WITH HOLD, close anything declared without.

The other theory we could adopt is that cursors stay open till main xact
commit; this would imply not releasing buffer refcounts at subxact
commit, plus any other resources needed by the cursor.  We're already
holding locks that way and it probably wouldn't be a big change to make
bufmgr work the same.  I'm not sure that there are any other resources
involved, other than the Portal memory which we already handle properly.

The first approach is a lower-risk path; I'm not sure if the second one
might have some hidden gotchas.  It seems like the second one would be
more flexible though.  Any opinions which to pursue?

Oh, there's another point: what happens if an outer xact level declares
a cursor, which is then FETCHed from by a subtransaction?  At minimum we
have the problem that this could change the set of buffer pins held,
which breaks the present bufmgr solution entirely.  It gets even more
interesting if you are of the opinion that subtransaction failure should
cause the effects of the FETCH to be undone --- we have no way to do
that at all, because there's no mechanism for saving/restoring the state
of an entire execution plan tree.  We might have to prohibit
subtransactions from touching outer-level cursors, at least for 7.5.
This would in turn make it a bit questionable whether there's any point
in letting cursors propagate up out of subtransactions...

            regards, tom lane

On Tue, Jun 29, 2004 at 06:59:20PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > As with the bufmgr.c original patch, I don't really know how to test
> > that this actually works.  [...]
>
> I forgot to mention to you that that code didn't work at all, btw.

Bad news, I guess.

> The other theory we could adopt is that cursors stay open till main xact
> commit; this would imply not releasing buffer refcounts at subxact
> commit, plus any other resources needed by the cursor.  We're already
> holding locks that way and it probably wouldn't be a big change to make
> bufmgr work the same.  I'm not sure that there are any other resources
> involved, other than the Portal memory which we already handle properly.

Well, AFAIR originally I had thought that refcounts should be held at
subtrans commit; you suggested that there was no reason for a subtrans
to keep a buffer refcount and that was it.  I think the open cursor is a
good reason why the count should be kept; it appears less useful if you
can't use the cursor anywhere out of the level that created it.

> Oh, there's another point: what happens if an outer xact level declares
> a cursor, which is then FETCHed from by a subtransaction?  At minimum we
> have the problem that this could change the set of buffer pins held,
> which breaks the present bufmgr solution entirely.  It gets even more
> interesting if you are of the opinion that subtransaction failure should
> cause the effects of the FETCH to be undone --- we have no way to do
> that at all, because there's no mechanism for saving/restoring the state
> of an entire execution plan tree.

Hmm ... yes, this could be very ugly indeed, but I haven't even looked
at the executor code so I can't comment.  Are executor nodes copyable?

Oh, and I've been playing with large objects and I've encountered bugs
elsewhere.  I'll look at it with the new patch you just posted.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Vivir y dejar de vivir son soluciones imaginarias.
La existencia está en otra parte" (Andre Breton)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Hmm ... yes, this could be very ugly indeed, but I haven't even looked
> at the executor code so I can't comment.  Are executor nodes copyable?

Nope, and even if we had support for that the executor tree per se
is just the tip of the iceberg.  There's also indexscan status, SRF
function internal state, yadda yadda.  I think the odds of doing
something with all that stuff for 7.5 are exactly zero ... we'd better
define a stopgap behavior.

> Oh, and I've been playing with large objects and I've encountered bugs
> elsewhere.  I'll look at it with the new patch you just posted.

Wouldn't surprise me, we've not looked at that yet either.

I do feel that we have enough things working that we should commit to
nested transactions for 7.5.  There will be some things that we have to
restrict, such as cursors and perhaps large objects.  But it's surely
better than no subtransactions at all.

            regards, tom lane

Added to TODO, just so we don't forget later:

    * Use a phantom command counter for nested subtransactions to reduce
          tuple overhead


---------------------------------------------------------------------------

Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > Hmm ... yes, this could be very ugly indeed, but I haven't even looked
> > at the executor code so I can't comment.  Are executor nodes copyable?
>
> Nope, and even if we had support for that the executor tree per se
> is just the tip of the iceberg.  There's also indexscan status, SRF
> function internal state, yadda yadda.  I think the odds of doing
> something with all that stuff for 7.5 are exactly zero ... we'd better
> define a stopgap behavior.
>
> > Oh, and I've been playing with large objects and I've encountered bugs
> > elsewhere.  I'll look at it with the new patch you just posted.
>
> Wouldn't surprise me, we've not looked at that yet either.
>
> I do feel that we have enough things working that we should commit to
> nested transactions for 7.5.  There will be some things that we have to
> restrict, such as cursors and perhaps large objects.  But it's surely
> better than no subtransactions at all.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

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