To: Alessandro Baretta <a.baretta@barettadeit.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Suspending SELECTs 
In-reply-to: <43CD4BD0.9090807@barettadeit.com> 
References: <43CB71AC.20203@barettadeit.com> <9766.1137433907@sss.pgh.pa.us>
	<43CD4BD0.9090807@barettadeit.com>
Comments: In-reply-to Alessandro Baretta <a.baretta@barettadeit.com>
	message dated "Tue, 17 Jan 2006 20:56:00 +0100"
Date: Tue, 17 Jan 2006 15:19:31 -0500
Message-ID: <21016.1137529171@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Alessandro Baretta <a.baretta@barettadeit.com> writes:
> * When the cursor state is pushed back to the backend, no new
> transaction is instantiated, but the XID of the original transaction
> is reused. In the MVCC system, this allows us to achieve a perfectly
> consistent view of the database at the instant the original
> transaction started, unless a VACUUM command has been executed in the
> meantime, in which case I would lose track of tuples which would have
> been live in the context of the original transaction, but have been
> updated or deleted and later vacuumed; however, this does not bother
> me at all.

> Is this not a viable solution?

No.  I'm not interested in "solutions" that can be translated as "you
may or may not get the right answer, and there's no way even to know
whether you did or not".  That might be acceptable for your particular
application but you certainly can't argue that it's of general
usefulness.

Also, I can't accept the concept of pushing the entire execution engine
state out to the client and then back again; that state is large enough
that doing so for every few dozen rows would yield incredibly bad
performance.  (In most scenarios I think it'd be just as efficient for
the client to pull the whole cursor output at the start and page through
it for itself.)  Worse yet: this would represent a security hole large
enough to wheel West Virginia through.  We'd have no reasonable way to
validate the data the client sends back.

Lastly, you underestimate the problems associated with not holding the
locks the cursor is using.  As an example, it's likely that a btree
indexscan wouldn't successfully restart at all, because it couldn't find
where it had been if the index page had been split or deleted meanwhile.
So not running VACUUM is not enough to guarantee the query will still
work.

			regards, tom lane