Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19680: FK integrity bypassed by session timezone (orphan rows)
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 303677365@qq.com
Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org
Date: Tue, 08 Sep 2026 06:58:16 +0000
Message-ID: <19680-4ff463d4a8e2b961@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19680-4ff463d4a8e2b961%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19680
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   x86_64
Description:       =20

PG permits creating foreign keys between types whose equality operator is
timezone-dependent (timestamp =3D timestamptz, and date =3D timestamptz). T=
he RI
reverse check (the scan for referencing rows when deleting/updating the PK
row) converts the PK value into the FK column's type using the current
session's TimeZone. As a result, the same FK constraint answers differently
depending on the session's time zone, and a referencing row becomes
invisible to the check:
CREATE TABLE tzpk(ts timestamp PRIMARY KEY);
CREATE TABLE tzfk(id int, tstz timestamptz REFERENCES tzpk(ts));

SET timezone TO 'UTC';
INSERT INTO tzpk VALUES ('2024-06-15 00:00:00');
INSERT INTO tzfk VALUES (1, '2024-06-15 00:00:00');   -- valid reference
under UTC

SET timezone TO 'Asia/Tokyo';
DELETE FROM tzpk;                                      -- SUCCEEDS =E2=80=
=94 no
error!

SET timezone TO 'UTC';
SELECT count(*) FROM tzfk;                             -- 1  (orphan row:
violates the FK)
SELECT EXISTS (SELECT 1 FROM tzpk WHERE tzfk.tstz =3D tzpk.ts) FROM tzfk;  =
--
false
INSERT INTO tzpk VALUES ('2024-06-15 00:00:00');       -- the "deleted" PK
can even be re-created

Control: under the same time zone, the identical DELETE is correctly
rejected (ERROR: update or delete on table "tzpk" violates foreign key
constraint). Only the time-zone switch is needed to bypass the constraint.
Silent referential-integrity violation with no error, no log, and no way to
detect it afterwards except querying across time-zone contexts. Any
deployment that (a) has such a cross-type FK and (b) has sessions with
differing TimeZone settings (extremely common: connection pools per region,
psql defaults vs app-server settings) can accumulate orphans. The FK
constraint's guarantee is void for these type pairs.