Обсуждение: 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