Re: [HACKERS] GSoC 2017: Foreign Key Arrays - Mailing list pgsql-hackers

From Mark Rofail
Subject Re: [HACKERS] GSoC 2017: Foreign Key Arrays
Date
Msg-id CAJvoCuvTSiKYD5Kt+3n6Fjd0M+4Y9uK6JYrA8F5n_9oh=ixz2w@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] GSoC 2017: Foreign Key Arrays  ("Joel Jacobson" <joel@compiler.org>)
Responses Re: [HACKERS] GSoC 2017: Foreign Key Arrays  ("Joel Jacobson" <joel@compiler.org>)
List pgsql-hackers
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.

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)

On Mon, Feb 15, 2021 at 5:35 PM Joel Jacobson <joel@compiler.org> wrote:
Hi all,

I've reviewed Mark's anyarray_anyelement_operators-v2.patch
and the only remaining issue I've identified is the opr_sanity problem.

Mark seems to be in need of some input here from more experienced hackers, see below.

Hopefully someone can guide him in the right direction.

/Joel

On Sat, Feb 13, 2021, at 11:49, Mark Rofail wrote:
>Hey Joel,
>
>test opr_sanity                   ... FAILED
>
>                    AND binary_coercible(p2.opcintype, p1.amoplefttype));
>  amopfamily | amopstrategy | amopopr
>------------+--------------+---------
>-(0 rows)
>+       2745 |            5 |    6105
>+(1 row)
>-- Operators that are primary members of opclasses must be immutable (else
>-- it suggests that the index ordering isn't fixed).  Operators that are
>This is due using anycompatiblearray for the left operand in @>>. 
>To solve this problem we need to use @>>(anyarray,anyelement) or introduce a new opclass for gin indices. 
>These are the two approaches that come to mind to solve this. Which one is the right way or is there another solution I am not aware of?
>That’s why I’m asking this on the mailing list, to get the community’s input.


Attachment

pgsql-hackers by date:

Previous
From: Justin Pryzby
Date:
Subject: Re: CREATE INDEX CONCURRENTLY on partitioned index
Next
From: Fabien COELHO
Date:
Subject: Re: PG vs LLVM 12 on seawasp, next round