Re: multiple UNIQUE indices for FK - Mailing list pgsql-general

Hi Rafal:

On Fri, Mar 4, 2016 at 11:44 AM, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
> While doing so I fell onto another problem, to which I cannot find any
> resolve so far.
...
> 2. but in the original schema I did have an additional field NEXT, which
> allowed me to dasy-chain all messages originating from a particular
> sender, and just one message (the most recent one) did have it a NULL
> there, so it was easy to peek the last message (which is a frequent
> operation).

I do not recall your original schema too well, but IMO doing
linked-lists with database records is not usually a good idea. They
are very procedural and relational is declarative. Normally to peek at
the last message from a chain you just declare your intentions in sql,
typically by having a timestamp column and doing select whatever where
whatelse order by xxx_ts desc limit 1.

> 3. currently, having just partially-unique index on messages-persons
> table for senders, I'm unable to FK (person,role,next) to (person,role,ssn).

This is normally a sympton of your schema not being appropiately normalized.

> Postgres complains, that FK columns MUST have an unconditional unique
> index at its target columns.

I would expect this, a foreign key must uniquely determine a row on
another table, postgres insures this with unconditional unique index.
This is because FK target tables, not indexes. If you are targetting a
conditional index probably you want another type of constraint.

> A) how to get around it?

Do not use FK. Try to use generic constraints. Better , normalize your
schema, IIRC it was not even in 2NF, and this tends to be asking for
problems.

> B) why that unique index at the target have to be unconditional? I mean:
> --> if the table was split into two inharited tables (one for
> role-sender, one for role-recepient), the partition table containing
> only role-sender could have a full-unique index and thus could become
> target for FK(sender,next).
> --> so why rdbms cannot treat partial indexes just like that: as if
> those where full-unique-indexes, but only covering part of the data. And
> consequently if there was a 'partial-index-miss', the target key is
> assumed as not present.

> I'd apreciate any help in how should I implement the chaining of rows in
> messages-persons table (like above); and some info on the "theory of
> rdbms" (or clasure in standard specs) which lead to restrictions
> preventing partial indexes as FK targets,

Partial indexes, even indexes in general, are implementation details
in "theory of rdbms". This theory is more matemathics, based on
tuples, sets, and the like.  Normally FK wants unique keys as targets,
the fact that many dbms force a unique index for these is an
implementation detail, you can have a unique constraint by just
scanning the table on every insertion / update, it will be slow but
will work ( and in some cases, like extremely small tables would even
be much better than indexes ).

From what you write your way of operation reminds me of when I worked
with COBOL and indexed files, you try to use the indexes, directly,
and make the rdbms use them automatically for some ops like you would
do in a one of this systems, but that is not the way rdbms work, they
like to have a declared structure and decide by themselves what to do.
This is nice in that once you write a query you can partition, add
indexes, create views, and let the rdbms work out how to do it, but
imposes some ( some would say a lot ) constraints in how you put your
data in.

Francisco Olarte.