Re: 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(at)toolsmythe(dot)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

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

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(at)toolsmythe(dot)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(at)postgresql(dot)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

Kris (sorry for the second copy; forgot to cc pgsql-jdbc(at)postgresql(dot)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(at)toolsmythe(dot)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(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Kris Jurka
Sent: Thursday, July 01, 2004 4:58 PM
To: John Pagakis
Cc: pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)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