Re: CURRENT_DATE and CURRENT_TIME return incorrect values

Nigel J. Andrews wrote:
> The big question is: Can you repeat it?

I doubt it, because I never noticed this before. And I tend to check
all the data very closely because I'm doing developement work (gotta
catch all those bugs).

> The next big question is: Can you supply a complete example of how to repeat
> it.

Alas no. :( I tried to reproduce the oddity by letting my system sit
mostly idle for the past 24 hours, to see if the same thing would
happen. I then added a new record, but the date/time was correct.

> Is there some reason you are splitting the order time and date into two columns
> rather than using a single timestamp one?

Yes, the order_time is sort of an 'extra' field. I don't currently use
it, and it might even disapear in the future. I only do calculations on
the order_date and setup_date fields. That's why I didn't use a
timestamp.

> The only things that crossed my mind were a) you started a transaction on the
> 26th and only wrote that record on the 28th or b) which ever system you were
> querying the time of to get the 28th had a different time to the database
> server.

Tom Lane wrote:
> Is it possible that the entry was made from a transaction that had been
> sitting open for two days? now() and related functions return
> transaction start time.

Aha! You may be on to something there. First, to clarify: I only have
one server, and all processes (pgsql, apache, etc.) are running on it
and query that server's system clock.

When I run 'ps auxww |grep postgres' it shows:

postgres 4770 0.0 0.2 7068 3076 pts/3 S Apr27 0:11 /usr/local/pgsql/bin/postmaster
postgres 12525 0.0 0.2 8060 3024 pts/3 S Apr27 0:00 postgres: stats buffer process
postgres 5850 0.0 0.2 7116 3080 pts/3 S Apr27 0:00 postgres: stats collector process
postgres 20284 0.0 0.8 10556 8748 pts/3 S May02 2:11 postgres: dev dev [local] idle
postgres 8572 0.0 0.4 7484 4772 pts/3 S May29 0:00 postgres: dev dev [local] idle in transaction
postgres 20062 0.0 0.4 7484 4680 pts/3 S May29 0:00 postgres: dev dev [local] idle in transaction
postgres 18226 0.0 0.4 7484 4680 pts/3 S May29 0:00 postgres: dev dev [local] idle in transaction
postgres 16750 0.0 0.4 7484 4680 pts/3 S May29 0:00 postgres: dev dev [local] idle in transaction
postgres 25970 0.0 0.4 7484 4676 pts/3 S May29 0:00 postgres: dev dev [local] idle in transaction
postgres 21388 0.0 0.4 7484 4676 pts/3 S 05:56 0:00 postgres: dev dev [local] idle in transaction

PID 20284 is an instance of psql, that I always keep open in 'screen',
so that's why it's been running since May 02.

All the other children processes are managed by mod_perl, using
Apache::DBI to get persistent connections. My application was
completely dormant during the 'ps' snapshot above. But it does seem
that Apache::DBI starts a transaction (one for each mod_perl process)
and waits...

I'm thinking that by some freaky occurence, one of those transactions
got started on the 26th and didn't get used at all (not even for SELECT
queries) during those few days, until the 28th when it was handed the
INSERT for the row in question. Does that sound possible? It seems
strange because my application has to run a good half-dozen SELECT
queries before even getting to the point of adding a record, and there
were a few (but only a few) other queries done during those days as well.

I imagine that this situation shouldn't happen in a real environment where
the DB is actually used on a daily basis, but there is still a chance
for the dates to be wrong if a transaction starts a little before midnight
but doesn't commit until the next day. And chances are the times may
often tend to be off by a little?

If this is true, then it sounds like a very bad idea to use columns with
'default CURRENT_DATE' and 'default CURRENT_TIME' with Apache::DBI...