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

On Thu, Apr 28, 2011 at 3:33 PM, Brian S. Krug <bkrug(at)usatech(dot)com> 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.

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.

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