possible time change issue - known problem? - Mailing list pgsql-general

From Dan Langille
Subject possible time change issue - known problem?
Date
Msg-id 3E9134AD.16217.9EED52D@localhost
Whole thread Raw
Responses Re: possible time change issue - known problem?
List pgsql-general
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/


pgsql-general by date:

Previous
From: Jan Wieck
Date:
Subject: Re: unable to dump database, toast errors
Next
From: Jürgen Hauser
Date:
Subject: Enum in PostgreSQL - How to