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(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
>