pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Hannu Krosing <hannu@tm.ee>
Cc: Neil Conway <neilc@samurai.com>
Cc: Will LaShell <will@lashell.net>
Cc: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: start of transaction (was: Re: [PERFORM] Help with count(*))
Date: Sun, 16 Nov 2003 09:50:11 -0500
Message-ID: <26134.1068994211@sss.pgh.pa.us> (raw)
In-Reply-To: <1068983887.17671.10.camel@fuji.krosing.net>
References: <200311141251.38786.mallah@trade-india.com>
	<m3ad6zkpgj.fsf@wolfe.cbbrowne.com>
	<1068831807.2540.44.camel@fuji.krosing.net>
	<3FB52FA7.8060508@lashell.net>
	<4987.1068842326@sss.pgh.pa.us>
	<87isllbcys.fsf@mailbox.samurai.com>
	<1068983887.17671.10.camel@fuji.krosing.net>

Hannu Krosing <hannu@tm.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



view thread (13+ messages)  latest in thread

Message-ID: <26134.1068994211@sss.pgh.pa.us>
Permalink:  ../26134.1068994211@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/26134.1068994211@sss.pgh.pa.us

 · 

reply

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, hannu@tm.ee, neilc@samurai.com, will@lashell.net, cbbrowne@acm.org, pgsql-hackers@postgresql.org
  Subject: Re: start of transaction (was: Re: [PERFORM] Help with count(*))
  In-Reply-To: <26134.1068994211@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