Thread: current_timestamp wrong
version 7.0.1 In a bigger perl-project I had to hunt down a bug and it turned out that on very rare occassions the time delivered by 'current_timestamp' is wrong by a noticeable amount of time. (up to hours sometimes) The problem occures when using the DBI-module for perl and mod_perl in apache and the db-handle is stored globally for hours. On the first call for current_timestamp after long idle-time the returned value is *sometimes* incorrect. Creating a subrouting, doing nothing but fetching and printing the system-time and the database-time in the apps-logfile I get things like this: 2001-08-21-13:59:18 192.168.11.1 databasetime 2001-08-21 13:54:34+02 Is there any chance that this is a postgreSQL-problem or did someone hear about this before or is it definitely a problem within perl, the DBI-module or the application ? It looks like a cache-problem, but no involved part should actually do caching on such an operation. mod_perl is always a dangerous one for caching, but in this case I used only local variables .. thnx, peter -- mag. peter pilsl phone: +43 676 3574035 fax : +43 676 3546512 email: pilsl@goldfisch.at sms : pilsl@max.mail.at pgp-key available
On Wed, 22 Aug 2001, Peter Pilsl wrote: > The problem occures when using the DBI-module for perl and mod_perl in > apache and the db-handle is stored globally for hours. On the first > call for current_timestamp after long idle-time the returned value is > *sometimes* incorrect. Are you inside a transaction? <para> It is quite important to realize that <function>CURRENT_TIMESTAMP</function> and related functions all return the time as of the start of the current transaction; their values do not increment while a transaction is running. But <function>timeofday()</function> returns the actual current time. </para>
Peter Pilsl <pilsl@goldfisch.at> writes:
> In a bigger perl-project I had to hunt down a bug and it turned out that on
> very rare occassions the time delivered by 'current_timestamp'
> is wrong by a noticeable amount of time. (up to hours sometimes)
> The problem occures when using the DBI-module for perl and mod_perl in
> apache and the db-handle is stored globally for hours. On the first
> call for current_timestamp after long idle-time the returned value is
> *sometimes* incorrect.
current_timestamp is defined as the time of the start of the current
transaction (this is not a bug, it's a feature).
If your program is set up to hold a transaction open while it's idle,
that could cause stale-looking values of current_timestamp when you
finally do something in the transaction. Unfortunately I believe that
"autocommit off" mode in the DBD driver does exactly that.
This could possibly be fixed by altering the DBD driver so that after
it's issued COMMIT or ROLLBACK, it doesn't instantly send a BEGIN,
but delays sending BEGIN until it's actually asked to issue a query.
I have not looked at the code to see how hard it'd be to do that.
regards, tom lane
Peter Pilsl writes: > In a bigger perl-project I had to hunt down a bug and it turned out that on > very rare occassions the time delivered by 'current_timestamp' > is wrong by a noticeable amount of time. (up to hours sometimes) current_timestamp returns the time when the transaction started. This is wrong, but some developers seem to want to keep it that way. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
On Wed, Aug 22, 2001 at 06:26:14PM -0400, Tom Lane wrote: > > current_timestamp is defined as the time of the start of the current > transaction (this is not a bug, it's a feature). > > If your program is set up to hold a transaction open while it's idle, > that could cause stale-looking values of current_timestamp when you > finally do something in the transaction. Unfortunately I believe that > "autocommit off" mode in the DBD driver does exactly that. > the DBD-Driver does not support explicit begin-transactions. Instead every commit and rollbackstatement issues implicit a new begin-statement when autocommit off. (The same has obviously to happen, when the databasehandle is opened, but it is not documented) This and your explanation about the meaning of current_timestamp is the perfect explanation of our bug. I just created a fix by just issuing a rollback-statement at the *beginning* of each workingsession. This should start a new transaction and - I hope - dont loose to much performance. When its working, I'll contact Edmund Mergl, author of DBD::Pg and try to find a better solution. I personally would prefer a solution where one must issue explicit begin and commit/rollback and each action in between will cause an error when autocommit->off. thnx a lot. peter -- mag. peter pilsl phone: +43 676 3574035 fax : +43 676 3546512 email: pilsl@goldfisch.at sms : pilsl@max.mail.at pgp-key available