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?
Best Regards,
MarkRofail
