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

From: Gaurav Singh <gaurav(dot)singh(at)yugabyte(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
Date: 2026-11-20 10:49:38
Message-ID: CAEcQ1baCwochm6HORfN29ZjmKbRxABzvN-UZtETdfVkL46z=Ew@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(dot)singh(at)yugabyte(dot)com>
wrote:

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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Nitin Motiani 2026-11-20 10:52:30 Re: BUG #19922: CYCLE/SEARCH with a window in the recursive term: could not find replacement targetlist entry
Previous Message Andrey Rachitskiy 2026-11-20 10:43:41 Re: BUG #19650: to_date()/to_timestamp() silently truncate 4+-digit day-of-year input to the first three digits