Re: why do we need two snapshots per query?

On Fri, Nov 11, 2011 at 10:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Can't we arrange to retain the snapshot used for parse
>> analysis / planning and reuse it for the portal that we create just
>> afterwards?
>
> Possibly, but what if planning takes a long time?  Also, I think you're
> ignoring the extended-query-protocol scenario, where it would be a whole
> lot harder to justify keeping a snapshot from Parse through Bind and
> Execute.

In the extend query protocol scenario, it seems to me that keeping the
snapshot would be both wrong and a bad idea. It would be wrong
because the user will (I think) expect the query can see all rows that
were marked as committed prior to Execute message. It would be a bad
idea because we'd have to keep that snapshot advertised for the entire
time between Parse and Execute, even if the client was sitting there
doing nothing for a long time, which would hold back RecentGlobalXmin.

But in the simple query scenario, I think it's fine. Even if query
planning does take a long time, it's a single operation from a user
perspective. If the user sends a query and gets an answer back ten
seconds later, they don't know (and shouldn't care) whether that
happened because the query took nine seconds to plan and one second to
execute, or one second to plan and nine seconds to execute, or 50ms to
plan and 9950ms to execute. For the scenario you're talking about to
be a problem, someone would have to be expecting a query to see rows
from a transaction that committed *after* the query was sent - based,
presumably, on the knowledge, that the execution snapshot wouldn't be
taken immediately, and that the concurrent transaction would commit
meanwhile. But such a practice is flagrantly unsafe anyway, because
any optimization that makes query planning faster could break it. And
I'm not prepared to guarantee that we're never going to speed up the
optimizer.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company