A conversation with Andrew Sullivan led me to the following idea:

We have a number of frontends that like to issue BEGIN immediately
after COMMIT; so that if the client does nothing for awhile after
finishing one transaction, the backend nonetheless sees it as being
in a transaction.  This creates problems for VACUUM since the open
transaction prevents it from removing dead tuples.

This has been known for a long time, and so far the hackers' opinion
has been that those frontends are broken and should be fixed.  But
AFAIK they are not getting fixed.  And some important frontends are
involved (I believe JDBC and DBI, and perhaps also ODBC, behave
this way).  Maybe it's time to think about a fix on the backend side.

It seems to me that it'd be fairly easy to make BEGIN cause only
a local state change in the backend; the actual transaction need not
start until the first subsequent command is received.  It's already
true that the transaction snapshot is not frozen at BEGIN time, but
only when the first DML or DDL command is received; so this would
have no impact on the client-visible semantics.  But a BEGIN-then-
sleep-for-awhile client wouldn't interfere with VACUUM anymore.

The other thing that could be thought about here is when to freeze the
value of now().  Currently now() is frozen when BEGIN is received.
We could keep doing that, but it seems to me it would make more sense
to freeze now() when the transaction snapshot is established.  In a
very real sense, the transaction snapshot defines "when the transaction
starts" --- so shouldn't now() agree?

If we did both of these things, then the negatives of doing an early
BEGIN would pretty much vanish, and we'd not need to complain that these
client libraries are broken.

Comments?

			regards, tom lane


Fri, 28 Mar 2003 23:13:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Changing behavior of BEGIN...sleep...do something...COMMIT
On Fri, 28 Mar 2003, Tom Lane wrote:

> It seems to me that it'd be fairly easy to make BEGIN cause only
> a local state change in the backend; the actual transaction need not
> start until the first subsequent command is received.
[snip]
> In a very real sense, the transaction snapshot defines "when the
> transaction starts" --- so shouldn't now() agree?
> 
> If we did both of these things, then the negatives of doing an early
> BEGIN would pretty much vanish, and we'd not need to complain that these
> client libraries are broken.
> 
> Comments?

Both ideas sound like a win to me.

Jon


Sat, 29 Mar 2003 04:54:05 +0000 (UTC)
Jon Jensen <jon@endpoint.com>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT
On Fri, Mar 28, 2003 at 11:13:28PM -0500, Tom Lane wrote:

> The other thing that could be thought about here is when to freeze the
> value of now().  Currently now() is frozen when BEGIN is received.
> We could keep doing that, but it seems to me it would make more sense
> to freeze now() when the transaction snapshot is established.  In a
> very real sense, the transaction snapshot defines "when the transaction
> starts" --- so shouldn't now() agree?

Yes, I saw this in the code and wondered about the inconsistency.  I
agree the timestamp should be taken at the same time as the snapshot.

While at this, what do you think about adding the necessary variables
to make now('transaction') and now('query') possible?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"En las profundidades de nuestro inconsciente hay una obsesiva necesidad
de un universo lógico y coherente. Pero el universo real se halla siempre
un paso más allá de la lógica" (Irulan)


Sat, 29 Mar 2003 12:46:51 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT
Alvaro Herrera wrote:
> On Fri, Mar 28, 2003 at 11:13:28PM -0500, Tom Lane wrote:
> 
> > The other thing that could be thought about here is when to freeze the
> > value of now().  Currently now() is frozen when BEGIN is received.
> > We could keep doing that, but it seems to me it would make more sense
> > to freeze now() when the transaction snapshot is established.  In a
> > very real sense, the transaction snapshot defines "when the transaction
> > starts" --- so shouldn't now() agree?
> 
> Yes, I saw this in the code and wondered about the inconsistency.  I
> agree the timestamp should be taken at the same time as the snapshot.
> 
> While at this, what do you think about adding the necessary variables
> to make now('transaction') and now('query') possible?

TODO already has:

	* Add now("transaction|statement|clock") functionality

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Sat, 29 Mar 2003 11:56:07 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Alvaro Herrera wrote:
>> While at this, what do you think about adding the necessary variables
>> to make now('transaction') and now('query') possible?

> TODO already has:
> 	* Add now("transaction|statement|clock") functionality

I know that's what we agreed to awhile back, but I've realized that that
would be a foolish way to do it.

The problem is that such a function would have to be marked VOLATILE,
which would prevent its use in indexscan qualifiers.  The volatility
labeling is a property of the function, not of the particular argument
it's passed, so we'd have to label it for the worst-case behavior.

Accordingly, it's a bad idea to invent now('clock') and make it the
same function as the other flavors.  We could get away with making
now('transaction') and now('statement') ---- but the argument for this
was consistency, and that argument pretty much falls flat if those two
are one function while clock time is something else.

So I'm back in the camp of thinking three separate parameterless
functions are the way to do it.  We already know what now() does,
and we're not going to change it --- anyone want to propose names
for the other two?

			regards, tom lane


Sat, 29 Mar 2003 17:45:18 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT 
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Accordingly, it's a bad idea to invent now('clock') and make it the
> same function as the other flavors.  We could get away with making
> now('transaction') and now('statement') ---- but the argument for this
> was consistency, and that argument pretty much falls flat if those two
> are one function while clock time is something else.
> 
> So I'm back in the camp of thinking three separate parameterless
> functions are the way to do it.  We already know what now() does,
> and we're not going to change it --- anyone want to propose names
> for the other two?

Maybe clock_time() and statement_time(), with transaction_time() an
alias for now() (if that's seemed necessary)?

A little verbose perhaps, but clear...

-Doug


29 Mar 2003 18:40:52 -0500
Doug McNaught <doug@mcnaught.org>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT
Doug McNaught wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
> > Accordingly, it's a bad idea to invent now('clock') and make it the
> > same function as the other flavors.  We could get away with making
> > now('transaction') and now('statement') ---- but the argument for this
> > was consistency, and that argument pretty much falls flat if those two
> > are one function while clock time is something else.
> > 
> > So I'm back in the camp of thinking three separate parameterless
> > functions are the way to do it.  We already know what now() does,
> > and we're not going to change it --- anyone want to propose names
> > for the other two?
> 
> Maybe clock_time() and statement_time(), with transaction_time() an
> alias for now() (if that's seemed necessary)?

Agreed on the need to not use args for now().

We already have CURRENT_TIMESTAMP.  Would CLOCK_TIMESTAMP,
TRANSACTION_TIMESTAMP, and STATEMENT_TIMESTAMP make sense, with
CURRENT_TIMESTAMP being the same as TRANSACTION_TIMESTAMP?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073


Sat, 29 Mar 2003 21:22:09 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Doug McNaught wrote:
>> Maybe clock_time() and statement_time(), with transaction_time() an
>> alias for now() (if that's seemed necessary)?

I could go with that ...

> We already have CURRENT_TIMESTAMP.  Would CLOCK_TIMESTAMP,
> TRANSACTION_TIMESTAMP, and STATEMENT_TIMESTAMP make sense, with
> CURRENT_TIMESTAMP being the same as TRANSACTION_TIMESTAMP?

... or that.  But, please, *not* the weird no-parentheses syntax that
CURRENT_TIMESTAMP has.  Make it clock_timestamp() and so on.

			regards, tom lane


Sat, 29 Mar 2003 21:38:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT 
Hi,

Tom Lane wrote:
> It seems to me that it'd be fairly easy to make BEGIN cause only
> a local state change in the backend; the actual transaction need not
> start until the first subsequent command is received.  It's already
> true that the transaction snapshot is not frozen at BEGIN time, but
> only when the first DML or DDL command is received; so this would
> have no impact on the client-visible semantics.  But a BEGIN-then-
> sleep-for-awhile client wouldn't interfere with VACUUM anymore.

As I can remember, already, when autocommit off transaction begin with 
first DML or DDL command. May be better change client to use autocommit 
off mode?

-- 
Olleg Samojlov


Mon, 31 Mar 2003 11:08:33 +0400
Olleg Samojlov <olleg@telecom.mipt.ru>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT
On Fri, 28 Mar 2003, Tom Lane wrote:

> It seems to me that it'd be fairly easy to make BEGIN cause only
> a local state change in the backend; the actual transaction need not
> start until the first subsequent command is received.  It's already
> true that the transaction snapshot is not frozen at BEGIN time, but
> only when the first DML or DDL command is received; so this would
> have no impact on the client-visible semantics.  But a BEGIN-then-
> sleep-for-awhile client wouldn't interfere with VACUUM anymore.

What about serializable mode?  Wouldn't that break it?


Mon, 31 Mar 2003 11:06:08 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> On Fri, 28 Mar 2003, Tom Lane wrote:
>> It seems to me that it'd be fairly easy to make BEGIN cause only
>> a local state change in the backend; the actual transaction need not
>> start until the first subsequent command is received.  It's already
>> true that the transaction snapshot is not frozen at BEGIN time, but
>> only when the first DML or DDL command is received; so this would
>> have no impact on the client-visible semantics.  But a BEGIN-then-
>> sleep-for-awhile client wouldn't interfere with VACUUM anymore.

> What about serializable mode?  Wouldn't that break it?

No.  Even in serializable mode, we don't set the snapshot until the
first DML/DDL command.  (This *has* to be true because if you want to
take any locks via explicit LOCK commands, you need to be able to issue
those before the snapshot is frozen.  Doesn't do you much good to lock
a table if your view of the table will date from before the lock.)

AFAICT the only part of this proposal that would result in any change in
user-visible behavior is the proposal to alter the point where now() is
frozen.  But that's really an independent question.

			regards, tom lane


Mon, 31 Mar 2003 13:21:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT 
Olleg Samojlov <olleg@jane.telecom.mipt.ru> writes:
> As I can remember, already, when autocommit off transaction begin with 
> first DML or DDL command. May be better change client to use autocommit 
> off mode?

We've been waiting for those clients to get fixed for a long while.
Waiting for them to adopt autocommit-off seems about as hopeless as
waiting for them to postpone BEGIN :-(

Also, per other discussions, we are removing backend autocommit support
in 7.4.  It was the wrong way to do it.

			regards, tom lane


Thu, 03 Apr 2003 12:47:00 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Doug McNaught wrote:
> >> Maybe clock_time() and statement_time(), with transaction_time() an
> >> alias for now() (if that's seemed necessary)?
> 
> I could go with that ...
> 
> > We already have CURRENT_TIMESTAMP.  Would CLOCK_TIMESTAMP,
> > TRANSACTION_TIMESTAMP, and STATEMENT_TIMESTAMP make sense, with
> > CURRENT_TIMESTAMP being the same as TRANSACTION_TIMESTAMP?
> 
> ... or that.  But, please, *not* the weird no-parentheses syntax that
> CURRENT_TIMESTAMP has.  Make it clock_timestamp() and so on.

TODO updated:

	* Add transaction_timestamp(), statement_timestamp(), clock_timestamp()
	  functionality

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 26 May 2003 14:35:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT

TODO updated (already reported):

	* Add transaction_timestamp(), statement_timestamp(), clock_timestamp()
	functionality


---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Alvaro Herrera wrote:
> >> While at this, what do you think about adding the necessary variables
> >> to make now('transaction') and now('query') possible?
> 
> > TODO already has:
> > 	* Add now("transaction|statement|clock") functionality
> 
> I know that's what we agreed to awhile back, but I've realized that that
> would be a foolish way to do it.
> 
> The problem is that such a function would have to be marked VOLATILE,
> which would prevent its use in indexscan qualifiers.  The volatility
> labeling is a property of the function, not of the particular argument
> it's passed, so we'd have to label it for the worst-case behavior.
> 
> Accordingly, it's a bad idea to invent now('clock') and make it the
> same function as the other flavors.  We could get away with making
> now('transaction') and now('statement') ---- but the argument for this
> was consistency, and that argument pretty much falls flat if those two
> are one function while clock time is something else.
> 
> So I'm back in the camp of thinking three separate parameterless
> functions are the way to do it.  We already know what now() does,
> and we're not going to change it --- anyone want to propose names
> for the other two?
> 
> 			regards, tom lane
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 26 May 2003 14:36:34 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Changing behavior of BEGIN...sleep...do something...COMMIT