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
#= 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.
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
#= 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[]
#= 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.
