Re: CURRENT_DATE and CURRENT_TIME return incorrect values - Mailing list pgsql-general
From | valerian |
---|---|
Subject | Re: CURRENT_DATE and CURRENT_TIME return incorrect values |
Date | |
Msg-id | 20030531003654.GB8474@hotpop.com Whole thread Raw |
In response to | Re: CURRENT_DATE and CURRENT_TIME return incorrect values ("Nigel J. Andrews" <nandrews@investsystems.co.uk>) |
List | pgsql-general |
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...
pgsql-general by date: