Re: GSoC 2017: Foreign Key Arrays

On Sat, Aug 5, 2017 at 10:36 PM, Mark Rofail <markm(dot)rofail(at)gmail(dot)com> wrote:
>
> SELECT 1 WHERE
> (SELECT COUNT(*)
> FROM
> (
> SELECT y
> FROM unnest($1) y
> GROUP BY y
> )
> )
> = (SELECT count(*) (<QUERY>) z)
>
Well, with trial and error the correct query is:

SELECT 1 WHERE
(SELECT COUNT(y) FROM (SELECT y FROM pg_catalog.unnest(%s) y GROUP BY
y) yy)
= (SELECT count(*) (<QUERY>) z)

This passes all the regress tests and elimnates the DISTINCT keyword that
created the limitation.
However a look on the performance change is a must and that's what I plan
to do next.

There is another problem though. When I remove the part of the code setting
the default opclass in tablecmd.c I get this error message afterwards:
CREATE TABLE PKTABLEFORARRAY ( ptest1 float8 PRIMARY KEY, ptest2 text );
CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF
ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int );
- ERROR: foreign key constraint "fktableforarray_ftest1_fkey" cannot be
implemented
- DETAIL: Key column "ftest1" has element type integer which does not have
a default btree operator class that's compatible with class "float8_ops".

Best Regards,
Mark Rofail