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

Started by Gaurav Singh24 days ago6 messagesbugs
Jump to latest
#1Gaurav Singh
gaurav.singh@yugabyte.com

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
</messages/by-id/787D78EB-E6D7-4408-9DFA-4AD9C3D4EA5E@localhost.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

#2Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Gaurav Singh (#1)
Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Laurenz Albe (#2)
Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters

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

#4Gaurav Singh
gaurav.singh@yugabyte.com
In reply to: Tom Lane (#3)
Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters

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:

Show quoted text

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

#5Gaurav Singh
gaurav.singh@yugabyte.com
In reply to: Gaurav Singh (#4)
Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters

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:

Show quoted text

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:

t254449_5
v1-0001-Mark-age-timestamptz-timestamptz-as-STABLE.patchapplication/octet-stream; name=v1-0001-Mark-age-timestamptz-timestamptz-as-STABLE.patchDownload+37-2
#6Gaurav Singh
gaurav.singh@yugabyte.com
In reply to: Gaurav Singh (#5)
Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters

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:

Show quoted text

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