Re: BUG #5996: CURRENT_TIMESTAMP uses often undesired TRANSACTION_TIMESTAMP, instead of STATEMENT_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.