Hi hackers, This is Medium 4 from my list in [1].
Since fc896821c44, a btree_gin index on a timestamp column can be
used to compare with a timestamptz value. When that value is in the
first pass of the repeated hour at the end of DST, the index scan
misses rows.
create extension btree_gin;
set timezone = 'America/New_York';
create table t (ts timestamp);
insert into t values ('2026-11-01 01:10'), ('2026-11-01 01:40');
create index on t using gin (ts);
set enable_seqscan = off;
select * from t where ts >= '2026-11-01 01:30-04'::timestamptz;
master returns only 01:40. 18 returns both rows, and so does master
with a seq scan.
0001 converts the start key back to timestamptz. If the result is
after the bound, it moves the key earlier by the difference. Here the
key becomes 00:30. The scan already rechecks each entry with the real
operator, so nothing extra is returned. 0002 adds a test. The patches
apply to master and REL_19_STABLE.
A date column goes through the same kind of conversion. I could not
make it fail, also not in the Cuba time zone where midnight repeats.
A bound in the spring gap can give wrong rows too, but a plain btree
index does the same there, so I left it alone.
The commit came from [2].
[1]
https://postgr.es/m/cagrkxqsfdjqzp_kwrz7dv6lhx-csrkdnynnxgntrscenqtk...@mail.gmail.com
[2] https://postgr.es/m/[email protected]
Thanks,
Shihao
v1-0002-btree_gin-Test-timestamptz-bound-in-repeated-DST-.patch
Description: Binary data
v1-0001-btree_gin-Fix-timestamptz-lower-bound-in-repeated.patch
Description: Binary data
