I suspect this may be a known problem, but just in case...
This problem occurs under 7.2.3 but not 7.3.2.
Today I found a problem with a stats function which is run every day
at midnight. For what it's worth, the time changed on Sunday
morning. The symptom is this error message:
Cannot insert a duplicate key into unique index
daily_stats_data_unique
The index involves a date field.
Looking at the code, I suspect the problem is related to this problem
which I can duplicate on my current system: PostgreSQL 7.2.3 on i386-
portbld-freebsd4.6, compiled by GCC 2.95.4
# select current_date, (current_date - interval '1 day')::date;
date | date
------------+------------
2003-04-07 | 2003-04-05
I expect the answer to be 2003-04-06 (i.e. yesterday's date).
But this seems to work:
select now(), (now() - interval '1 day')::date;
now | date
-------------------------------+------------
2003-04-07 08:08:08.360088-04 | 2003-04-06
Why would this suddenly stop working?
The problem does not occur on a 7.3.2 system:
# select current_date, (current_date - interval '1 day')::date;
date | date
------------+------------
2003-04-07 | 2003-04-06
(1 row)
# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
(1 row)
--
Dan Langille : http://www.langille.org/