| From: | Gaurav Singh <gaurav(dot)singh(at)yugabyte(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters |
| Date: | 2026-10-29 21:00:34 |
| Message-ID: | CAEcQ1badDTrQM8qjdkMOy4Bs5xP2EwKopK=3O_-DSatkrsz2KA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
Hi,
age(timestamptz, timestamptz) is marked IMMUTABLE, but its result
depends on the TimeZone setting, because it converts both values to
local time before subtracting:
```
SET TimeZone = 'UTC';
SELECT age('2026-03-01 00:00+00'::timestamptz, '2026-01-31
00:00+00'::timestamptz);
-- 1 mon 1 day
SET TimeZone = 'America/New_York';
SELECT age('2026-03-01 00:00+00'::timestamptz, '2026-01-31
00:00+00'::timestamptz);
-- 29 days
```
Because of the IMMUTABLE label, postgres_fdw considers age() safe to
send to the remote server. The remote session runs with
timezone = 'GMT', so the filter is evaluated in GMT instead of the
local session's time zone, and the query returns wrong rows. On
19beta1, in database "postgres":
```
CREATE EXTENSION postgres_fdw;
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname
'postgres');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE TABLE t (s timestamptz, e timestamptz);
INSERT INTO t VALUES ('2026-01-31 00:00+00', '2026-03-01 00:00+00');
CREATE FOREIGN TABLE ft (s timestamptz, e timestamptz) SERVER loopback
OPTIONS (table_name 't');
SET TimeZone = 'America/New_York';
SELECT * FROM t WHERE age(e, s) = '29 days'; -- 1 row
SELECT * FROM ft WHERE age(e, s) = '29 days'; -- 0 rows
EXPLAIN VERBOSE shows the filter in the remote query:
Remote SQL: SELECT s, e FROM public.t WHERE ((age(e, s) = '29
days'::interval))
```
The mislabel itself was raised recently in the BUG #19644
<https://www.postgresql.org/message-id/787D78EB-E6D7-4408-9DFA-4AD9C3D4EA5E%40localhost.localdomain>
thread,
where the concern was that relabeling it STABLE would break existing
generated columns and indexes.
Would it make sense to mark age(timestamptz, timestamptz) STABLE on
master, or, if that is too disruptive, to stop postgres_fdw from
shipping it?
Regards,
Gaurav Singh
YugabyteDB
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Laurenz Albe | 2026-10-29 21:40:24 | Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters |
| Previous Message | David Rowley | 2026-10-29 20:57:51 | Re: BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out |