From: | Alexander Korotkov <aekorotkov(at)gmail(dot)com> |
---|---|
To: | Mark Rofail <markm(dot)rofail(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>, David Steele <david(at)pgmasters(dot)net>, Stephen Frost <sfrost(at)snowman(dot)net>, pgsql-hackers-owner(at)postgresql(dot)org, Erik Rijkers <er(at)xs4all(dot)nl> |
Subject: | Re: GSoC 2017: Foreign Key Arrays |
Date: | 2017-08-08 12:25:39 |
Message-ID: | CAPpHfdsFyMtmh5m2mA=kRkK_uD8hgBp8mNo2U7jZBmFXQP7DBw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Sat, Aug 5, 2017 at 11:36 PM, Mark Rofail <markm(dot)rofail(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeevan Chalke | 2017-08-08 12:34:56 | Server crash (FailedAssertion) due to catcache refcount mis-handling |
Previous Message | Rajkumar Raghuwanshi | 2017-08-08 10:40:42 | Re: reload-through-the-top-parent switch the partition table |