From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Rafal Pietrak <r(dot)pietrak(at)sm(dot)strop(dot)com(dot)pl>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: it's a feature, but it feels like a bug |
Date: | 2018-02-08 04:51:50 |
Message-ID: | 20180208045150.GV18043@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Feb 07, 2018 at 10:26:50PM -0500, Tom Lane wrote:
> Rafal Pietrak <r(dot)pietrak(at)sm(dot)strop(dot)com(dot)pl> writes:
> > ztk=# create table test (a int, b int, c int, d bool, e int, primary key
> > (a,b,c,d));
> > CREATE TABLE
> > ztk=# create unique index leftone on test (a,b) where d is true;
> > CREATE INDEX
> > ztk=# create unique index rightone on test (b,c) where d is false;
> > CREATE INDEX
> > ztk=# alter table ONLY test ADD CONSTRAINT e2b_fk FOREIGN KEY (a,e)
> > REFERENCES test(a,b) ON UPDATE CASCADE;
> > ERROR: there is no unique constraint matching given keys for referenced
> > table "test"
>
> > And it is sort of "couterintuitive" - as you can see, there is a UNIQUE
> > index for test(a,b) target; admitedly partial, but .... why should that
> > matter?
>
> Because the index fails to guarantee uniqueness of (a,b) in rows where d
> isn't true. There could be many duplicates in such rows, possibly even of
> (a,b) pairs that also appear --- though only once --- in rows where d is
> true.
>
> If there were a way to say that the FK is only allowed to reference rows
> where d is true, then this index could support an FK like that. But
> there's no way to express such a thing in SQL.
There will be as soon as we implement ASSERTIONs.
> Personally I'd think about separating your rows-where-d-is-true into
> their own table, which could have a normal PK index. You could
> still create a union view over that table and the one with the other
> rows to satisfy whatever queries want to think the two kinds of rows
> are the same thing. But I'd offer that if one set of rows has (a,b)
> as a PK and the other does not, they are not really the same kind of
> thing.
Another way might be to partition the table on the boolean and make a
foreign key to the "true" partition, e.g.:
CREATE TABLE foo(b BOOLEAN, i INTEGER NOT NULL, t TEXT NOT NULL) PARTITION BY LIST (b);
CREATE TABLE foo_true PARTITION OF foo (PRIMARY KEY(i, t)) FOR VALUES IN ('true');
CREATE TABLE bar(foo_i INTEGER NOT NULL, foo_t TEXT NOT NULL, FOREIGN KEY(foo_i, foo_t) REFERENCES foo_true);
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2018-02-08 05:11:07 | Re: [HACKERS] advanced partition matching algorithm for partition-wise join |
Previous Message | Masahiko Sawada | 2018-02-08 04:36:11 | Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently |