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
