Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-08-19 Thread Mark Rofail
I have a concern that after supporting UPDATE/DELETE CASCADE, the performance would drop. On Thu, Jul 27, 2017 at 12:54 PM, Alexander Korotkov wrote: > > I wonder how may RI trigger work so fast if it has to do some job besides > index search with no results? > Best Regards, Mark Rofail

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-08-08 Thread Mark Rofail
ction. > If so, we don't introduce additional restriction here... > You mean to remove the wrapper query ? > 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, Best Regard, Mark Rofail

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-08-05 Thread Mark Rofail
This is the query fired upon any UPDATE/DELETE for RI checks: SELECT 1 FROM ONLY x WHERE pkatt1 = $1 [AND ...] FOR KEY SHARE OF x in the case of foreign key arrays, it's wrapped in this query: SELECT 1 WHERE (SELECT count(DISTINCT y) FROM unnest($1) y) = (SELECT count(*) FROM () z) Th

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-08-03 Thread Mark Rofail
the experience to answer that Best Regards, Mark Rofail

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-31 Thread Mark Rofail
depending on what indexes happen to be available. > > Err ... > > > I think what you meant to say is that the planner may only choose an > > optimization of this sort when the index's opclass matches the one > > DISTINCT will use, ie the default for the data type. I understand the problem. I am currently researching how to resolve it. Best Regards, Mark Rofail

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-29 Thread Mark Rofail
These are limitations of the patch ordered by importance: ✗ 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. - Supported actions: ✔ NO ACTIO

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-28 Thread Mark Rofail
On Fri, Jul 28, 2017 at 1:19 PM, Erik Rijkers wrote: > One small thing while building docs: > > $ cd doc/src/sgml && make html > osx -wall -wno-unused-param -wno-empty -wfully-tagged -D . -D . -x lower > postgres.sgml >postgres.xml.tmp > osx:ref/create_table.sgml:960:100:E: document type does no

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-27 Thread Mark Rofail
of the referencing array or the whole array. I think there's a lot of options the user might want to consider and it's hard to generalize to DELETE CASCADE. Maybe new grammar would be introduced here ?| Best Regards, Mark Rofail

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-27 Thread Mark Rofail
g/message-id/28617.1351095...@sss.pgh.pa.us [2] https://www.postgresql.org/message-id/CAJvoCutcMEYNFYK8Hdiui-M2y0ZGg%3DBe17fHgQ%3D8nHexZ6ft7w%40mail.gmail.com [3] https://www.postgresql.org/message-id/CAJvoCuuoGo5zJTpmPm90doYTUWoeUc%2BONXK2%2BH_vxsi%2BZi09bQ%40mail.gmail.com Best Regards, Mark Rofail

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-27 Thread Mark Rofail
On Thu, Jul 27, 2017 at 12:54 PM, Alexander Korotkov wrote: > > How many rows of FK table were referencing the PK table row you're > updating/deleting. > I wonder how may RI trigger work so fast if it has to do some job besides > index search with no results? > The problem here is that the only to

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-24 Thread Mark Rofail
extremely difficult to sort all of > the problems that would result from a change in this area. Best Regards, Mark Rofail

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-24 Thread Mark Rofail
> > However, there is a bug that prevented me from testing the third scenario, > I assume there's an issue of incompatible types problem since the right > operand type is anyelement and the supporting procedures expect anyarray. > I am working on debugging it right now. > I have also solved the bu

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-19 Thread Mark Rofail
On Wed, Jul 19, 2017 at 10:08 PM, Alvaro Herrera wrote: > So let's step back a bit, > get a patch that works for the case where the types match on both sides > of the FK, then we review that patch; if all is well, we can discuss the > other problem as a stretch goal. Agreed. This should be a fu

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-19 Thread Mark Rofail
On Wed, Jul 19, 2017 at 7:28 PM, Robert Haas wrote: > Why do we have to solve that limitation? Since the regress test labled element_foreing_key fails now that I made the RI queries utilise @(anyarray, anyelement), that means it's not functioning as it is meant to be.

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-19 Thread Mark Rofail
ally enhances the performance of the RI checks. How can I go about this? Best Regards, Mark Rofail

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-19 Thread Mark Rofail
t4[] @>> int8, int4[] @>> int4, int4[] @>> int2, int4[] @>> numeric. So I think implementing operators is the way to go. Best Regards, Mark Rofail.

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-18 Thread Mark Rofail
On Tue, 18 Jul 2017 at 7:43 pm, Alexander Korotkov wrote: > separate operators for int4[] @>> int8, int4[] @>> int4, int4[] @>> int2, > int4[] @>> numeric. > My only comment on the separate operators is its high maintenance. Any new datatype introduced a corresponding operator should be create

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-18 Thread Mark Rofail
On Tue, 18 Jul 2017 at 7:43 pm, Alexander Korotkov wrote: > On T upue, Jul 18, 2017 at 2:24 AM, Mark Rofail > wrote: > >> On Wed, Jul 12, 2017 at 12:53 AM, Alvaro Herrera < >> alvhe...@2ndquadrant.com> wrote: >>> >>> We have one opclass for e

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-17 Thread Mark Rofail
On Wed, Jul 12, 2017 at 12:53 AM, Alvaro Herrera wrote: > > We have one opclass for each type combination -- int4 to int2, int4 to > int4, int4 to int8, etc. You just need to add the new strategy to all > the opclasses. I tried this approach by manually declaring the operator multiple of time

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-14 Thread Mark Rofail
On Wed, Jul 12, 2017 at 2:30 PM, Mark Rofail wrote: > On Wed, Jul 12, 2017 at 12:53 AM, Alvaro Herrera > wrote: >> >> We have one opclass for each type combination -- int4 to int2, int4 to >> int4, int4 to int8, etc. You just need to add the new strategy to all >>

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-12 Thread Mark Rofail
lution would be external casting BTW now that we've gone through this a little further, it's starting to > look like a mistake to me to use the same @> operator for (anyarray, > anyelement) than we use for (anyarray, anyarray). I agree. Changed to @>> Best Regards, Mark Rofail

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-11 Thread Mark Rofail
here are the modifications to ri_triggers.c On Wed, Jul 12, 2017 at 12:26 AM, Mark Rofail wrote: > > *What I did * > >- now the RI checks utilise the @>(anyarray, anyelement) > > Best Regards, > Mark Rofail > diff --git a/src/backend/utils/adt/ri_trigger

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-11 Thread Mark Rofail
traint > would be violated: SELECT 1 FROM ONLY fktable x WHERE $1 = ANY (fkcol) FOR SHARE OF x;. or is there anything remaining ? Best Regards, Mark Rofail

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-09 Thread Mark Rofail
On Sun, Jul 9, 2017 at 2:38 AM, Alexander Korotkov wrote: > Could you, please, specify idea of what you're implementing in more > detail? > Ultimatley we would like an indexed scan instead of a sequential scan, so I thought we needed to index the FK array columns first.

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-08 Thread Mark Rofail
Strategy) within the CREATE INDEX query. For example: CREATE INDEX ON fktable USING gin (fkcolumn array_ops) Where does the strategy number fit? - The patch is attached here, is the approach I took to creating an index programmatically, correct? Best Regard, Mark Rofail diff

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-07-05 Thread Mark Rofail
y in C index a table? [1] https://www.postgresql.org/message-id/28389.1351094795%40sss.pgh.pa.us Best Regards, Mark Rofail diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 3a25ba52f3..0045f64c9e 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-06-25 Thread Mark Rofail
eck if the Datum was null and its type, I had to do it in the arraycontainselem and pass it as a parameter to the underlying function array_contains_elem. I'm proposing to introduce a new struct like ArrayType, but ElementType along all with brand new MACROs to make dealing with anyelement e

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-06-19 Thread Mark Rofail
results(src/test/regress/expected/arrays.out) - wokred on the new GIN strategy, I don't think it would vary much from GinContainsStrategy. *What I plan to do:* - I need to start working on the Referential Integrity code but I don't where to start Best Regards, Mark Rofail diff --git

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-06-17 Thread Mark Rofail
uch a check? Best Regards, Mark Rofail diff --git a/src/backend/access/gin/ginarrayproc.c b/src/backend/access/gin/ginarrayproc.c index cc7435e030..214aac8fba 100644 --- a/src/backend/access/gin/ginarrayproc.c +++ b/src/backend/access/gin/ginarrayproc.c @@ -24,6 +24,7 @@ #define GinContainsStrate

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-06-10 Thread Mark Rofail
• After finding the arraycontains function, I implemented arraycontainselem that corresponds to the operator @<(anyarray, anyelem) ◦ Please read the attached patch file to view my progress. • In addition to src/backend/utils/adt/arrayfuncs.c where I implemented arraycontainselem. ◦

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

2017-05-29 Thread Mark Rofail
ed in the source code Best Regards, Mark Rofail

Fwd: [HACKERS] GSoC 2017 Proposal

2017-04-19 Thread Mark Rofail
, Apr 6, 2017 at 4:18 PM, Mark Rofail wrote: > Hello Mr Alexander, > > From my understanding, the main issue occurs whenever any UPDATE or > DELETE statement is executed on the PK table, > this triggers a referential integrity check on the FK table. In the > previous patches