We are experiencing serious time stamp drifts (days and weeks) when
running postgresql for long periods of time. I know that
CURRENT_TIMESTAMP is the time at the beginning of the transaction block,
but when the database reaches a drift state, the CURRENT_TIMESTAMP is
consistently drifted across all connections/transactions. This drift
worsens the longer the database is running. A restart of the database
fixes the drift. I've read somewhere that postgres keeps it's own
internal clock. Is there a reason for this? Could this be the cause of
this serious drift on Win32 systems (we don't see these issues on Linux
using the same version of postgres)? Could we have some transactions
that never get committed or rolledback? Is there some way to view all
the open transactions? The system clock maintains correct time through
all of this, and using gettimeofday() gives results similar to what the
system clock says. We have temporarily changed all of our queries to
use the gettimeofday() construct rather than the current_timestamp, but
that's a work around, and is causing us other problems. Has anyone else
seen this? Is there something that was fixed in the 7.3.x tree that
would fix this problem?
We're using postgres 7.2.3 on Windows 2000 server.
Thanks for any insights.
Joseph
