Обсуждение: it's a feature, but it feels like a bug

Hi,

I've bumped onto the following problem:
---------------------screenshot----------------------------
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"
ztk=#
-----------------------------------------------------------

BTW: the "rightone" index above only "reflects my actual schema" and
does not give much to the axample.

Now I know it's a "feature".

But it really hurts my dataset/schema "coexistence".

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? a unique index is there to indicate exactly one row as target
for the FK. This is what LEFTONE index does. Surely, some part of the
TEST table will never be accesable for TEST(A,E) as FK targets; but that
(in the case of my dataset) is exactly what is expected.

Thus, It feels like a bug.

So my questions are:
1. is there a well known reason for not allowing it? (i.e. ignoring
partial unique indexes when looking up FK support index)
2. I hope that there isn't; So: would it break things if I try to patch
sources to allow for it?
3. I admit I'm not so much of a hacker, but at this point I'm so
desperate to have it done, that I'll try anyway. So if the above is
doable at all, where should I start reading sources?

Thank you,

-R

Hi,

I've bumped onto the following problem:
---------------------screenshot----------------------------
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;
 a unique index is there to indicate exactly one row as target
for the FK. This is what LEFTONE index does. 

Rafal Pietrak <r.pietrak@sm.strop.com.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.

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.

            regards, tom lane

On Wed, Feb 07, 2018 at 10:26:50PM -0500, Tom Lane wrote:
> Rafal Pietrak <r.pietrak@sm.strop.com.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

W dniu 08.02.2018 o 04:26, Tom Lane pisze:
> Rafal Pietrak <r.pietrak@sm.strop.com.pl> writes:
[--------------------]
> 
>> 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.

I sort of knew/expected that.

But I'd like to approach the sources anyway.

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

Actually, they are. the explanation of my schema would be lengthy, but
in showt, lets say, I'm talking of a mail-hub, where : A=mbox-owner-id,
B=message-UNIQUE-id, C=the-other-entity-id, D=flas-inbox-outbox'; the
table contains every message anyone send or received. only sender
assigns ID to a message. So:
all outgoing messages have unique (A,B), and D=true
all received messages have unique (B,C), and D=false
those messages are parsed, digested, and they update columns of their
respective rows.
... the tricky part is, that some of them must form explicit lists. This
is column (E). This is why I need to have an FK (E,A) --> (B,A).


Currently, to use FK in this dataset I have the main table split into:
inbox, and outbox. Unfortunately this fires back as the entire schema
effectively has to have twice the number of relations, and FK
interlinking it growing almost as O(2) with tables. At the point that I
am, this is already unmanagable.

So I'm quite desperate to "do it some other way". Like patching postgresql.

I was thinking, that: an attempt to "alter table add constraint ..
foreign key..." could:
a) identify if the target table has ANY sort of UNIQUE index covering
provided list of columns (even if it is a partial index)
b) if that index is only partial, locate the condition and use it during
insert/update/etc.... and retrieval of target row.
c) if that index is functional index, locate that function and use it
during insert/update/etc.

So I'd appreciate some guidence which part of the sources I should study
first.

regards,

-R

W dniu 08.02.2018 o 05:51, David Fetter pisze:
> On Wed, Feb 07, 2018 at 10:26:50PM -0500, Tom Lane wrote:
>> Rafal Pietrak <r.pietrak@sm.strop.com.pl> writes:
[-------------]
> 
> 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);

This is exactly my current setup.

It creates other problems with manageing my dataset, so I'm looking for
other ways to lay down the schema.

thenx,

-R