Skip site navigation (1) Skip section navigation (2)

nested-xacts cursors (was Re: Performance with new nested-xacts code)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: nested-xacts cursors (was Re: Performance with new nested-xacts code)
Date: 2004-07-01 14:19:08
Message-ID: 26844.1088691548@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> Well, my opinion is that cursors and other resources should at least be
> usable from a inner subtransaction in its parent -- because if that
> can't be done we are wasting some of the benefits, because we can't just
> "stick everything in a subtransaction" to be able to retry if it fails.  
> It is a pity that we can't roll back FETCH or lo_close() but at least we
> can keep them declared/open across a subtransaction commit.

AFAICS we can't allow an inner transaction to use a cursor that was
declared in an outer transaction, because if the inner transaction fails
then it's not just a matter of the FETCH not rolling back; the
subtransaction abort will restore state in the bufmgr and other places
that is simply inconsistent with the state of the cursor's plantree.

If we don't restore bufmgr state at subxact commit, I think that it
would work to do

	begin;
		begin;
		declare cursor c ...;
		end; -- cursor, bufmgr state NOT changed here
	fetch from c;
	...

It seems though that we might have a lot of problems with figuring out
which subsystems ought to restore state at subxact commit and which not.

Another point is that this will NOT work:

	begin;
		begin;
		declare cursor c ...;
		end; -- cursor, bufmgr state NOT changed here

		begin;
		fetch from c;
		abort; -- oops, wrong state restored here

so the rule would have to be something like "cursors can only be
touched by the highest subxact nesting level they have ever been
visible to".  Yuck.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Justin CliftDate: 2004-07-01 15:04:55
Subject: Re: Bug with view definitions?
Previous:From: Alvaro HerreraDate: 2004-07-01 13:52:07
Subject: Re: Performance with new nested-xacts code

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group