From: | Alexander Korotkov <aekorotkov(at)gmail(dot)com> |
---|---|
To: | Mark Rofail <markm(dot)rofail(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>, David Steele <david(at)pgmasters(dot)net>, Stephen Frost <sfrost(at)snowman(dot)net>, pgsql-hackers-owner(at)postgresql(dot)org, Erik Rijkers <er(at)xs4all(dot)nl> |
Subject: | Re: GSoC 2017: Foreign Key Arrays |
Date: | 2017-08-14 20:35:22 |
Message-ID: | CAPpHfdtpJZheekg6LhQb7fPxsUF4p9h3qRiBgqATZbhdFPD=ZQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-08-14 20:37:09 | Re: pl/perl extension fails on Windows |
Previous Message | Peter Geoghegan | 2017-08-14 20:28:33 | Re: Crash report for some ICU-52 (debian8) COLLATE and work_mem values |