Re: Daylight Savings Time handling on persistent connections

On Sun, Oct 31, 2004 at 11:52:03AM -0500, Tom Lane wrote:
> Randall Nortman <postgreslists@wonderclown.com> writes:
> > My suspicion is that Postgres calculates the local offset from UTC
> > only once per session, during session initialization.
>
> This is demonstrably not so.  We might be able to figure out what
> actually went wrong, if you would show us the exact commands your
> application issued.

I can't reproduce the error without messing up my clock, but from my
logs, here's the text of the SQL sent to the server:

insert into sensor_readings_numeric (sensor_id, reading_ts, reading,
min, max) values (3, '2004-10-31 01:00:00', 0.540602, 0.519071837254,
0.551811824539)

And this came back:

ERROR:  duplicate key violates unique constraint "sensor_readings_numeric_pkey"

Table definition:

                               Table "public.sensor_readings_numeric"
   Column   |           Type           |
   Modifiers
------------+--------------------------+-------------------------------------------------------------
 sensor_id  | integer                  | not null
 reading_ts | timestamp with time zone | not null default ('now'::text)::timestamp(6) with time zone
 reading    | numeric                  | not null
 min        | numeric                  |
 max        | numeric                  |
Indexes:
    "sensor_readings_numeric_pkey" primary key, btree (reading_ts, sensor_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (sensor_id) REFERENCES sensors(sensor_id)


I'll try to set up a system where I can play around with the clock to
see if I can reproduce the error, but it'll probably be a few days at
least before I can do that.  There's no hurry for me, since this won't
happen again until next year.

Thanks,

Randall Nortman