postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
От:
Gaurav 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 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
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 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
Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
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
Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Laurenz Albe 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
Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
От:
Gaurav Singh <gaurav.singh@yugabyte.com>
Дата:
I'll raise a patch.
Regards,
Gaurav Singh
YugabyteDB
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
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 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
YugabyteDBOn 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