Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMY4q-00000002536-1pyS for pgsql-bugs@arkaria.postgresql.org; Thu, 29 Oct 2026 21:54:28 +0000 Received: from localhost ([127.0.0.1]:49996 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMY4p-00000006P6G-3MJz for pgsql-bugs@arkaria.postgresql.org; Thu, 29 Oct 2026 21:54:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:40750) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMY4p-00000006P67-2Fdf for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 21:54:27 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMY4m-00000001eUC-0HbE for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 21:54:27 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.18.1/8.18.1) with ESMTP id 69TLsLZw2134618; Thu, 29 Oct 2026 17:54:21 -0400 DKIM-Filter: OpenDKIM Filter v2.11.0 sss.pgh.pa.us 69TLsLZw2134618 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sss.pgh.pa.us; s=default; t=1793310861; bh=jmRewufUzcSecxzIFG7Z0oox0FFyUMjq7o7oyJtpjkA=; h=From:To:cc:Subject:In-reply-to:References:Date:From; b=wBvxxvPMIy4l8FPn6MTd5tRo8oCMrcLkq5dbPpqV7naab2tzDjlW4fJhQIGVxfFkK xOEhtAiKL8+j+lSbpRmX9OcGdDLvvHx/c5krqtyttLOUW8ynKMcVNBmQwcuqJ58qWb DTPJ0HgGEm9JttIu3r3XE2TqwjntB5IwFLsF3Sjw= From: Tom Lane To: Laurenz Albe cc: Gaurav Singh , pgsql-bugs@lists.postgresql.org Subject: Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters In-reply-to: <5c53c98046b8e273dfadaba5bfd92f8e36728bc9.camel@cybertec.at> References: <5c53c98046b8e273dfadaba5bfd92f8e36728bc9.camel@cybertec.at> Comments: In-reply-to Laurenz Albe message dated "Thu, 29 Oct 2026 22:40:24 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2134616.1793310861.1@sss.pgh.pa.us> Date: Thu, 29 Oct 2026 17:54:21 -0400 Message-ID: <2134617.1793310861@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Laurenz Albe 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