agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19680: FK integrity bypassed by session timezone (orphan rows)
3+ messages / 3 participants
[nested] [flat]
* BUG #19680: FK integrity bypassed by session timezone (orphan rows)
@ 2026-09-08 06:58 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-09-08 06:58 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19680: FK integrity bypassed by session timezone (orphan rows)
@ 2026-09-14 15:39 sahil mahajan <sahilmahajanstar@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: sahil mahajan @ 2026-09-14 15:39 UTC (permalink / raw)
To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org
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@postgresql.org> wrote:
> 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.
>
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19680: FK integrity bypassed by session timezone (orphan rows)
@ 2026-09-14 16:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: sahil mahajan <sahilmahajanstar@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2026-09-14 16:00 UTC (permalink / raw)
To: sahil mahajan <sahilmahajanstar@gmail.com>; +Cc: 303677365@qq.com; pgsql-bugs@lists.postgresql.org
sahil mahajan <sahilmahajanstar@gmail.com> writes:
> 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:
AFAICS, setting up a foreign key like this is simply user error,
at least in a database where anyone ever changes the timezone setting.
I feel no great need to do anything about it. As you say, we could
refuse creation of such foreign-key constraints, but that's unlikely
to make anyone happier and could break databases that have been working
fine for their users' purposes. (If we'd mandated that from the
beginning, probably no one would have complained, but we failed to
and now maybe someone is depending on such a setup.) Your other idea
of trying to constrain the execution environment seems entirely
unworkable, since we don't know which environmental details a STABLE
function might depend on; and even if it were workable would be far
more effort than is justified.
As a comparison point, we recommend but don't require that CHECK
constraints be immutable. So there's plenty of ways to build a
foot-gun there too. I recall there have been past discussions around
whether that should be tightened up, and the answer has been "no, it's
sometimes useful". An example is "CHECK (mytimestamp <= now())" as
a filter for bogus input.
Maybe there is room for a documentation warning about non-immutable
foreign key comparisons, but I don't think changing the behavior is
going to fly.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-14 16:00 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-08 06:58 BUG #19680: FK integrity bypassed by session timezone (orphan rows) PG Bug reporting form <noreply@postgresql.org>
2026-09-14 15:39 ` sahil mahajan <sahilmahajanstar@gmail.com>
2026-09-14 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox