Message-ID: <560b5873c9b8129a4b7b4fe3239ec32363168599.camel@cybertec.at>
Subject: Re: Mutable foreign key constraints
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@lists.postgresql.org
Date: Fri, 13 Sep 2024 04:41:40 +0200
In-Reply-To: <4162598.1726176826@sss.pgh.pa.us>
References: <4162598.1726176826@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/560b5873c9b8129a4b7b4fe3239ec32363168599.camel%40cybertec.at>
Precedence: bulk

On Thu, 2024-09-12 at 17:33 -0400, Tom Lane wrote:
> I happened to notice that Postgres will let you do
>=20
> regression=3D# create table foo (id timestamp primary key);
> CREATE TABLE
> regression=3D# create table bar (ts timestamptz references foo);
> CREATE TABLE
>=20
> 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.
>=20
> I'm inclined to propose rejecting FK constraints if the comparison
> operator is not immutable.

I think that is the only sane thing to do.  Consider

  test=3D> SHOW timezone;
     TimeZone   =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
   Europe/Vienna
  (1 row)

  test=3D> INSERT INTO foo VALUES ('2024-09-13 12:00:00');
  INSERT 0 1
  test=3D> INSERT INTO bar VALUES ('2024-09-13 12:00:00+02');
  INSERT 0 1
  test=3D> SELECT * FROM foo JOIN bar ON foo.id =3D bar.ts;
           id          =E2=94=82           ts          =20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
   2024-09-13 12:00:00 =E2=94=82 2024-09-13 12:00:00+02
  (1 row)

  test=3D> SET timezone =3D 'Asia/Kolkata';
  SET
  test=3D> SELECT * FROM foo JOIN bar ON foo.id =3D bar.ts;
   id =E2=94=82 ts=20
  =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90
  (0 rows)

  test=3D> INSERT INTO foo VALUES ('2024-09-14 12:00:00');
  INSERT 0 1
  test=3D> INSERT INTO bar VALUES ('2024-09-14 12:00:00+02');
  ERROR:  insert or update on table "bar" violates foreign key constraint "=
bar_ts_fkey"
  DETAIL:  Key (ts)=3D(2024-09-14 15:30:00+05:30) is not present in table "=
foo".

That's very broken and should not be allowed.

> 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.

I hope that there are few cases of that in the field, and I think it
is OK to break them.  After all, it can be fixed with a simple

  ALTER TABLE foo ALTER id TYPE timestamptz;

If the session time zone is UTC, that wouldn't even require a rewrite.

I agree that it cannot be backpatched.

Yours,
Laurenz Albe