public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: [email protected]
Subject: Mutable foreign key constraints
Date: Thu, 12 Sep 2024 17:33:46 -0400
Message-ID: <[email protected]> (raw)

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.  Among the built-in opclasses, the only
instances of non-immutable btree equality operators are

regression=# select amopopr::regoperator from pg_amop join pg_operator o on o.oid = amopopr join pg_proc p on p.oid = oprcode where amopmethod=403 and amopstrategy=3 and provolatile != 'i';
                         amopopr                         
---------------------------------------------------------
 =(date,timestamp with time zone)
 =(timestamp without time zone,timestamp with time zone)
 =(timestamp with time zone,date)
 =(timestamp with time zone,timestamp without time zone)
(4 rows)

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.
So I certainly wouldn't propose back-patching this.  But
maybe we should add it as a foot-gun defense going forward.

Thoughts?

			regards, tom lane

view thread (2+ messages)

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