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

Hi,

On Fri, Oct 30, 2026 at 3:24 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)singh(at)yugabyte(dot)com>
wrote:

> I'll raise a patch.
>
> Regards,
> Gaurav Singh
> YugabyteDB
>
> On Fri, Oct 30, 2026 at 3:24 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>>
>