From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alessandro Baretta <a.baretta@barettadeit.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Suspending SELECTs
Date: Tue, 17 Jan 2006 15:19:31 -0500
Message-ID: <21016.1137529171@sss.pgh.pa.us> (raw)
In-Reply-To: <43CD4BD0.9090807@barettadeit.com>
References: <43CB71AC.20203@barettadeit.com>
<9766.1137433907@sss.pgh.pa.us>
<43CD4BD0.9090807@barettadeit.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, a.baretta@barettadeit.com
Subject: Re: Suspending SELECTs
In-Reply-To: <21016.1137529171@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox