To: Greg Stark <gsstark@mit.edu>
Cc: Andrew Sullivan <ajs@crankycanuck.ca>, pgsql-sql@postgresql.org
Subject: Re: row-attribute in EXPLAIN-output doesn't match count(*) 
In-reply-to: <8764ypf8hy.fsf@stark.xeocode.com> 
References: <200504141838.14705.andreak@officenet.no>
	<425EA496.2030501@archonet.com>
	<200504141921.38476.andreak@officenet.no>
	<20050414173358.GB9027@phlogiston.dyndns.org>
	<8764ypf8hy.fsf@stark.xeocode.com>
Comments: In-reply-to Greg Stark <gsstark@mit.edu>
	message dated "14 Apr 2005 15:44:57 -0400"
Date: Thu, 14 Apr 2005 19:26:09 -0400
Message-ID: <27509.1113521169@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Greg Stark <gsstark@mit.edu> writes:
> This is presumably because of the long-standing issue that Postgres takes the
> snapshot as soon as the BEGIN is issued.

No, we don't set the snapshot until the first DML query is issued.  This
is critical for serializable transactions: you have to be able to take
locks before the snapshot is frozen.

There are at least three interesting events involved:
    1  BEGIN command issued
    2  First lock taken (presumably as a consequence of another command)
    3  Transaction snapshot taken (ditto; might be a different command)

We have to start the transaction no later than event #2 since there has
to be something to hold the lock.  But it'd be easy enough to decouple
this from BEGIN, and it'd be good enough to solve the "COMMIT;BEGIN"
problem.

Which of these three times do you think now() ought to correspond to?
I recall having argued that it should be event #3 since that corresponds
to the database snapshot you see.  100% backwards compatibility would
require setting now() at event #1, but will anyone weep if we change that?

			regards, tom lane