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