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

The following bug has been logged online:

Bug reference:      5996
Logged by:          Brian S. Krug
Email address:      bkrug@usatech.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.

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@usatech.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.

On Thu, Apr 28, 2011 at 3:33 PM, Brian S. Krug <bkrug@usatech.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A05996
> Logged by: =A0 =A0 =A0 =A0 =A0Brian S. Krug
> Email address: =A0 =A0 =A0bkrug@usatech.com
> PostgreSQL version: 9.0.3
> Operating system: =A0 Solaris
> Description: =A0 =A0 =A0 =A0CURRENT_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 behavio=
r.
> This tripped me up significant and I would anticipate that many have fall=
en
> into the same trap. I recommend that CURRENT_TIMESTAMP functions as
> STATEMENT_TIMESTAMP instead of as TRANSACTION_TIMESTAMP.

I've been bitten by this too, but I doubt we're likely to want to
change it at this point for reasons of backward compatibility.  The
SQL standard may have something to say about it, too.

But as for pooled connections, it would seem unwise to multiplex a
server connection across different sessions while leaving a
transaction open.  Presumably each client should commit after it
finishes its own work.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company