Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Mon, Feb 15, 2021, at 20:34, Mark Rofail wrote:
>Dear All,
>
>I know that avoiding trivial coercion problems is convenient but at the end of the day,
>it's the DB Architect's job to use the proper tables to be able to use FK Arrays.
>For instance, if we have two tables, TABLE A (atest1 int2) and TABLE B (btest1 int, btest2 int4[])
>and an FK constraint between A(atest1) and B(btest2), it simply shouldn't work. btest2 should be of type int2[].
>Thus, I have reverted back the signature @>>(anyarray,anyelement) and <<@(anyelement,anyarray).
>I am open to discuss this if anyone has any input, would be appreciated.

I agree, I think this is a wise decision.
This reduces complexity to the actual need for fk_arrays_elem_v1.patch,
and eliminates an entire class of possible bugs.

>Please find the "anyarray_anyelement_operators-v3.patch" attached below.
>Changelog:
>- v3 (compatible with current master 2021-02-15, commit 0e5290312851557ee24e3d6103baf14d6066695c)
> * refactor ginqueryarrayextract in ginarrayproc.c
> * revert back the signature @>>(anyarray,anyelement) and <<@(anyelement,anyarray)

Hmm, I think it looks like you forgot to update the documentation?

It still says anycompatiblenonarray:

@ doc/src/sgml/func.sgml
+ <type>anyarray</type> <literal>@&gt;&gt;</literal> <type>anycompatiblenonarray</type>
+ <type>anycompatiblenonarray</type> <literal>&lt;&lt;@</literal> <type>anyarray</type>

@ src/sgml/gin.sgml
+ <entry><literal>@&gt;&gt; (anyarray,anycompatiblenonarray)</literal></entry>
+ <entry><literal>&lt;&lt;@ (anycompatiblenonarray,anyarray)</literal></entry>

Should it be anyelement in combination with anyarray?

Anyway, I've tested the patch, not only using your tests, but also the attached test.

The test has been auto-generated by type-test.sql (attached) mining values
of different types from the regression database, and then ensuring there
are no non-null differences between these three queries:

SELECT value1::type1 = ANY(ARRAY[value2::type2]);
SELECT value1::type1 <<@ ARRAY[value2::type2];
SELECT ARRAY[value2::type1] @>> value1::type2;

It tests a huge number of different type combinations, and reports any problems.

For the values which types could actually be compared (now only when types are the same),
it outputs the queries and results, from which the attached tests have been created.

No problems were found. Good.

/Joel