> I had previously dropped the support to the timezone dependent functions.
> This patchset has one additional patch where that is fixed.
I focused on mostly this, and found a few issues in it:
1: session_timezone is read at plan time, so cached plans can go stale
and return wrong results
SET enable_seqscan = off;
SET enable_bitmapscan = off;
CREATE TABLE pc(t timestamptz);
INSERT INTO pc VALUES ('2025-11-02 05:59:59.999999+00'), ('2025-11-02
06:00:00+00');
CREATE INDEX ON pc(t);
PREPARE q AS SELECT t AT LOCAL AS l FROM pc ORDER BY 1;
EXECUTE q; -- generic plan, built under Etc/UTC
SET TimeZone = 'America/New_York';
EXECUTE q; -- 01:59:59.999999 then 01:00:00
SELECT t AT LOCAL AS l FROM pc ORDER BY 1; -- same query, fresh plan: correct
2: timezone(text, timestamp) isn't monotonic
SET enable_seqscan = off;
SET enable_bitmapscan = off;
CREATE TABLE gap(ts timestamp);
INSERT INTO gap VALUES ('2026-03-08 01:59'), ('2026-03-08 02:30'),
('2026-03-08 03:00');
CREATE INDEX ON gap(ts);
EXPLAIN (COSTS OFF) SELECT timezone('America/New_York', ts) FROM gap ORDER BY 1;
SELECT timezone('America/New_York', ts) FROM gap ORDER BY 1;
SET enable_slope = off;
SELECT timezone('America/New_York', ts) FROM gap ORDER BY 1;
3: unrecognized zones results in an error during planning
CREATE TABLE e(t timestamptz);
CREATE INDEX ON e(t);
SELECT date_trunc('day', t, 'Bogus/Zone') FROM e ORDER BY 1; --
ERROR: time zone "Bogus/Zone" not recognized
4: inspect_monotonicity has a corner-case issue with date_trunc which
returns a timestamptz
SET enable_seqscan = off;
SET enable_bitmapscan = off;
CREATE TABLE dt(t timestamptz);
INSERT INTO dt VALUES ('1916-07-27 22:26:07.999999+00'), ('1916-07-27
22:26:08+00');
CREATE INDEX ON dt(t);
SELECT date_trunc('hour', t, 'Europe/Athens') FROM dt ORDER BY 1; --
22:25:08 then 22:00:00
SET enable_slope = off;
SELECT date_trunc('hour', t, 'Europe/Athens') FROM dt ORDER BY 1; --
correct order
There's also an uninitialized variable warning in 0002:
- index_pathkeys = build_index_pathkeys(root, index,
-
BackwardScanDirection);
