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.
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.
Is this the right way to go? It's been a week and I don't think I made significant progress. Any pointers?
