Re: Mutable foreign key constraints

On Thu, 2024-09-12 at 17:33 -0400, Tom Lane wrote:
> I happened to notice that Postgres will let you do
>
> regression=# create table foo (id timestamp primary key);
> CREATE TABLE
> regression=# 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.

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

  test=> SHOW timezone;
     TimeZone
  ═══════════════
   Europe/Vienna
  (1 row)

  test=> INSERT INTO foo VALUES ('2024-09-13 12:00:00');
  INSERT 0 1
  test=> INSERT INTO bar VALUES ('2024-09-13 12:00:00+02');
  INSERT 0 1
  test=> SELECT * FROM foo JOIN bar ON foo.id = bar.ts;
           id          │           ts
  ═════════════════════╪════════════════════════
   2024-09-13 12:00:00 │ 2024-09-13 12:00:00+02
  (1 row)

  test=> SET timezone = 'Asia/Kolkata';
  SET
  test=> SELECT * FROM foo JOIN bar ON foo.id = bar.ts;
   id │ ts
  ════╪════
  (0 rows)

  test=> INSERT INTO foo VALUES ('2024-09-14 12:00:00');
  INSERT 0 1
  test=> 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)=(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