Re: [HACKERS] GSoC 2017: Foreign Key Arrays
| От | Mark Rofail |
|---|---|
| Тема | Re: [HACKERS] GSoC 2017: Foreign Key Arrays |
| Дата | |
| Msg-id | CAJvoCutZWfFjT7YDrvM-XxERBhq2aBoRxR9dXtCAWHo8W3Uzng@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [HACKERS] GSoC 2017: Foreign Key Arrays (Alexander Korotkov <aekorotkov@gmail.com>) |
| Ответы |
Re: [HACKERS] GSoC 2017: Foreign Key Arrays
|
| Список | pgsql-hackers |
On Tue, Aug 8, 2017 at 3:24 PM, Alexander Korotkov <aekorotkov@gmail.com> wrote:
On Tue, Aug 8, 2017 at 4:12 PM, Mark Rofail <markm.rofail@gmail.com> wrote: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.
The limitation in short.
should be accepted but this produces the following erroroperator 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
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
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
For example:
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
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:
This approach works and I have written some tests to verify the approach.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
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
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
Вложения
В списке pgsql-hackers по дате отправления: