Daylight saving bug in PostgreSQL 7.2.1 ?
От | Jonas Lindholm |
---|---|
Тема | Daylight saving bug in PostgreSQL 7.2.1 ? |
Дата | |
Msg-id | 3F0F787A.8090605@usa.net обсуждение исходный текст |
Ответы |
Re: Daylight saving bug in PostgreSQL 7.2.1 ?
(Jacek Kalinski <jacek_kal@o2.pl>)
Re: Daylight saving bug in PostgreSQL 7.2.1 ? (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-bugs |
Hi! I believe I've found a bug with daylight saving time. The example below shows that after inserting a row with a date and time when daylight saving is changing (6 April 2003 02:00:00) any selects using the same time and date are only returning correct result first time. If the select is done again the row is not found. CREATE SEQUENCE timespan_idx_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1; CREATE TABLE timespan ( host text NOT NULL, starttime timestamptz NOT NULL, endtime timestamptz NOT NULL, idx int8 DEFAULT nextval('"timespan_idx_seq"'::text) NOT NULL, CONSTRAINT timespan_pkey PRIMARY KEY (host, starttime, endtime) ) WITH OIDS; -- Index: timespan_i1 CREATE INDEX timespan_i1 ON timespan USING btree (host); -- Index: timespan_i2 CREATE INDEX timespan_i2 ON timespan USING btree (starttime); -- Index: timespan_i3 CREATE INDEX timespan_i3 ON timespan USING btree (endtime); -- Index: timespan_i4 CREATE UNIQUE INDEX timespan_i4 ON timespan USING btree (idx); -- Index: timespan_i5 CREATE INDEX timespan_i5 ON timespan USING btree (host, starttime); -- Index: timespan_i6 CREATE INDEX timespan_i6 ON timespan USING btree (host, endtime); INSERT INTO timespan VALUES('XXXXXX', ' 6-APR-2003 02:00:00', ' 6-APR-2003 03:00:00'); test=# SELECT idx FROM timespan WHERE host='XXXXXX' AND starttime=' 6-APR-2003 02:00:00' AND endtime=' 6-APR-2003 03:00:00'; idx ----- 1 <============= correct (1 row) test=# SELECT idx FROM timespan WHERE host='XXXXXX' AND starttime=' 6-APR-2003 02:00:00' AND endtime=' 6-APR-2003 03:00:00'; idx ----- (0 rows) <=============== not correct test=# SELECT idx FROM timespan WHERE host='XXXXXX' AND starttime=' 6-APR-2003 02:00:00' AND endtime=' 6-APR-2003 03:00:00'; idx ----- (0 rows) <=============== not correct test=# \q [root@xxxxxx scripts]# /usr/local/pgsql/bin/psql test Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit test=# SELECT idx FROM timespan WHERE host='XXXXXX' AND starttime=' 6-APR-2003 02:00:00' AND endtime=' 6-APR-2003 03:00:00'; idx ----- 1 <============= correct (1 row) test=# SELECT idx FROM timespan WHERE host='XXXXXX' AND starttime=' 6-APR-2003 02:00:00' AND endtime=' 6-APR-2003 03:00:00'; idx ----- (0 rows) <=============== not correct /Jonas Lindholm
В списке pgsql-bugs по дате отправления: