pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
6+ messages / 3 participants
[nested] [flat]
* postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
@ 2026-10-29 21:00 Gaurav Singh <gaurav.singh@yugabyte.com>
0 siblings, 1 reply; 6+ messages in thread
From: Gaurav Singh @ 2026-10-29 21:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
@ 2026-10-29 21:40 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Gaurav Singh <gaurav.singh@yugabyte.com>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2026-10-29 21:40 UTC (permalink / raw)
To: Gaurav Singh <gaurav.singh@yugabyte.com>; pgsql-bugs@lists.postgresql.org
On Fri, 2026-10-30 at 02:30 +0530, Gaurav Singh wrote:
> 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.
>
> The mislabel itself was raised recently in the BUG #19644 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?
I think that the best solution would be to change the result of the
age() function: if it didn't try to convert to months and just
returned days and smaller, the result should be immutable.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
@ 2026-10-29 21:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2026-10-29 21:54 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Gaurav Singh <gaurav.singh@yugabyte.com>; pgsql-bugs@lists.postgresql.org
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Fri, 2026-10-30 at 02:30 +0530, Gaurav Singh wrote:
>> 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?
> I think that the best solution would be to change the result of the
> age() function: if it didn't try to convert to months and just
> returned days and smaller, the result should be immutable.
That's not going to help much, because you can't reduce to days either
without accounting for DST boundaries. For instance, because of the
upcoming fall fall-back in my time zone:
regression=# show timezone;
TimeZone
------------------
America/New_York
(1 row)
regression=# select age('11/03/2026'::timestamptz, '10/29/2026'::timestamptz);
age
--------
5 days
(1 row)
but that's hiding an hour:
regression=# select '11/03/2026'::timestamptz - '10/29/2026'::timestamptz;
?column?
-----------------
5 days 01:00:00
(1 row)
I think it's correct to mark age() as STABLE, and if we try not to
then we will invent kluges that have even worse consequences.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
@ 2026-10-30 06:50 Gaurav Singh <gaurav.singh@yugabyte.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Gaurav Singh @ 2026-10-30 06:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org
I'll raise a patch.
Regards,
Gaurav Singh
YugabyteDB
On Fri, Oct 30, 2026 at 3:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Fri, 2026-10-30 at 02:30 +0530, Gaurav Singh wrote:
> >> 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?
>
> > I think that the best solution would be to change the result of the
> > age() function: if it didn't try to convert to months and just
> > returned days and smaller, the result should be immutable.
>
> That's not going to help much, because you can't reduce to days either
> without accounting for DST boundaries. For instance, because of the
> upcoming fall fall-back in my time zone:
>
> regression=# show timezone;
> TimeZone
> ------------------
> America/New_York
> (1 row)
>
> regression=# select age('11/03/2026'::timestamptz,
> '10/29/2026'::timestamptz);
> age
> --------
> 5 days
> (1 row)
>
> but that's hiding an hour:
>
> regression=# select '11/03/2026'::timestamptz - '10/29/2026'::timestamptz;
> ?column?
> -----------------
> 5 days 01:00:00
> (1 row)
>
> I think it's correct to mark age() as STABLE, and if we try not to
> then we will invent kluges that have even worse consequences.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
@ 2026-10-30 20:42 Gaurav Singh <gaurav.singh@yugabyte.com>
parent: Gaurav Singh <gaurav.singh@yugabyte.com>
0 siblings, 1 reply; 6+ messages in thread
From: Gaurav Singh @ 2026-10-30 20:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org
Hi,
On Fri, Oct 30, 2026 at 3:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think it's correct to mark age() as STABLE
The attached patch does that. With it, postgres_fdw evaluates age()
locally instead of shipping it to the remote, and the function is no
longer accepted in index expressions, generated columns or partition
keys. The patch also adds a postgres_fdw test using the example from
my first mail, which returns 0 rows without the fix and 1 row with it.
This is for master only. Existing indexes, generated columns and
partition keys that use age(timestamptz, timestamptz) will fail to restore
on upgrade, so this probably needs a release note.
Regards,
Gaurav Singh
YugabyteDB
On Fri, Oct 30, 2026 at 12:20 PM Gaurav Singh <gaurav.singh@yugabyte.com>
wrote:
> I'll raise a patch.
>
> Regards,
> Gaurav Singh
> YugabyteDB
>
> On Fri, Oct 30, 2026 at 3:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Laurenz Albe <laurenz.albe@cybertec.at> writes:
>> > On Fri, 2026-10-30 at 02:30 +0530, Gaurav Singh wrote:
>> >> 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?
>>
>> > I think that the best solution would be to change the result of the
>> > age() function: if it didn't try to convert to months and just
>> > returned days and smaller, the result should be immutable.
>>
>> That's not going to help much, because you can't reduce to days either
>> without accounting for DST boundaries. For instance, because of the
>> upcoming fall fall-back in my time zone:
>>
>> regression=# show timezone;
>> TimeZone
>> ------------------
>> America/New_York
>> (1 row)
>>
>> regression=# select age('11/03/2026'::timestamptz,
>> '10/29/2026'::timestamptz);
>> age
>> --------
>> 5 days
>> (1 row)
>>
>> but that's hiding an hour:
>>
>> regression=# select '11/03/2026'::timestamptz - '10/29/2026'::timestamptz;
>> ?column?
>> -----------------
>> 5 days 01:00:00
>> (1 row)
>>
>> I think it's correct to mark age() as STABLE, and if we try not to
>> then we will invent kluges that have even worse consequences.
>>
>> regards, tom lane
>>
>
Attachments:
v1-0001-Mark-age-timestamptz-timestamptz-as-STABLE.patch (application/octet-stream, 4.2K) download
From 490d3c454cf6d1888d305800283359c2166c2c9b Mon Sep 17 00:00:00 2001
From: Gaurav Singh <gaurav.singh@yugabyte.com>
Date: Sat, 31 Oct 2026 01:46:04 +0530
Subject: [PATCH v1] Mark age(timestamptz, timestamptz) as STABLE
Its result depends on the TimeZone setting, but it was marked
IMMUTABLE. That let postgres_fdw ship it to the remote server, which
runs with TimeZone = GMT, and return wrong rows.
Do not backpatch, since this can break existing indexes, generated
columns and partition keys that use the function.
Reported-by: Gaurav Singh <gaurav.singh@yugabyte.com>
Author: Gaurav Singh <gaurav.singh@yugabyte.com>
Discussion: https://postgr.es/m/CAEcQ1badDTrQM8qjdkMOy4Bs5xP2EwKopK=3O_-DSatkrsz2KA@mail.gmail.com
---
.../postgres_fdw/expected/postgres_fdw.out | 24 +++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 12 ++++++++++
src/include/catalog/pg_proc.dat | 2 +-
3 files changed, 37 insertions(+), 1 deletion(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 115ebc7925f..82ae8396ef3 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -1176,6 +1176,30 @@ SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' EN
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
(4 rows)
+-- age(timestamptz, timestamptz) depends on TimeZone, so it can't be shipped:
+-- the remote session runs with TimeZone = GMT
+CREATE TABLE loct_age (s timestamptz, e timestamptz);
+INSERT INTO loct_age VALUES ('2026-01-31 00:00+00', '2026-03-01 00:00+00');
+CREATE FOREIGN TABLE ft_age (s timestamptz, e timestamptz)
+ SERVER loopback OPTIONS (table_name 'loct_age');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT age(e, s) FROM ft_age WHERE age(e, s) = '29 days';
+ QUERY PLAN
+-------------------------------------------------------------
+ Foreign Scan on public.ft_age
+ Output: age(e, s)
+ Filter: (age(ft_age.e, ft_age.s) = '@ 29 days'::interval)
+ Remote SQL: SELECT s, e FROM public.loct_age
+(4 rows)
+
+SELECT age(e, s) FROM ft_age WHERE age(e, s) = '29 days';
+ age
+-----------
+ @ 29 days
+(1 row)
+
+DROP FOREIGN TABLE ft_age;
+DROP TABLE loct_age;
-- Test array type conversion pushdown
SET plan_cache_mode = force_generic_plan;
PREPARE s(varchar[]) AS SELECT count(*) FROM ft2 WHERE c6 = ANY ($1);
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 77c9a25a890..85352704690 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -453,6 +453,18 @@ SELECT * FROM ft1 WHERE CASE c3 WHEN c6 THEN true ELSE c3 < 'bar' END;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' END;
+-- age(timestamptz, timestamptz) depends on TimeZone, so it can't be shipped:
+-- the remote session runs with TimeZone = GMT
+CREATE TABLE loct_age (s timestamptz, e timestamptz);
+INSERT INTO loct_age VALUES ('2026-01-31 00:00+00', '2026-03-01 00:00+00');
+CREATE FOREIGN TABLE ft_age (s timestamptz, e timestamptz)
+ SERVER loopback OPTIONS (table_name 'loct_age');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT age(e, s) FROM ft_age WHERE age(e, s) = '29 days';
+SELECT age(e, s) FROM ft_age WHERE age(e, s) = '29 days';
+DROP FOREIGN TABLE ft_age;
+DROP TABLE loct_age;
+
-- Test array type conversion pushdown
SET plan_cache_mode = force_generic_plan;
PREPARE s(varchar[]) AS SELECT count(*) FROM ft2 WHERE c6 = ANY ($1);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1f21a8eb6d4..ce90b271ffb 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2562,7 +2562,7 @@
proname => 'interval_larger', prorettype => 'interval',
proargtypes => 'interval interval', prosrc => 'interval_larger' },
{ oid => '1199', descr => 'date difference preserving months and years',
- proname => 'age', prorettype => 'interval',
+ proname => 'age', provolatile => 's', prorettype => 'interval',
proargtypes => 'timestamptz timestamptz', prosrc => 'timestamptz_age' },
# OIDS 1200 - 1299
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
@ 2026-11-20 10:49 Gaurav Singh <gaurav.singh@yugabyte.com>
parent: Gaurav Singh <gaurav.singh@yugabyte.com>
0 siblings, 0 replies; 6+ messages in thread
From: Gaurav Singh @ 2026-11-20 10:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org
Ping on the v1 patch from upthread. It marks age(timestamptz, timestamptz)
as STABLE, as suggested, and is for master only. I have added it to the
commitfest: https://commitfest.postgresql.org/patch/7673/
One thing I would like feedback on: existing indexes, generated columns
and partition keys that use the function will fail to restore after this
change. Is a release note enough, or should pg_upgrade check for them?
Regards,
Gaurav Singh
YugabyteDB
On Sat, Oct 31, 2026 at 2:12 AM Gaurav Singh <gaurav.singh@yugabyte.com>
wrote:
> Hi,
>
> On Fri, Oct 30, 2026 at 3:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I think it's correct to mark age() as STABLE
>
> The attached patch does that. With it, postgres_fdw evaluates age()
> locally instead of shipping it to the remote, and the function is no
> longer accepted in index expressions, generated columns or partition
> keys. The patch also adds a postgres_fdw test using the example from
> my first mail, which returns 0 rows without the fix and 1 row with it.
>
> This is for master only. Existing indexes, generated columns and
> partition keys that use age(timestamptz, timestamptz) will fail to restore
> on upgrade, so this probably needs a release note.
>
> Regards,
> Gaurav Singh
> YugabyteDB
>
> On Fri, Oct 30, 2026 at 12:20 PM Gaurav Singh <gaurav.singh@yugabyte.com>
> wrote:
>
>> I'll raise a patch.
>>
>> Regards,
>> Gaurav Singh
>> YugabyteDB
>>
>> On Fri, Oct 30, 2026 at 3:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>>> Laurenz Albe <laurenz.albe@cybertec.at> writes:
>>> > On Fri, 2026-10-30 at 02:30 +0530, Gaurav Singh wrote:
>>> >> 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?
>>>
>>> > I think that the best solution would be to change the result of the
>>> > age() function: if it didn't try to convert to months and just
>>> > returned days and smaller, the result should be immutable.
>>>
>>> That's not going to help much, because you can't reduce to days either
>>> without accounting for DST boundaries. For instance, because of the
>>> upcoming fall fall-back in my time zone:
>>>
>>> regression=# show timezone;
>>> TimeZone
>>> ------------------
>>> America/New_York
>>> (1 row)
>>>
>>> regression=# select age('11/03/2026'::timestamptz,
>>> '10/29/2026'::timestamptz);
>>> age
>>> --------
>>> 5 days
>>> (1 row)
>>>
>>> but that's hiding an hour:
>>>
>>> regression=# select '11/03/2026'::timestamptz -
>>> '10/29/2026'::timestamptz;
>>> ?column?
>>> -----------------
>>> 5 days 01:00:00
>>> (1 row)
>>>
>>> I think it's correct to mark age() as STABLE, and if we try not to
>>> then we will invent kluges that have even worse consequences.
>>>
>>> regards, tom lane
>>>
>>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-11-20 10:49 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-29 21:00 postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters Gaurav Singh <gaurav.singh@yugabyte.com>
2026-10-29 21:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-29 21:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-30 06:50 ` Gaurav Singh <gaurav.singh@yugabyte.com>
2026-10-30 20:42 ` Gaurav Singh <gaurav.singh@yugabyte.com>
2026-11-20 10:49 ` Gaurav Singh <gaurav.singh@yugabyte.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox