Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On 11/10/2017 01:47 AM, Mark Rofail wrote: I am sorry for the late reply There is no reason for you to be. It did not take you 6 weeks to do a review. :) Thanks for this new version. == Functional review >1) MATCH FULL does not seem to care about NULLS in arrays. In the example be

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

On 9 October 2017 at 18:57, Alexander Korotkov wrote: > On Thu, Oct 5, 2017 at 9:48 PM, Shubham Barai > wrote: > >> On 3 October 2017 at 00:32, Alexander Korotkov > > wrote: >> >>> On Mon, Oct 2, 2017 at 9:11 PM, Andrew Borodin >>> wrote: >>> On Mon, Oct 2, 2017 at 8:00 PM, Alexander Korot

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Sorry for the very late review. I like this feature and have needed it myself in the past, and the current syntax seems pretty good. One could argue for if the syntax could be generalized to support other things like json and hstore, but I do not think it would be fair to block this patch due

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

On Thu, Oct 5, 2017 at 9:48 PM, Shubham Barai wrote: > On 3 October 2017 at 00:32, Alexander Korotkov > wrote: > >> On Mon, Oct 2, 2017 at 9:11 PM, Andrew Borodin >> wrote: >> >>> On Mon, Oct 2, 2017 at 8:00 PM, Alexander Korotkov >>> wrote: >>> > What happen if exactly this "continue" fires?

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

Sent with Mailtrack <#> On 3 October 2017 at 00:32, Alexander Korotkov wrote: > On Mon, Oct 2, 2017

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

On Mon, Oct 2, 2017 at 9:11 PM, Andrew Borodin wrote: > On Mon, Oct 2, 2017 at 8:00 PM, Alexander Korotkov > wrote: > > What happen if exactly this "continue" fires? > > > >> if (GistFollowRight(stack->page)) > >> { > >> if (!xlocked) > >> { > >> LockBuffer(stack->buffer, GIST_UNLOCK); > >> Lock

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

On Mon, Oct 2, 2017 at 8:00 PM, Alexander Korotkov wrote: > What happen if exactly this "continue" fires? > >> if (GistFollowRight(stack->page)) >> { >> if (!xlocked) >> { >> LockBuffer(stack->buffer, GIST_UNLOCK); >> LockBuffer(stack->buffer, GIST_EXCLUSIVE); >> xlocked = true; >> /* someone migh

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

On Sun, Oct 1, 2017 at 11:53 AM, Shubham Barai wrote: > Yes, page-level predicate locking should happen only when fast update is > off. > Actually, I forgot to put conditions in updated patch. Does everything > else look ok ? > I think that isolation tests should be improved. It doesn't seems t

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

Hi, Andrew! On Mon, Oct 2, 2017 at 1:40 PM, Andrew Borodin wrote: > Thanks for looking into the patch! > > On Thu, Sep 28, 2017 at 3:59 PM, Alexander Korotkov < > a.korot...@postgrespro.ru> wrote: > >> >> >> In gistdoinsert() you do CheckForSerializableConflictIn() only if page >> wasn't exclusi

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

Hi, Alexander! Thanks for looking into the patch! On Thu, Sep 28, 2017 at 3:59 PM, Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > > > In gistdoinsert() you do CheckForSerializableConflictIn() only if page > wasn't exclusively locked before (xlocked is false). > > if (!xlocked) >> { >>

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

On Sep 28, 2017 4:30 PM, "Alexander Korotkov" wrote: Hi! On Wed, Jun 21, 2017 at 10:52 AM, Shubham Barai wrote: > Hi, > > On 21 June 2017 at 13:11, Heikki Linnakangas wrote: > >> On 06/16/2017 01:24 PM, Shubham Barai wrote: >> >>> @@ -497,6 +499,13 @@ gistplacetopage(Relation rel, Size freesp

Re: [HACKERS] GSoC 2017: weekly progress reports (week 8)

> On 29 Sep 2017, at 00:59, Alexander Korotkov > wrote: > > On Thu, Sep 28, 2017 at 2:22 PM, Alexander Korotkov > mailto:a.korot...@postgrespro.ru>> wrote: > On Fri, Jul 28, 2017 at 7:58 AM, Shubham Barai > wrote: > I am attaching a patch for predicate locking

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

On 1 October 2017 at 01:47, Alexander Korotkov wrote: > On Sat, Sep 30, 2017 at 6:12 PM, Shubham Barai > wrote: > >> I have made changes according to your suggestions. Please have a look at >> the updated patch. >> I am also considering your suggestions for my other patches also. But, I >> will

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

On Sat, Sep 30, 2017 at 6:12 PM, Shubham Barai wrote: > I have made changes according to your suggestions. Please have a look at > the updated patch. > I am also considering your suggestions for my other patches also. But, I > will need some time to > make changes as I am currently busy doing my

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

Sent with Mailtrack <#> On 28 September 2017 at 15:49, Alexander Korotkov wrote: > On Thu, Sep 28, 2017 at 12:45 AM, Alexander Korotkov < > a.korot...@postgrespro.ru>

Re: [HACKERS] GSoC 2017: weekly progress reports (week 4) and patch for hash index

On Fri, Sep 8, 2017 at 4:07 AM, Thomas Munro wrote: > Hi Shubham, > > On Tue, Jun 27, 2017 at 9:21 PM, Shubham Barai > wrote: > > If these two hash keys (78988658 and 546789888) mapped to the same > bucket, this will result in false serialization failure. > > Please correct me if this assumption

Re: [HACKERS] GSoC 2017: weekly progress reports (week 8)

On Thu, Sep 28, 2017 at 2:22 PM, Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > On Fri, Jul 28, 2017 at 7:58 AM, Shubham Barai > wrote: > >> I am attaching a patch for predicate locking in SP-Gist index >> > > I took a look over this patch. > > newLeafBuffer = SpGistGetBuffer(index, >>

Re: [HACKERS] GSoC 2017: weekly progress reports (week 8)

Hi! On Fri, Jul 28, 2017 at 7:58 AM, Shubham Barai wrote: > I am attaching a patch for predicate locking in SP-Gist index > I took a look over this patch. newLeafBuffer = SpGistGetBuffer(index, > GBUF_LEAF | (isNulls ? GBUF_NULLS : 0), > Min(totalLeafSizes, > SPGIST_PAGE_CAPACITY), > &xlrec.in

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

Hi! On Wed, Jun 21, 2017 at 10:52 AM, Shubham Barai wrote: > Hi, > > On 21 June 2017 at 13:11, Heikki Linnakangas wrote: > >> On 06/16/2017 01:24 PM, Shubham Barai wrote: >> >>> @@ -497,6 +499,13 @@ gistplacetopage(Relation rel, Size freespace, >>> GISTSTATE *giststate, >>>

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

On Thu, Sep 28, 2017 at 12:45 AM, Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > On Wed, Aug 9, 2017 at 6:30 PM, Shubham Barai > wrote: > >> Please find the updated patch for predicate locking in gin index here. >> >> There was a small issue in the previous patch. I didn't consider the

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

Hi! On Wed, Aug 9, 2017 at 6:30 PM, Shubham Barai wrote: > Please find the updated patch for predicate locking in gin index here. > > There was a small issue in the previous patch. I didn't consider the case > where only root page exists in the tree, and there is a predicate lock on > it, > and

Re: [HACKERS] GSoC 2017: weekly progress reports (week 4) and patch for hash index

Hi Thomas, I have attached the rebased version of patch here. Kind Regards, Shubham On 8 September 2017 at 06:37, Thomas Munro wrote: > Hi Shubham, > > On Tue, Jun 27, 2017 at 9:21 PM, Shubham Barai > wrote: > > If these two hash keys (78988658 and 546789888) mapped to the same > bucket, thi

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

I have not looked at the issue with the btree_gin tests yet, but here is the first part of my review. = Review This is my first quick review where I just read the documentation and quickly tested the feature. I will review it more in-depth later. This is a very useful feature, one which I ha

Re: [HACKERS] GSoC 2017: weekly progress reports (week 4) and patch for hash index

Hi Shubham, On Tue, Jun 27, 2017 at 9:21 PM, Shubham Barai wrote: > If these two hash keys (78988658 and 546789888) mapped to the same bucket, > this will result in false serialization failure. > Please correct me if this assumption about false positives is wrong. I wonder if there is an opport

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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 : Patch for predicate locking in Gist index

Hi, hackers! > 21 июня 2017 г., в 12:52, Shubham Barai написал(а): > > Hi, > ... > I know that. This is the old version of the patch. I had sent updated patch > later. Please have a look at updated patch. > > Regards, > Shubham Here is some information for reviewers. This applies to patches

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Alexander Korotkov writes: > On Mon, Aug 14, 2017 at 2:09 PM, Mark Rofail wrote: >> 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 shoul

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Mon, Aug 14, 2017 at 2:09 PM, Mark Rofail wrote: > On Tue, Aug 8, 2017 at 3:24 PM, Alexander Korotkov > wrote: > >> On Tue, Aug 8, 2017 at 4:12 PM, Mark Rofail >> wrote: >> >>> On Tue, Aug 8, 2017 at 2:25 PM, Alexander Korotkov >> > wrote: >>> >> GROUP BY would also use default btree/hash op

[HACKERS] GSoC 2017: weekly progress reports (week 9 and week 10)

Project: Explicitly support predicate locks in index AMs besides b-tree Hi, In the last two weeks, I mostly worked on predicate locking in rum index. Rum is based on gin access method. The main difference between rum and gin is that rum stores additional information in posting tree to perform a f

Re: [HACKERS] GSoC 2017: weekly progress reports (week 6)

Hi, Please find the updated patch for predicate locking in gin index here. There was a small issue in the previous patch. I didn't consider the case where only root page exists in the tree, and there is a predicate lock on it, and it gets split. If we treat the original page as a left page and c

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Tue, Aug 8, 2017 at 4:12 PM, Mark Rofail wrote: > On Tue, Aug 8, 2017 at 2:25 PM, Alexander Korotkov > wrote: >> >> Do we already assume that default btree opclass for array element type >> matches PK opclass when using @>> operator on UPDATE/DELETE of referenced >> table? >> > I believe so,

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Tue, Aug 8, 2017 at 2:25 PM, Alexander Korotkov wrote: > > Do we already assume that default btree opclass for array element type > matches PK opclass when using @>> operator on UPDATE/DELETE of referenced > table? > I believe so, since it's a polymorphic function. > If so, we don't introduce

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Sat, Aug 5, 2017 at 11:36 PM, Mark Rofail wrote: > 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

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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

To better understand a limitation I ask 5 questions What is the limitation? Why is there a limitation? Why is it a limitation? What can we do? Is it feasible? Through some reading: *What is the limitation?* presupposes that count(distinct y) has exactly the same notion of equality that the PK un

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Mon, Jul 31, 2017 at 5:18 PM, Alvaro Herrera wrote: > Tom Lane wrote: > > Alvaro Herrera writes: > > > ... However, when you create an index, you can > > > indicate which operator class to use, and it may not be the default > one. > > > If a different one is chosen at index creation time, th

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Tom Lane wrote: > Alvaro Herrera writes: > > ... However, when you create an index, you can > > indicate which operator class to use, and it may not be the default one. > > If a different one is chosen at index creation time, then a query using > > COUNT(distinct) will do the wrong thing, because

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Alvaro Herrera writes: > ... However, when you create an index, you can > indicate which operator class to use, and it may not be the default one. > If a different one is chosen at index creation time, then a query using > COUNT(distinct) will do the wrong thing, because DISTINCT will select > an

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Mark Rofail wrote: > 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. Ope

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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

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

On 2017-07-27 21:08, Mark Rofail wrote: On Thu, Jul 27, 2017 at 7:15 PM, Erik Rijkers wrote: It would help (me at least) if you could be more explicit about what exactly each instance is. I apologize, I thought it was clear through the context. Thanks a lot. It's just really easy for tes

Re: [HACKERS] GSoC 2017: weekly progress reports (week 8)

Hi. I am attaching a patch for predicate locking in SP-Gist index Regards, Shubham Sent with Mailtrack On 26 July 2017 at 20:50, Shubham Barai wrote: > Project:

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Thu, Jul 27, 2017 at 7:30 PM, Alexander Korotkov wrote: > Oh, ok. I missed that. >> > Could you remind me why don't we have DELETE CASCADE? I understand that > UPDATE CASCADE is problematic because it's unclear which way should we > delete elements from array. But what about DELETE CASCADE?

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Thu, Jul 27, 2017 at 7:15 PM, Erik Rijkers wrote: > It would help (me at least) if you could be more explicit about what > exactly each instance is. > I apologize, I thought it was clear through the context. I meant by the original patch is all the work done before my GSoC project. The lates

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Thu, Jul 27, 2017 at 3:07 PM, Mark Rofail wrote: > 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

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On 2017-07-27 02:31, Mark Rofail wrote: I have written some benchmark test. It would help (me at least) if you could be more explicit about what exactly each instance is. Apparently there is an 'original patch': is this the original patch by Marco Nenciarini? Or is it something you posted

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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

On Thu, Jul 27, 2017 at 3:31 AM, Mark Rofail wrote: > I have written some benchmark test. > > With two tables a PK table with 5 rows and an FK table with growing row > count. > > Once triggering an RI check > at 10 rows, > 100 rows, > 1,000 rows, > 10,000 rows, > 100,000 rows and > 1,000,000 rows

[HACKERS] GSoC 2017: weekly progress reports (week 8)

Project: Explicitly support predicate locks in index AMs besides b-tree Hi, During this week, I worked on predicate locking in spgist index. I think, for spgist index, predicate lock only on leaf pages will be enough as spgist searches can determine if there is a match or not only at leaf level.

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On 2017-07-24 23:31, Mark Rofail wrote: On Mon, Jul 24, 2017 at 11:25 PM, Erik Rijkers wrote: This patch doesn't apply to HEAD at the moment ( e2c8100e6072936 ). My bad, I should have mentioned that the patch is dependant on the original patch. Here is a *unified* patch that I just tested

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On 2017-07-24 23:08, Mark Rofail wrote: Here is the new Patch with the bug fixes and the New Patch with the Index in place performance results. I just want to point this out because I still can't believe the numbers. In reference to the old patch: The new patch without the index suffers a 41.

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

It certainly is, thank you for the heads up. I included a note to encourage the user to index the referencing column instead. On Sun, Jul 23, 2017 at 4:41 AM, Robert Haas wrote: > > This is a jumbo king-sized can of worms, and even a very experienced > contributor would likely find it extremely d

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

> > 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

On Sat, Jul 22, 2017 at 5:50 PM, Mark Rofail wrote: > so personally I don't think we should leave creating a GIN index up to the > user, it should be automatically generated instead. I can certainly understand why you feel that way, but trying to do that in your patch is just going to get your pa

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Wed, Jul 19, 2017 at 11:08 PM, Alvaro Herrera wrote: > I'm not entirely sure what's the best way to deal with the polymorphic > problem, but on the other hand as Robert says downthread maybe we > shouldn't be solving it at this stage anyway. So let's step back a bit, > get a patch that works

Re: [HACKERS] GSoC 2017: weekly progress reports (week 7)

On Thu, Jul 20, 2017 at 1:22 PM, Shubham Barai wrote: > I had detailed discussion about this with my mentor. Sorry, I didn't share > details on hackers list. > > B-tree, gist, spgist, and gin are all tree based indexes where we scan and > acquire predicate lock > on only some and not all internal

Re: [HACKERS] GSoC 2017: weekly progress reports (week 7)

Hi Robert, I had detailed discussion about this with my mentor. Sorry, I didn't share details on hackers list. B-tree, gist, spgist, and gin are all tree based indexes where we scan and acquire predicate lock on only some and not all internal pages or leaf pages. So, here we have scope to reduce

Re: [HACKERS] GSoC 2017: weekly progress reports (week 7)

On Tue, Jul 18, 2017 at 10:36 AM, Shubham Barai wrote: > During this week, I read documentation and source code of BRIN index to > understand its implementation. > But later I found out that it is unlikely to implement page level or tuple > level predicate locking in BRIN index. > In this week, I

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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

Mark Rofail wrote: > On Tue, Jul 18, 2017 at 11:14 PM, Alvaro Herrera > wrote: > > > > Why did we add an operator and not a support > > procedure? > > I thought the support procedures were constant within an opclass. Uhh ... I apologize but I think I was barking at the wrong tree. I was thinkin

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Wed, Jul 19, 2017 at 2:29 PM, Mark Rofail wrote: > 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 no

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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

On Wed, Jul 19, 2017 at 8:08 AM, Mark Rofail wrote: > To summarise, the options we have to solve the limitation of the @>(anyarray > , anyelement) where it produces the following error: operator does not > exist: integer[] @> smallint Why do we have to solve that limitation? -- Robert Haas Ente

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

*To summarise,* the options we have to solve the limitation of the @>(anyarray , anyelement) where it produces the following error: operator does not exist: integer[] @> smallint *Option 1: *Multiple Operators Have separate operators for every combination of datatypes instead of a single polymorph

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Tue, Jul 18, 2017 at 11:14 PM, Alvaro Herrera wrote: > > Why did we add an operator and not a support > procedure? I thought the support procedures were constant within an opclass. They implement the mandotary function required of an opclass. I don't see why we would need to implement new one

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Alexander Korotkov wrote: > The problem is that you need to have not only opclass entries for the > operators, but also operators themselves. I.e. separate operators for > int4[] @>> int8, int4[] @>> int4, int4[] @>> int2, int4[] @>> numeric. You > tried to add multiple pg_amop rows for single o

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Mark Rofail wrote: > 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 co

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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

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 each type combination -- int4 to int2, int4 to >>> int4

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Tue, Jul 18, 2017 at 2:24 AM, 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 >> the opclasses. > > > I tried

[HACKERS] GSoC 2017: weekly progress reports (week 7)

Project: Explicitly support predicate locks in index AMs besides b-tree Hi, During this week, I read documentation and source code of BRIN index to understand its implementation. But later I found out that it is unlikely to implement page level or tuple level predicate locking in BRIN index. In t

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

There is a generic definition for any array added as part of https://commitfest.postgresql.org/10/708/ (it may be the reason for the duplicate error). I am not sure what your change is but I would review the above just in case. There is also a defect with a misleading error that is still being trig

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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: weekly progress reports (week 6)

Hi, I am attaching a patch for predicate locking in gin index. Regards, Shubham Sent with Mailtrack On 11 July 2017 at 19:10, Shubham Barai wrote: > Project: Exp

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Mark Rofail wrote: > 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

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 >> the opclasses. >> > > Can you

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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. > Can you clarify this solution ? I think another solution would be external

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Mark Rofail wrote: >- now the RI checks utilise the @>(anyarray, anyelement) > - however there's a small problem: > operator does not exist: integer[] @> smallint > I assume that external casting would be required here. But how can I > downcast smallint to integer or in

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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_triggers.c b/src/backend/utils/adt/ri_triggers

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Sun, Jul 9, 2017 at 7:42 PM, Alexander Korotkov wrote: > We may document that GIN index is required to accelerate RI queries for > array FKs. And users are encouraged to manually define them. > It's also possible to define new option when index on referencing > column(s) would be created auto

[HACKERS] GSoC 2017: weekly progress reports (week 6)

Project: Explicitly support predicate locks in index AMs besides b-tree I have done following tasks during this week. 1) worked on how to detect rw conflicts when fast update is enabled 2) created tests for different gin operators 3) went through some patches on commitfest to review 4) solved

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Sun, Jul 9, 2017 at 1:11 PM, Mark Rofail wrote: > 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 nee

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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

On Sun, Jul 9, 2017 at 2:35 AM, Mark Rofail wrote: > * What I am working on* > >- since we want to create an index on the referencing column, I am >working on firing a 'CREATE INDEX' query programatically right after >the 'CREATE TABLE' query >- The problem I ran into is how to sp

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

* What I am working on* - since we want to create an index on the referencing column, I am working on firing a 'CREATE INDEX' query programatically right after the 'CREATE TABLE' query - The problem I ran into is how to specify my Strategy ( GinContainsElemStrategy) within the CR

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

To make the queries fired by the RI triggers GIN indexed. We need to ‒ as Tom Lane has previously suggested[1] ‒ to replace the query SELECT 1 FROM ONLY fktable x WHERE $1 = ANY (fkcol) FOR SHARE OF x; with SELECT 1 FROM ONLY fktable x WHERE ARRAY[$1] <@ fkcol FOR SHARE OF x; but since we have

[HACKERS] GSoC 2017: weekly progress reports (week 5) and Proposal for predicate locking in gin index

Project: Explicitly support predicate locks in index AMs besides b-tree Hi, During this week, I read documentation and source code of gin index to find appropriate places to insert calls to existing functions. Proposal Gin index consists of a Btree index over key values, where each key is an e

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Mark Rofail wrote: > On Mon, Jun 26, 2017 at 6:44 PM, Alexander Korotkov > wrote: > > > Have you met any particular problem here? Or is it just a lot of > > mechanical work? > > > > Just A LOT of mechanictal work, thankfully. The patch is now rebased and > all regress tests have passed (even th

[HACKERS] GSoC 2017: weekly progress reports (week 4) and patch for hash index

Project: Explicitly support predicate locks in index AMs besides b-tree Hi, During this week, I continued my work on predicate locking in the hash index and created a patch for it. As I have written in my proposal for the hash index, every scan operation acquires a predicate lock on the primary p

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Mon, Jun 26, 2017 at 2:26 AM, Mark Rofail wrote: > *What I did:* > > > >- read into the old patch but couldn't apply it since it's quite old. >It needs to be rebased and that's what I am working on. It's a lot of >work. > - incomplete patch can be found attached here > > Hav

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

*What I did:* - read into the old patch but couldn't apply it since it's quite old. It needs to be rebased and that's what I am working on. It's a lot of work. - incomplete patch can be found attached here *Bugs* - problem with the @>(anyarray, anyelement) opertator: if for exa

Re: [HACKERS] GSoC 2017 Proposal for predicate locking in hash index

Hi, On 22 June 2017 at 21:12, Alvaro Herrera wrote: > Shubham Barai wrote: > > Hi, > > > > Now that hash index support write-ahead logging, it will be great if we > add > > support for predicate locking to it. > > Implementation of predicate locking in hash index seems very simple. > > I have al

Re: [HACKERS] GSoC 2017 Proposal for predicate locking in hash index

Shubham Barai wrote: > Hi, > > Now that hash index support write-ahead logging, it will be great if we add > support for predicate locking to it. > Implementation of predicate locking in hash index seems very simple. > I have already made changes in the code. I am currently working on testing. So

[HACKERS] GSoC 2017 Proposal for predicate locking in hash index

Hi, Now that hash index support write-ahead logging, it will be great if we add support for predicate locking to it. Implementation of predicate locking in hash index seems very simple. I have already made changes in the code. I am currently working on testing. Here is my approach 1) PredicateLo

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

Hi, On 21 June 2017 at 13:11, Heikki Linnakangas wrote: > On 06/16/2017 01:24 PM, Shubham Barai wrote: > >> @@ -497,6 +499,13 @@ gistplacetopage(Relation rel, Size freespace, >> GISTSTATE *giststate, >> for (ptr = dist->next; ptr; ptr = ptr->next) >>

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

On 06/21/2017 10:41 AM, Heikki Linnakangas wrote: On 06/16/2017 01:24 PM, Shubham Barai wrote: @@ -497,6 +499,13 @@ gistplacetopage(Relation rel, Size freespace, GISTSTATE *giststate, for (ptr = dist->next; ptr; ptr = ptr->next) UnlockRele

Re: [HACKERS] GSoC 2017 : Patch for predicate locking in Gist index

On 06/16/2017 01:24 PM, Shubham Barai wrote: @@ -497,6 +499,13 @@ gistplacetopage(Relation rel, Size freespace, GISTSTATE *giststate, for (ptr = dist->next; ptr; ptr = ptr->next) UnlockReleaseBuffer(ptr->buffer); } + +

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Mark Rofail wrote: > Okay, so major breakthrough. > > *Updates:* > >- The operator @>(anyarray, anyelement) is now functional > - The segmentation fault was due to applying PG_FREE_IF_COPY on a > datum when it should only be applied on TOASTed inputs > - The only problem now

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Okay, so major breakthrough. *Updates:* - The operator @>(anyarray, anyelement) is now functional - The segmentation fault was due to applying PG_FREE_IF_COPY on a datum when it should only be applied on TOASTed inputs - The only problem now is if for example you apply the op

  1   2   >