Re: GSoC 2017: Foreign Key Arrays

On Tue, Aug 8, 2017 at 3:24 PM, Alexander Korotkov <aekorotkov(at)gmail(dot)com>
wrote:

> On Tue, Aug 8, 2017 at 4:12 PM, Mark Rofail <markm(dot)rofail(at)gmail(dot)com>
> wrote:
>
>> On Tue, Aug 8, 2017 at 2:25 PM, Alexander Korotkov <aekorotkov(at)gmail(dot)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.
<https://www.postgresql.org/message-id/CAJvoCusUmk7iBNf7ak_FdT%2Bb%3Dtot3smRNH9DOjDMUEzNFXgrfg%40mail.gmail.com>

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.
3.If the rightArrayOid is larger we cast the left array, else If the
leftArrayOid is larger we cast the right element to the base element type
of the leftArrayOid

For example:
#= CREATE TABLE PKTABLEFORARRAY ( ptest1 int4 PRIMARY KEY, ptest2 text );
#= CREATE TABLE FKTABLEFORARRAY ( ftest1 int2[], FOREIGN KEY (EACH ELEMENT
OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int );

The left operand here is int2[] and the right int4

1.get int4[] oid and store it
2. compare int4[] and int2[] oid numerically
3. since int4[] is larger the cast is applied to int2[] to make the left
operant int4[]

If the example was reversed:
#= CREATE TABLE PKTABLEFORARRAY ( ptest1 int2 PRIMARY KEY, ptest2 text );
#= CREATE TABLE FKTABLEFORARRAY ( ftest1 int4[], FOREIGN KEY (EACH ELEMENT
OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int );

The left operand here is int4[] and the right int2

1.get int2[] oid and store it
2. compare int4[] and int2[] oid numerically
3. since int4[] is larger the cast is applied to int2 to make the right
operant int 4

This approach works and I have written some tests to verify the approach.

However, if there's a cleaner way to go about this or a more "postgres"
way. let me know.
The changes are int ri_trigger.c and the patch is attached here.

Best Regards,
Mark Rofail