X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 66033D1B563 for ; Sun, 16 Nov 2003 14:50:51 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 59708-05 for ; Sun, 16 Nov 2003 10:50:24 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 35F88D1D20B for ; Sun, 16 Nov 2003 10:50:15 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hAGEoB19026135; Sun, 16 Nov 2003 09:50:11 -0500 (EST) To: Hannu Krosing Cc: Neil Conway , Will LaShell , Christopher Browne , "pgsql-hackers@postgresql.org" Subject: Re: start of transaction (was: Re: [PERFORM] Help with count(*)) In-reply-to: <1068983887.17671.10.camel@fuji.krosing.net> References: <200311141251.38786.mallah@trade-india.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> Comments: In-reply-to Hannu Krosing message dated "Sun, 16 Nov 2003 13:58:08 +0200" Date: Sun, 16 Nov 2003 09:50:11 -0500 Message-ID: <26134.1068994211@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/840 X-Sequence-Number: 47128 Hannu Krosing 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