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

Hi,
Thanks for bringing this up. I’ve been looking into the root cause of this,
and it essentially boils down to a type mismatch issue.

Because PostgreSQL permits Foreign Keys between timestamp and timestamptz,
the RI trigger machinery must rely on an equality operator to bridge the
two types. The timestamp = timestamptz operator is STABLE (not IMMUTABLE)
because it performs a cast that depends on the current session's TimeZone
GUC.

When an RI trigger (like RI_FKey_noaction_del) fires, it executes the
equality check under whatever the current session timezone happens to be,
rather than the timezone active during the original insert. This volatility
allows the RI check to silently fail and leave orphaned rows behind.

To resolve this, I see two potential architectural approaches:

*Approach 1:* Enforce IMMUTABLE equality operators for Foreign Keys
(Proposed) We modify ATAddForeignKeyConstraint to check the volatility of
the equality operator (pfeqop). If the operator is not
PROVOLATILE_IMMUTABLE, we throw an ERRCODE_DATATYPE_MISMATCH and refuse to
create the Foreign Key.

*Pros: *This is mathematically correct. A foreign key represents a strict,
deterministic relationship. Just like we require index expressions to be
immutable, the equality check bridging an FK should be held to the same
standard. It prevents the database from ever entering a logically flawed
state.
*Cons* (Trade-off): Backward compatibility. Existing users who have built
schemas relying on this (broken) timestamp to timestamptz relationship will
face issues during pg_upgrade or pg_restore, as the constraints will be
rejected.

*Approach 2*: Force RI checks to execute in a fixed, consistent context We
attempt to fix the RI trigger machinery so that it ignores the current
session timezone and forces execution in a fixed context (e.g., UTC).

*Pros: *Preserves backward compatibility and allows users to keep their
existing cross-type foreign keys.
*Cons*: It's logically flawed and practically impossible. PostgreSQL does
not store the original session timezone that a row was inserted under. If a
user inserted a timestamp in Tokyo time, forcing the RI check to evaluate
it as UTC later will still result in a mismatch against the stored
timestamptz.

*Conclusion*: I believe Approach 1 is the only correct path forward, as it
addresses the root logical flaw rather than trying to patch the trigger
execution context.

Since *Approach 1* introduces breaking changes, I'd love to hear the
community's thoughts. Specifically, how should we handle existing databases
during pg_upgrade if we introduce this restriction? Are there any other
heavily used STABLE cross-type foreign keys that this would unfairly break?

Best regards,
Sahil

On Mon, Sep 14, 2026 at 7:35 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> 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.
>
>
>
>
>
>
>