Skip site navigation (1) Skip section navigation (2)

Re: BUG #5996: CURRENT_TIMESTAMP uses often undesired TRANSACTION_TIMESTAMP, instead of STATEMENT_TIMESTAMP

From: John R Pierce <pierce(at)hogranch(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Cc: bkrug(at)usatech(dot)com
Subject: Re: BUG #5996: CURRENT_TIMESTAMP uses often undesired TRANSACTION_TIMESTAMP, instead of STATEMENT_TIMESTAMP
Date: 2011-04-29 05:34:35
Message-ID: 4DBA4DEB.8090305@hogranch.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On 04/28/11 12:33 PM, Brian S. Krug wrote:
> The following bug has been logged online:
>
> Bug reference:      5996
> Logged by:          Brian S. Krug
> Email address:      bkrug(at)usatech(dot)com
> PostgreSQL version: 9.0.3
> Operating system:   Solaris
> Description:        CURRENT_TIMESTAMP uses often undesired
> TRANSACTION_TIMESTAMP, instead of STATEMENT_TIMESTAMP
> Details:
>
> CURRENT_TIMESTAMP (and CURRENT_DATE, CURRENT_TIME) return the time of the
> start of the transcaction - which seems to be right after the end of the
> last transaction. Thus, if you use pooled connections, CURRENT_TIMESTAMP
> will return the time of the last COMMIT. This is often unintended behavior.
> This tripped me up significant and I would anticipate that many have fallen
> into the same trap. I recommend that CURRENT_TIMESTAMP functions as
> STATEMENT_TIMESTAMP instead of as TRANSACTION_TIMESTAMP.
>

Transactions start when you issue a BEGIN; command.   If you don't issue 
a BEGIN, then every statement is a self contained transaction.  Are you 
using a runtime abstraction thats doing this automagically behind your 
back right after COMMIT; ?  if so, thats your problem.

old JDBC's did exactly that, and it caused other ugly problems like long 
running IDLE In Transaction processes.



In response to

pgsql-bugs by date

Next:From: MichielDate: 2011-04-29 08:07:02
Subject: BUG #5997: [queryplan] PostgreSQL is sorting the query results when the result can only be 1 item.
Previous:From: Brian S. KrugDate: 2011-04-28 19:33:13
Subject: BUG #5996: CURRENT_TIMESTAMP uses often undesired TRANSACTION_TIMESTAMP, instead of STATEMENT_TIMESTAMP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group