Re: FK v.s unique indexes

On Thu, Jul 5, 2018 at 2:36 PM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:

>
>
> W dniu 05.07.2018 o 23:04, David G. Johnston pisze:
> > On Thu, Jul 5, 2018 at 1:45 PM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu
> > <mailto:rafal(at)ztk-rp(dot)eu>>wrote:
> >
> > I was thinking, that when "add constraint" cannot choose appropriate
> > index, may be some explicit help (like ... using <index_name>;)
> would be
> > due.
> >
> >
> > ​Basically all the FK trigger does is:
> >
> > SELECT EXISTS(SELECT 1 FROM pk_table WHERE pk_col1 = val1 AND pk_col2 =
> > val2)
> > ​
> > And fails if query returns false. The planner is still free to use the
> > index or not to execute the query just as if you had written it by
> > hand. For a small table fully in memory it likely would prefer a
> > sequential scan and it would be perfectly within its rights to do so.
> >
>
> I must assume, that the above "WHERE pk_col1 - val1..." is not the same
> place as the one you've mentioned earlier, where "FK don't have where
> clausures".
>

​The FK definition doesn't have a WHERE clause so the only (and all)
columns used in the trigger are those defined by the constraint itself.
So, if a partial index for the above was:

UNIQUE INDEX (pk_col1, pk_col2) WHERE pktblcol3 = false;

and

FK (col1, col2) REFERENCES pk_table (pk_col1, pk_col2)​

​There is no place on the FK to reference "pktblcol3​" so that the
effective trigger query would become:

WHERE pk_col1 = val2 AND pk_col2 = val2 AND pktblcol3 = ???

And without pktblcol3 more than one row could be returned (so, not really
EXISTS...)

>
> Thus, the bulk of code that maintains FK consistency "does not use where
> clausures", would break on partial indexes. But in the above SELECT,
> with partial indexed, that select will never fail. So it is not the
> blocker... on the other hand, I would rather rewrite it along the lines of:
> SELECT 1 = (SELECT count(1) FROM pk_table WHERE pk_col1 = val1 AND
> pk_col2 = val2)
>

​Yeah, that's closer to reality
​

>
> Naturally, if the planner choses to do a seq scan, everything would
> break down - inappropriate rows would get hit; but that's a different
> story:
> 1. one may think, doing a "SELECT * FROM pk_table WHERE... INTO current"
> and making all constraint trigger functions use that "current" instead
> of making more lookups, could solve the problem.
> 2. or with some syntax help during FK creation (aka: ... add constraint
> ... using <index_name>) one could force the planner to always use
> indicated index.
>

​This comes back to constraints don't directly target indexes even though
indexes are used in implementation (it would be violation of scope). They
target other constraints at a logical level. Whether the code is written
exactly like that without evidence to the contrary it provides a sound
mental model to operate from.

I think we might get away from this for INSERT ON CONFLICT but I'm not that
well versed nor have time to look into it right now.

David J.
​