Yes, this is a known issue with now(). We discussed it at great length
and decided to keep it the same in 7.3. There is timeofday for
current time, but that can change during the query. I think 7.4 will
have a parameter to now() which you can get the statement time, which is
probably what you want.
---------------------------------------------------------------------------
Talja Ari wrote:
> Postgres version: 7.2.1
> Platform: Server on Linux, (java-)client on HP-UX.11 and SunOS 5.8
> JDBC drivers: For PostgreSQL 7.2, JDK 1.3
> JDK version: 1.3.1
> Autocommit off for each connection
> Special server flags: -i to allow the connections from different host
>
> The program is a java server, which updates a row in the database at regular
> intervals. The update time is taken from postgres function now() but the
> update time is the time when the connection was created/last transaction
> ended. In Oracle the time which is created by 'sysdate' is the time when the
> update statement is executed (or the time when the statement is actually
> committed. I'm not sure about that because the execution and commit are
> called practically at the same time). This causes a delay to the time and
> the size of the delay can be almost anything depending on the time how long
> the connection has been without any use. The connections are kept in a
> connection pool for later use so I cannot trust that the connection is
> created or the transaction has ended recently enough. Currently I'm calling
> commit before I execute the update statement.
>
> Ari Talja
> ari.talja@comptel.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073