Re: Daylight Savings Time handling on persistent connections

On Sun, Oct 31, 2004 at 11:52:03AM -0500, Tom Lane wrote:
> Randall Nortman <postgreslists(at)wonderclown(dot)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