Re: start of transaction (was: Re: [PERFORM] Help with count(*))

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> Probably the latest time we can start the transaction is ath the start
> of executor step after the first statement in a transaction is planned
> and optimized.

The transaction has to exist before it can take locks, so the above
would not fly.

A complete example of what we have to think about is:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE foo;
UPDATE foo ... -- or in general a SELECT/UPDATE/INSERT/DELETE query
... etc ...

The transaction snapshot *must* be set at the time of the first query
(here, the UPDATE). It obviously can't be later, and it cannot be
earlier either, because in this sort of example you need the requested
locks to be taken before the snapshot is set.

The transaction must be created (as observed by other backends, in
particular VACUUM) not later than the LOCK statement, else there is
nothing that can own the lock. In principle though, the effects of
BEGIN and perhaps SET could be strictly local to the current backend,
and only when we hit a LOCK or query do we create the transaction
externally.

In practice the problem we observe is clients that issue BEGIN and then
go to sleep (typically because of poorly-designed autocommit behavior in
interface libraries). Postponing externally-visible creation of the
transaction to the first command after BEGIN would be enough to get
around the real-world issues, and it would not require code changes
nearly as extensive as trying to let other stuff like SET happen
"before" the transaction starts.

There isn't any compelling implementation reason when to freeze the
value of now(). Reasonable options are
1. at BEGIN (current behavior)
2. at transaction's external creation
3. at freezing of transaction snapshot
#1 and #2 are actually the same at the moment, but could be decoupled
as sketched above, in which case the behavior of #2 would effectively
become "at first command afte BEGIN".

In the previous thread:
http://archives.postgresql.org/pgsql-hackers/2003-03/msg01178.php
I argued that now() should be frozen at the time of the transaction
snapshot, and I still think that that's a defensible behavior.

regards, tom lane