BUG #19680: FK integrity bypassed by session timezone (orphan rows)

The following bug has been logged on the website:

Bug reference: 19680
Logged by: chunling qin
Email address: 303677365(at)qq(dot)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.