public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Tom Lane <[email protected]>
To: [email protected]
Subject: Re: Mutable foreign key constraints
Date: Fri, 13 Sep 2024 04:41:40 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Mutable foreign key constraints
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox