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 1xMXrN-000000024wR-3TQD for pgsql-bugs@arkaria.postgresql.org; Thu, 29 Oct 2026 21:40:34 +0000 Received: from localhost ([127.0.0.1]:41226 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMXrL-00000006MSC-3Kjo for pgsql-bugs@arkaria.postgresql.org; Thu, 29 Oct 2026 21:40:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:52260) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMXrL-00000006MS4-0pAS for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 21:40:31 +0000 Received: from mail-wm1-x335.google.com ([2a00:1450:4864:20::335]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xMXrI-00000001ajM-3hEq for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 21:40:29 +0000 Received: by mail-wm1-x335.google.com with SMTP id 5b1f17b1804b1-4a140e7405dso247705e9.3 for ; Thu, 29 Oct 2026 14:40:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec.at; s=google; t=1793310027; x=1793914827; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:content-type :references:in-reply-to:date:to:from:subject:message-id:from:to:cc :subject:date:message-id:reply-to:content-type; bh=jituh8qc2FB9nfu10IYzWwX6ck5/WIRTdLeEm0Ca4Zg=; b=gavFwrOhwo2GcPUDLaD6ve99gcOJTml1JmZHzl4efwjqaHwWOrBRYsug6aYm6bHN1R tJvlk1yUiewG1EuMLbwFEI54SLwO96mWKjwJ7vMkeezeVngBTFN2b7rD0FXYFphtzAFo cB3nCIwZ7Le2LII2ASX4lByp2Ykb3gvI8kJfKItgxhrkj1n1zGxT+XS81YJQsGnMm/PE jseE8z+HzbVitzYoDoZQWpLVl7MESIF++YcBnd/k4R2NML53VNfKj/hlW5GevM53X0XX FBIE2LmHceRWKD9J7xAb5OD+RIqlFYQhoayMWssD4t4s491/fxf+YjNHG3P3ZFxRZ6Y7 hC1Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1793310027; x=1793914827; h=mime-version:user-agent:content-transfer-encoding:content-type :references:in-reply-to:date:to:from:subject:message-id:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to :content-type; bh=jituh8qc2FB9nfu10IYzWwX6ck5/WIRTdLeEm0Ca4Zg=; b=LsqbG1T53Zgikrrb+rba5ggEyimzI7vPEBs+mhhOJWMn/hNAW20lq5QGUvt0Qk4UtV Fg1qX7wVJmPxnCItImpHbRVA6lcNWjZ/wPkc8bSq7ZiB5gSdxFYiTthOBPAW8BmcNDXg mLZ/vo4GIEuF8PHC98pjRJ0hcUhT5ETxS+MpRTHaeJJG52MG0lXCT3wOYnLWTSP19DtC 2WjvucPUkaUdZrnH5H22F7hhw1Uusrm06s/UqX27Vlh+Fs0nRpQ06reteKeySdHomV5f Grr0y0ayCYA6zOh2NONUOU3U2NucUbKlBU1UlxJf4YR0pAiOQwsTUpvwrN9Xg8OLzCAw sJOw== X-Forwarded-Encrypted: i=1; AKwUvBwiX+5EInIyWzJbWZ3UnKzz/puFZHAiLE4lzvrWunGp8r4C/ZGpLMpi4nJyGdyFtZcz2xBv3E5bGgaH@lists.postgresql.org X-Gm-Message-State: AFq9FYINrmQvPK5bOixgZ92W8oNNR5pwo/nyoBEVK4Gio2d+1X6VrE7/ V2GuXnTuOeAeBd1+dcADvkdPT2DFjvy7sqfjpEXBsM8axVi0n0vjp4EHDsXEqM7KjHU= X-Gm-Gg: AYBFou1zCAJbxugOo++um4Hg26HHkW4d7hoEnA3DFI5IwqsELpj9K4Entl7rurhuMjY JT+Emo9LI6bNI72tyzGBKj/GdU1gP3q1l/TJC7XkftKanliZQigk5NBKa+6sXGkiGCXpCdVfLSz k3UR67mwGY+KFD9UsHdrPdoUPtkoOK2Mv6uKtq1IGwHTaG7K4QueDh4ZIC7OCwUMaPVc9QI8Zk3 28PdxYYBN3uoR9aAVPjSH12Vs6IASS7IPPhMqGvZCSK7ypbXpj8VjrcvMd3QDro4BhROfgKlhlz 6epvT/rolObO7U21mnHQ5ApGt+ruOYQ96VI+6FpExsuVJwLnpu6JgjHDRvaqNQp1SQN99tFuJpo kRizB7RDMlP11/OXWqu5KbfOGVXGdy++KLVBulxmRPtSC8yLB8b0mR04cMDm6742jsnyz4umDDv 7jIPZLLlbV8ubz6QxrflYGfUxH8QIiy9KI4tqGgdxIH9RwM3ke19geLygDIsogUUrxgQhhOqVHg uhMV5BcgZVJb3dxtEAUtVSgv+K9ITIvQp0nbZG5vBDW4iBhkaomJYhQzIa05xhh3mPFT57BaAYh P5Xe3QcEUHOQ58i4xHKvf2/JgPnnSZfPgwyVADl3c1KFCIfAvJxUsQJ878RG9w== X-Received: by 2002:a05:600c:8b63:b0:49d:34:420d with SMTP id 5b1f17b1804b1-4ac17ff9d41mr5423795e9.20.1793310027166; Thu, 29 Oct 2026 14:40:27 -0700 (PDT) Received: from laurenz.albe-K4N0CV00F97414D ([2001:871:270:b677:677f:591c:a18a:2956]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-48e6eba104fsm924720f8f.55.2026.10.29.14.40.25 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 29 Oct 2026 14:40:26 -0700 (PDT) Message-ID: <5c53c98046b8e273dfadaba5bfd92f8e36728bc9.camel@cybertec.at> Subject: Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters From: Laurenz Albe To: Gaurav Singh , pgsql-bugs@lists.postgresql.org Date: Thu, 29 Oct 2026 22:40:24 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.60.2 (3.60.2-2.fc44) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2026-10-30 at 02:30 +0530, Gaurav Singh wrote: > age(timestamptz, timestamptz) is marked IMMUTABLE, but its result > depends on the TimeZone setting, because it converts both values to > local time before subtracting: >=20 > SET TimeZone =3D 'UTC'; > SELECT age('2026-03-01 00:00+00'::timestamptz, '2026-01-31 00:00+00'::tim= estamptz); > -- 1 mon 1 day >=20 > SET TimeZone =3D 'America/New_York'; > SELECT age('2026-03-01 00:00+00'::timestamptz, '2026-01-31 00:00+00'::tim= estamptz); > -- 29 days >=20 > Because of the IMMUTABLE label, postgres_fdw considers age() safe to > send to the remote server. The remote session runs with > timezone =3D 'GMT', so the filter is evaluated in GMT instead of the > local session's time zone, and the query returns wrong rows. >=20 > The mislabel itself was raised recently in the BUG #19644 thread, > where the concern was that relabeling it STABLE would break existing > generated columns and indexes. >=20 > 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. Yours, Laurenz Albe