Subject: Re: Mutable foreign key constraints

From: Laurenz Albe <[email-masked]>
Date: Fri, 13 Sep 2024 04:41:40 +0200

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

---

From: Andreas Karlsson <[email-masked]>
Date: Fri, 13 Sep 2024 15:05:49 +0200

> That's very broken and should not be allowed.

+1

> I agree that it cannot be backpatched.

I unfortunately suspect there might be more cases than we think in the 
field due to many people not understanding the difference between 
timestamp and timestamptz but the good thing is that 
timestamp/timestamptz are rare in foreign keys, even in composite ones.

Since this is quite broken and does not have any real world usefulness I 
think we should just go ahead and disallow it and have a few people 
complain.

Andreas

---

From: Vik Fearing <[email-masked]>
Date: Fri, 13 Sep 2024 15:38:03 +0200

> timestamp/timestamptz are rare in foreign keys, even in composite ones.

It will become a lot more common with WITHOUT OVERLAPS, so I think it is 
important to fix this at the same time or earlier as that feature.

+1
-- 
Vik Fearing