Postgres Pro
Downloads
Home   >   mailing lists

Thread: now() gives the time of the last commit, not the time it is calle d

now() gives the time of the last commit, not the time it is calle d

From
Talja Ari
Date:
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

Re: now() gives the time of the last commit, not the time it

From
Stephan Szabo
Date:
On Wed, 16 Oct 2002, 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.

This is the currently intended behavior of now(), if you want the moment
the call is made, you can use timeofday() I believe.

Re: now() gives the time of the last commit, not the time it

From
Bruce Momjian
Date:
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