Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Tue, Aug 8, 2017 at 3:24 PM, Alexander Korotkov <aekorotkov@gmail.com> wrote:

On Tue, Aug 8, 2017 at 4:12 PM, Mark Rofail <markm.rofail@gmail.com> wrote:

On Tue, Aug 8, 2017 at 2:25 PM, Alexander Korotkov <aekorotkov@gmail.com> wrote:

GROUP BY would also use default btree/hash opclass for element type.  It doesn't differ from DISTINCT from that point.

Then there's no going around this limitation,

That seems like this.

Since for now, the limitation

✗ presupposes that count(distinct y) has exactly the same notion of equality that the PK unique index has. In reality, count(distinct) will fall back to the default btree opclass for the array element type. 

is unavoidable.

I started to look at the next one on the list. 

✗ coercion is unsopported. i.e. a numeric can't refrence int8

The limitation in short.

#= CREATE TABLE PKTABLEFORARRAY ( ptest1 int4 PRIMARY KEY, ptest2 text );

#= CREATE TABLE FKTABLEFORARRAY ( ftest1 int2[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int );

should be accepted but this produces the following error

operator does not exist: integer[] @> smallint

The algorithm I propose:
I don't think it's easy to modify the @>> operator as we discussed here.

I think we should cast the operands in the RI queries fired as follows
1. we get the array type from the right operand

2. compare the two array type and see which type is more "general" (as to which should be cast to which, int2 should be cast to int4, since casting int4 to int2 could lead to data loss). This can be done by seeing which Oid is larger numerically since, coincidentally, they are declared in this way in pg_type.h.