From: Tom Lane <tgl@sss.pgh.pa.us>
To: sahil mahajan <sahilmahajanstar@gmail.com>
cc: 303677365@qq.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19680: FK integrity bypassed by session timezone (orphan
 rows)
In-reply-to: 
 <CAAR_GTEoHfXVR+193QC0186Q_3_6pz9JFKa4VRJbN9qt2v_Utw@mail.gmail.com>
References: <19680-4ff463d4a8e2b961@postgresql.org>
 <CAAR_GTEoHfXVR+193QC0186Q_3_6pz9JFKa4VRJbN9qt2v_Utw@mail.gmail.com>
Comments: In-reply-to sahil mahajan <sahilmahajanstar@gmail.com>
	message dated "Mon, 14 Sep 2026 21:09:18 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <128112.1789401628.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 14 Sep 2026 12:00:28 -0400
Message-ID: <128113.1789401628@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/128113.1789401628%40sss.pgh.pa.us>
Precedence: bulk

sahil mahajan <sahilmahajanstar@gmail.com> writes:
> Thanks for bringing this up. I=E2=80=99ve been looking into the root cau=
se of this,
> and it essentially boils down to a type mismatch issue.

> Because PostgreSQL permits Foreign Keys between timestamp and timestampt=
z,
> the RI trigger machinery must rely on an equality operator to bridge the
> two types. The timestamp =3D timestamptz operator is STABLE (not IMMUTAB=
LE)
> because it performs a cast that depends on the current session's TimeZon=
e
> 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 volatil=
ity
> 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 <=3D 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