Re: nested xacts and phantom Xids - Mailing list pgsql-patches

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