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

sahil mahajan <sahilmahajanstar(at)gmail(dot)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