Re: GSoC 2017: Foreign Key Arrays

On Mon, Aug 14, 2017 at 2:09 PM, Mark Rofail <markm(dot)rofail(at)gmail(dot)com> wrote:

> 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.
>

I'm not sure numerical comparison of Oids is a good idea. AFAIK, any
regularity of Oids assignment is coincidence... Also, consider
user-defined data types: their oids depend on order of their creation.
Should we instead use logic similar to select_common_type() and underlying
functions?

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company