On Mon, Aug 14, 2017 at 2:09 PM, Mark Rofail <markm.rof...@gmail.com> wrote:
> On Tue, Aug 8, 2017 at 3:24 PM, Alexander Korotkov <aekorot...@gmail.com> > wrote: > >> On Tue, Aug 8, 2017 at 4:12 PM, Mark Rofail <markm.rof...@gmail.com> >> wrote: >> >>> On Tue, Aug 8, 2017 at 2:25 PM, Alexander Korotkov <aekorot...@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. > <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