From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Mutable foreign key constraints |
Date: | 2024-09-12 21:33:46 |
Message-ID: | 4162598.1726176826@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nathan Bossart | 2024-09-12 21:39:14 | Re: Pgstattuple on Sequences: Seeking Community Feedback on Potential Patch |
Previous Message | David Rowley | 2024-09-12 21:20:11 | Re: Add memory/disk usage for WindowAgg nodes in EXPLAIN |