Thread: Issue with now() within a transaction in pg74.213.jdbc.jar
Most of my tables have a last update date and during some testing in
Postgres 7.4.1 I noticed that the last_update date was slightly in the past
for anything updated within a transaction. The amount it is off by seems to
vary: as little as 30 seconds; as much as a couple of minutes.
Tables updated outside of a transaction seem to have an accurate timestamp.
If I setAutoCommit(true) instead of setAutoCommit(false) and comment out my
commit/rollbacks, the timestamp is accurate. Within a transaction, it is as
though I have set the WayBack machine for a random trip 1/2 - 2 minutes back
in time.
I am using pg74.213.jdbc.jar. Is there a more current driver?
Has anyone else noticed this? Is anyone looking at it?
__________________________________________________________________
John Pagakis
Email: ih8spam_thebfh@toolsmythe.com
remove the ih8spam_ and you'll find me
"I don't know the meaning of the word
surrender! I mean I know it, I'm not
dumb, just not in this context."
-- The Tick
This signature generated by
... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc.
www.spazmodicfrog.com
this has nothing to do with jdbc, it is more to do with the way now() is determined. From your explanation it is determined at the end of the transaction, or when the transaction is committed. Dave On Thu, 2004-07-01 at 17:47, John Pagakis wrote: > Most of my tables have a last update date and during some testing in > Postgres 7.4.1 I noticed that the last_update date was slightly in the past > for anything updated within a transaction. The amount it is off by seems to > vary: as little as 30 seconds; as much as a couple of minutes. > > Tables updated outside of a transaction seem to have an accurate timestamp. > If I setAutoCommit(true) instead of setAutoCommit(false) and comment out my > commit/rollbacks, the timestamp is accurate. Within a transaction, it is as > though I have set the WayBack machine for a random trip 1/2 - 2 minutes back > in time. > > I am using pg74.213.jdbc.jar. Is there a more current driver? > > Has anyone else noticed this? Is anyone looking at it? > > __________________________________________________________________ > John Pagakis > Email: ih8spam_thebfh@toolsmythe.com > remove the ih8spam_ and you'll find me > > "I don't know the meaning of the word > surrender! I mean I know it, I'm not > dumb, just not in this context." > -- The Tick > > This signature generated by > ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. > www.spazmodicfrog.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Dave Cramer 519 939 0336 ICQ # 14675561
John Pagakis wrote: > Most of my tables have a last update date and during some testing in > Postgres 7.4.1 I noticed that the last_update date was slightly in the past > for anything updated within a transaction. The amount it is off by seems to > vary: as little as 30 seconds; as much as a couple of minutes. > > Tables updated outside of a transaction seem to have an accurate timestamp. > If I setAutoCommit(true) instead of setAutoCommit(false) and comment out my > commit/rollbacks, the timestamp is accurate. Within a transaction, it is as > though I have set the WayBack machine for a random trip 1/2 - 2 minutes back > in time. > > I am using pg74.213.jdbc.jar. Is there a more current driver? > > Has anyone else noticed this? Is anyone looking at it? Older drivers issue a BEGIN immediately after the previous transaction commits or rollbacks. This freezes the value of now() at that point. It also manifests as idle backends being "idle in transaction" all the time. Upgrade to a more recent driver. Current CVS should be fine against a 7.4 server. See http://jdbc.postgresql.org -o
On Thu, 1 Jul 2004, John Pagakis wrote: > Most of my tables have a last update date and during some testing in > Postgres 7.4.1 I noticed that the last_update date was slightly in the past > for anything updated within a transaction. The amount it is off by seems to > vary: as little as 30 seconds; as much as a couple of minutes. > This is not a JDBC issue. now() always returns the time from the start of the transaction. If you want current wall clock time use timeofday(). Kris Jurka
Kris (sorry for the second copy; forgot to cc pgsql-jdbc@postgresql.org with this) - That did it. Thanks so much!! Odd though. There seems to be a bug in the cygwin implementation of postgres. Tim McAuley ran into the same problem I did and posted here: http://groups.google.com/groups?q=cast+timeofday+timestamp&hl=en&lr=&ie=UTF- 8&selm=bgdif2%2414kk%241%40FreeBSD.csie.NCTU.edu.tw&rnum=5 To save you from having to follow the link - an attempt to cast TIMEOFDAY() to TIMESTAMP in cygwin's implementation results in: ERROR: Bad timestamp external representation 'Thu Jul 01 22:29:25.402375 2004 USMST' Here's what I did to get around it. Perhaps someone out there has a more elegant solution (and if so I'm all ears because I hate this hack): SELECT CAST( SUBSTRING( TIMEOFDAY() FROM 1 FOR 31 ) AS TIMESTAMP ); It looks like the CAST problem is a cygwin-only issue. Thanks again Kris!! __________________________________________________________________ John Pagakis Email: ih8spam_thebfh@toolsmythe.com remove the ih8spam_ and you'll find me "Think enough and you won't know anything." -- Kenneth Patchen This signature generated by ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. www.spazmodicfrog.com -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Kris Jurka Sent: Thursday, July 01, 2004 4:58 PM To: John Pagakis Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Issue with now() within a transaction in pg74.213.jdbc.jar On Thu, 1 Jul 2004, John Pagakis wrote: > Most of my tables have a last update date and during some testing in > Postgres 7.4.1 I noticed that the last_update date was slightly in the past > for anything updated within a transaction. The amount it is off by seems to > vary: as little as 30 seconds; as much as a couple of minutes. > This is not a JDBC issue. now() always returns the time from the start of the transaction. If you want current wall clock time use timeofday(). Kris Jurka ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
On Thu, 1 Jul 2004, John Pagakis wrote: > Odd though. There seems to be a bug in the cygwin implementation of > postgres. Tim McAuley ran into the same problem I did and posted here: > > http://groups.google.com/groups?q=cast+timeofday+timestamp&hl=en&lr=&ie=UTF- > 8&selm=bgdif2%2414kk%241%40FreeBSD.csie.NCTU.edu.tw&rnum=5 > > To save you from having to follow the link - an attempt to cast TIMEOFDAY() > to TIMESTAMP in cygwin's implementation results in: > > ERROR: Bad timestamp external representation 'Thu Jul 01 22:29:25.402375 > 2004 USMST' > Well the JDBC list isn't the best place to get help on this, but I don't see a problem here, so I imagine it is dependent on your timezone. (I'm PDT). Postgresql has some confusion on timezone names because it has an internal list of timezones, but also uses the system libraries to manipulate them as well. This will be rectified in 7.5 when all timezone handling is moved under the control of postgresql libraries, but for now I will note that USMST does not appear in src/backend/utils/adt/datetime.c. Also notice that the original bug report you cite shows different timezone results for timeofday. cygwin shows GMTDT while linux shows IST. Checking datetime.c again IST is included, but GMTDT is not. Kris Jurka