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 1 WHERE
(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 table?
If so, we don't introduce additional restriction here...
This is what I came up with:
SELECT 1 WHERE
(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
