From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Subject: Mutable foreign key constraints
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4162597.1726176826.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 12 Sep 2024 17:33:46 -0400
Message-ID: <4162598.1726176826@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/4162598.1726176826%40sss.pgh.pa.us>
Precedence: bulk

I happened to notice that Postgres will let you do

regression=3D# create table foo (id timestamp primary key);
CREATE TABLE
regression=3D# create table bar (ts timestamptz references foo);
CREATE TABLE

This strikes me as a pretty bad idea, because whether a particular
timestamp is equal to a particular timestamptz depends on your
timezone setting.  Thus the constraint could appear to be violated
after a timezone change.

I'm inclined to propose rejecting FK constraints if the comparison
operator is not immutable.  Among the built-in opclasses, the only
instances of non-immutable btree equality operators are

regression=3D# select amopopr::regoperator from pg_amop join pg_operator o=
 on o.oid =3D amopopr join pg_proc p on p.oid =3D oprcode where amopmethod=
=3D403 and amopstrategy=3D3 and provolatile !=3D 'i';
                         amopopr                         =

---------------------------------------------------------
 =3D(date,timestamp with time zone)
 =3D(timestamp without time zone,timestamp with time zone)
 =3D(timestamp with time zone,date)
 =3D(timestamp with time zone,timestamp without time zone)
(4 rows)

A possible objection is that if anybody has such a setup and
hasn't noticed a problem because they never change their
timezone setting, they might not appreciate us breaking it.
So I certainly wouldn't propose back-patching this.  But
maybe we should add it as a foot-gun defense going forward.

Thoughts?

			regards, tom lane