postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters

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

Responses

Browse pgsql-bugs by date

  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