On Sat, Aug 5, 2017 at 11:36 PM, Mark Rofail <markm.rof...@gmail.com> wrote:

> This is the query fired upon any UPDATE/DELETE for RI checks:
> SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND ...] FOR KEY SHARE
> OF x
> in  the case of foreign key arrays, it's wrapped in this query:
>     (SELECT count(DISTINCT y) FROM unnest($1) y)
>     = (SELECT count(*) FROM (<QUERY>) z)
> This is where the limitation appears, the DISTINCT keyword. Since in
> reality, count(DISTINCT) will fall back to the default btree opclass for
> the array element type regardless of the opclass indicated in the access
> method. Thus I believe going around DISTINCT is the way to go.

Do we already assume that default btree opclass for array element type
matches PK opclass when using @>> operator on UPDATE/DELETE of referenced
If so, we don't introduce additional restriction here...

This is what I came up with:
>     (SELECT COUNT(*)
>         FROM
>         (
>             SELECT y
>             FROM unnest($1) y
>             GROUP BY y
>         )
>     )
>     = (SELECT count(*) (<QUERY>) z)
> I understand there might be some syntax errors but this is just a proof of
> concept.

GROUP BY would also use default btree/hash opclass for element type.  It
doesn't differ from DISTINCT from that point.

Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Reply via email to