Re: BUG #19680: FK integrity bypassed by session timezone (orphan rows) - Mailing list pgsql-bugs

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