From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Gaurav Singh <gaurav.singh@yugabyte.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
Date: Thu, 29 Oct 2026 17:54:21 -0400
Message-ID: <2134617.1793310861@sss.pgh.pa.us> (raw)
In-Reply-To: <5c53c98046b8e273dfadaba5bfd92f8e36728bc9.camel@cybertec.at>
References: <CAEcQ1badDTrQM8qjdkMOy4Bs5xP2EwKopK=3O_-DSatkrsz2KA@mail.gmail.com>
<5c53c98046b8e273dfadaba5bfd92f8e36728bc9.camel@cybertec.at>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, laurenz.albe@cybertec.at, gaurav.singh@yugabyte.com
Subject: Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters
In-Reply-To: <2134617.1793310861@sss.pgh.pa.us>
References: <CAEcQ1badDTrQM8qjdkMOy4Bs5xP2EwKopK=3O_-DSatkrsz2KA@mail.gmail.com> <5c53c98046b8e273dfadaba5bfd92f8e36728bc9.camel@cybertec.at> <2134617.1793310861@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox