Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Tue, Jul 18, 2017 at 2:24 AM, Mark Rofail <markm.rofail@gmail.com> wrote:

On Wed, Jul 12, 2017 at 12:53 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

We have one opclass for each type combination -- int4 to int2, int4 to
int4, int4 to int8, etc.  You just need to add the new strategy to all
the opclasses.

 I tried this approach by manually declaring the operator multiple of times in pg_amop.h (src/include/catalog/pg_amop.h)

so instead of the polymorphic declaration
DATA(insert ( 2745   2277 2283 5 s 6108 2742 0 )); /* anyarray @>> anyelem */

multiple declarations were used, for example for int4[] :

DATA(insert ( 2745   1007 20 5 s 6108 2742 0 )); /* int4[] @>> int8 */

DATA(insert ( 2745   1007 23 5 s 6108 2742 0 )); /* int4[] @>> int4 */

DATA(insert ( 2745   1007 21 5 s 6108 2742 0 )); /* int4[] @>> int2 */

DATA(insert ( 2745   1007 1700 5 s 6108 2742 0 ));/* int4[] @>> numeric */

However, make check produced:
could not create unique index "pg_amop_opr_fam_index"
Key (amopopr, amoppurpose, amopfamily)=(6108, s, 2745) is duplicated.

Am I implementing this the wrong way or do we need to look for another approach? 

The problem is that you need to have not only opclass entries for the operators, but also operators themselves.  I.e. separate operators for int4[] @>> int8, int4[] @>> int4, int4[] @>> int2, int4[] @>> numeric.  You tried to add multiple pg_amop rows for single operator and consequently get unique index violation.

Alvaro, do you think we need to define all these operators?  I'm not sure.  If even we need it, I think we shouldn't do this during this GSoC.  What particular shortcomings do you see in explicit cast in RI triggers queries?