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

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