agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19680: FK integrity bypassed by session timezone (orphan rows)
Date: Tue, 08 Sep 2026 06:58:16 +0000
Message-ID: <19680-4ff463d4a8e2b961@postgresql.org> (raw)
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:
PG permits creating foreign keys between types whose equality operator is
timezone-dependent (timestamp = timestamptz, and date = timestamptz). The 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 — 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 = 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.
Message-ID: <19680-4ff463d4a8e2b961@postgresql.org>
Permalink: ../19680-4ff463d4a8e2b961@postgresql.org/
Also on: postgresql.org/message-id/19680-4ff463d4a8e2b961@postgresql.org
reply
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 303677365@qq.com
Subject: Re: BUG #19680: FK integrity bypassed by session timezone (orphan rows)
In-Reply-To: <19680-4ff463d4a8e2b961@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox