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 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
