Re: Mutable foreign key constraints

On 2024-09-12 Th 5:33 PM, 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. 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?
>
>

Isn't there an upgrade hazard here? People won't thank us if they can't
now upgrade their clusters. If we can get around that then +1.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com