Re: [HACKERS] Surjective functional indexes

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> What I'm not willing to do is write hacks for pg_upgrade or pg_dump >> to mask cases where the option has been set on a v11 index. I judge >> that it's not worth the trouble. If someone else disagrees, they >> can do that work.

Re: [HACKERS] Surjective functional indexes

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Andres Freund writes: > > On 2019-01-14 18:53:02 -0500, Tom Lane wrote: > >> But I suspect just doing the revert is already going to be painful > >> enough :-( > > > I assume you're not particularly interested in doing that? > > No, I'm

Re: [HACKERS] Surjective functional indexes

Hi, On 2019-01-14 19:04:07 -0500, Stephen Frost wrote: > As that's the case, then I guess I'm thinking we really should make > pg_upgrade complain if it finds it during the check phase. I really > don't like having a case like this where the pg_upgrade will fail from > something that we could

Re: [HACKERS] Surjective functional indexes

Greetings, * Andres Freund (and...@anarazel.de) wrote: > On 2019-01-14 18:55:18 -0500, Stephen Frost wrote: > > * Andres Freund (and...@anarazel.de) wrote: > > > > Or are you suggesting that pg_dump in v12+ would throw errors if it > > > > finds that set? Or that we'll dump it, but fail to allow

Re: [HACKERS] Surjective functional indexes

Andres Freund writes: > On 2019-01-14 18:53:02 -0500, Tom Lane wrote: >> But I suspect just doing the revert is already going to be painful >> enough :-( > I assume you're not particularly interested in doing that? No, I'm willing to do it, and will do so tomorrow if there haven't been

Re: [HACKERS] Surjective functional indexes

Hi, On 2019-01-14 18:55:18 -0500, Stephen Frost wrote: > * Andres Freund (and...@anarazel.de) wrote: > > > Or are you suggesting that pg_dump in v12+ would throw errors if it > > > finds that set? Or that we'll dump it, but fail to allow it into a > > > v12+ database? What if v12 sees

Re: [HACKERS] Surjective functional indexes

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> After a few minutes' more thought, I think that the most attractive > >> option is to leave v11 alone and do a full revert in HEAD. In this > >> way, if anyone's attached

Re: [HACKERS] Surjective functional indexes

Hi, On 2019-01-14 18:53:02 -0500, Tom Lane wrote: > But I suspect just doing the revert is already going to be painful > enough :-( I assume you're not particularly interested in doing that? I'm more than happy to leave this to others, but if nobody signals interest I'll give it a go, because

Re: [HACKERS] Surjective functional indexes

Greetings, * Andres Freund (and...@anarazel.de) wrote: > On 2019-01-14 18:46:18 -0500, Stephen Frost wrote: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > > Andres Freund writes: > > > > On 2019-01-14 18:03:24 -0500, Tom Lane wrote: > > > >> Do we want to revert entirely, or leave the

Re: [HACKERS] Surjective functional indexes

On 2019-01-14 18:46:18 -0500, Stephen Frost wrote: > Greetings, > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > Andres Freund writes: > > > On 2019-01-14 18:03:24 -0500, Tom Lane wrote: > > >> Do we want to revert entirely, or leave the "recheck_on_update" option > > >> present but nonfunctional?

Re: [HACKERS] Surjective functional indexes

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Andres Freund writes: > > On 2019-01-14 18:03:24 -0500, Tom Lane wrote: > >> Do we want to revert entirely, or leave the "recheck_on_update" option > >> present but nonfunctional? > > > I think it depends a bit on whether we want to revert in

Re: [HACKERS] Surjective functional indexes

Andres Freund writes: > On 2019-01-14 18:03:24 -0500, Tom Lane wrote: >> Do we want to revert entirely, or leave the "recheck_on_update" option >> present but nonfunctional? > I think it depends a bit on whether we want to revert in master or > master and 11. If only master, I don't see much

Re: [HACKERS] Surjective functional indexes

Hi, On 2019-01-14 18:03:24 -0500, Tom Lane wrote: > Andres Freund writes: > > On 2018-11-07 14:25:54 -0500, Tom Lane wrote: > >> In short, it seems likely to me that large parts of this patch need to > >> be pulled out, rewritten, and then put back in different places than > >> they are today.

Re: [HACKERS] Surjective functional indexes

Andres Freund writes: > On 2018-11-07 14:25:54 -0500, Tom Lane wrote: >> In short, it seems likely to me that large parts of this patch need to >> be pulled out, rewritten, and then put back in different places than >> they are today. I'm not sure if a complete revert is the best next >> step,

Re: [HACKERS] Surjective functional indexes

On 2018-11-07 14:25:54 -0500, Tom Lane wrote: > Robert Haas writes: > > On Fri, May 11, 2018 at 11:01 AM, Simon Riggs wrote: > I have no problem if you want to replace this with an even better > design in a later release. > > >>> Meh. The author / committer should get a patch into the

Re: [HACKERS] Surjective functional indexes

On 08.11.2018 22:05, Alvaro Herrera wrote: On 2018-Nov-08, Konstantin Knizhnik wrote: Before doing any other refactoring of projection indexes I want to attach small bug fix patch which fixes the original problem (SIGSEGV) and also disables recheck_on_update by default. As Laurenz has

Re: [HACKERS] Surjective functional indexes

On 09.11.2018 2:27, Tom Lane wrote: I wrote: The bigger picture here, and the reason for my skepticism about having any intelligence in the enabling logic, is that there is no scenario in which this code can be smarter than the user about what to do. We have no insight today, and are

Re: [HACKERS] Surjective functional indexes

I wrote: > The bigger picture here, and the reason for my skepticism about having > any intelligence in the enabling logic, is that there is no scenario > in which this code can be smarter than the user about what to do. > We have no insight today, and are unlikely to have any in future, about >

Re: [HACKERS] Surjective functional indexes

Alvaro Herrera writes: > On 2018-Nov-08, Konstantin Knizhnik wrote: >> Before doing any other refactoring of projection indexes I want to attach >> small bug fix patch which >> fixes the original problem (SIGSEGV) and also disables recheck_on_update by >> default. >> As Laurenz has suggested, I

Re: [HACKERS] Surjective functional indexes

Hi, On 2018-11-07 14:25:54 -0500, Tom Lane wrote: > We need to move forward, either by undertaking a more extensive > clean-out, or by finding a path to a version of the code that is > satisfactory. > [...] > In short, it seems likely to me that large parts of this patch need to > be pulled out,

Re: [HACKERS] Surjective functional indexes

On 2018-Nov-08, Konstantin Knizhnik wrote: > Before doing any other refactoring of projection indexes I want to attach > small bug fix patch which > fixes the original problem (SIGSEGV) and also disables recheck_on_update by > default. > As Laurenz has suggested, I replaced boolean

Re: [HACKERS] Surjective functional indexes

On 08.11.2018 15:23, Laurenz Albe wrote: Tom Lane wrote: I wanted to enumerate my concerns while yesterday's events are still fresh in mind. (Andres or Robert might have more.) * I do not understand why this feature is on-by-default in the first place. It can only be a win for expression

Re: [HACKERS] Surjective functional indexes

Tom Lane wrote: > I wanted to enumerate my concerns while yesterday's > events are still fresh in mind. (Andres or Robert might have more.) > > * I do not understand why this feature is on-by-default in the first > place. It can only be a win for expression indexes that are many-to-one >

Re: [HACKERS] Surjective functional indexes

On 07.11.2018 22:25, Tom Lane wrote: Robert Haas writes: On Fri, May 11, 2018 at 11:01 AM, Simon Riggs wrote: I have no problem if you want to replace this with an even better design in a later release. Meh. The author / committer should get a patch into the right shape They have done,

Re: [HACKERS] Surjective functional indexes

Robert Haas writes: > On Fri, May 11, 2018 at 11:01 AM, Simon Riggs wrote: I have no problem if you want to replace this with an even better design in a later release. >>> Meh. The author / committer should get a patch into the right shape >> They have done, at length. Claiming

Re: [HACKERS] Surjective functional indexes

On Fri, May 11, 2018 at 11:01 AM, Simon Riggs wrote: >>> I have no problem if you want to replace this with an even better >>> design in a later release. >> >> Meh. The author / committer should get a patch into the right shape > > They have done, at length. Claiming

Re: [HACKERS] Surjective functional indexes

On 11 May 2018 at 16:37, Andres Freund wrote: > On 2018-05-11 14:56:12 +0200, Simon Riggs wrote: >> On 11 May 2018 at 05:32, Andres Freund wrote: >> > No. Simon just claimed it's not actually a concern: >> >

Re: [HACKERS] Surjective functional indexes

On 2018-05-11 14:56:12 +0200, Simon Riggs wrote: > On 11 May 2018 at 05:32, Andres Freund wrote: > > No. Simon just claimed it's not actually a concern: > > https://www.postgresql.org/message-id/canp8+j+vtskphep_gmqmeqdwakst2kbotee0yz-my+agh0a...@mail.gmail.com > > > > And

Re: [HACKERS] Surjective functional indexes

On Fri, May 11, 2018 at 4:58 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > > Sorry, may be I do not completely understand you. > So whats happed before this patch: > > - On update postgres compares old and new values of all changed attributes > to determine whether them are

Re: [HACKERS] Surjective functional indexes

On 11 May 2018 at 05:32, Andres Freund wrote: > On 2018-05-10 23:25:58 -0400, Robert Haas wrote: >> On Thu, Mar 1, 2018 at 2:48 PM, Andres Freund wrote: >> > I still don't think, as commented upon by Tom and me upthread, that we >> > want this feature in

Re: [HACKERS] Surjective functional indexes

On 11.05.2018 07:48, David G. Johnston wrote: On Thursday, February 1, 2018, Konstantin Knizhnik > wrote: Old + New for check = 2 plus calculate again in index = 3 Yes, we have to calculate the value of index

Re: [HACKERS] Surjective functional indexes

On Thursday, February 1, 2018, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > > Old + New for check = 2 >> plus calculate again in index = 3 >> > > Yes, we have to calculate the value of index expression for original and > updated version of the record. If them are equal, then it is

Re: [HACKERS] Surjective functional indexes

On 2018-05-10 23:25:58 -0400, Robert Haas wrote: > On Thu, Mar 1, 2018 at 2:48 PM, Andres Freund wrote: > > I still don't think, as commented upon by Tom and me upthread, that we > > want this feature in the current form. > > Was this concern ever addressed, or did the patch

Re: [HACKERS] Surjective functional indexes

On Thu, Mar 1, 2018 at 2:48 PM, Andres Freund wrote: > I still don't think, as commented upon by Tom and me upthread, that we > want this feature in the current form. Was this concern ever addressed, or did the patch just get committed anyway? -- Robert Haas EnterpriseDB:

Re: [HACKERS] Surjective functional indexes

On 27.03.2018 22:08, Simon Riggs wrote: On 23 March 2018 at 15:54, Simon Riggs wrote: So please could you make the change? Committed, but I still think that change would be good. Thank you. But I still not sure whether replacement of bitmap with List or array of

Re: [HACKERS] Surjective functional indexes

On 23 March 2018 at 15:54, Simon Riggs wrote: > So please could you make the change? Committed, but I still think that change would be good. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Surjective functional indexes

On 23.03.2018 18:45, Alvaro Herrera wrote: Konstantin Knizhnik wrote: rd_projidx is not a list, it is Bitmapset. It is just one of many bitmap sets in RelationData: Yes, but the other bitmapsets are of AttrNumber of the involved column. They new one is of list_nth() counters for items in

Re: [HACKERS] Surjective functional indexes

On 22.03.2018 23:53, Alvaro Herrera wrote: The whole IsProjectionFunctionalIndex looks kinda bogus/ugly to me. Set the boolean to false, but keep evaluating anyway? But then, I thought the idea was to do this based on the reloption, not by comparing the expression cost to a magical

Re: [HACKERS] Surjective functional indexes

On 23 March 2018 at 15:39, Konstantin Knizhnik wrote: > > > On 22.03.2018 23:37, Alvaro Herrera wrote: >> >> The rd_projidx (list of each nth element in the index list that is a >> projection index) thing looks odd. Wouldn't it make more sense to have >> a list of

Re: [HACKERS] Surjective functional indexes

Konstantin Knizhnik wrote: > rd_projidx is not a list, it is Bitmapset. It is just one of many bitmap > sets in RelationData: Yes, but the other bitmapsets are of AttrNumber of the involved column. They new one is of list_nth() counters for items in the index list. That seems weird and it scares

Re: [HACKERS] Surjective functional indexes

On 22.03.2018 23:37, Alvaro Herrera wrote: The rd_projidx (list of each nth element in the index list that is a projection index) thing looks odd. Wouldn't it make more sense to have a list of index OIDs that are projection indexes? rd_projidx is not a list, it is Bitmapset. It is just one

Re: [HACKERS] Surjective functional indexes

The whole IsProjectionFunctionalIndex looks kinda bogus/ugly to me. Set the boolean to false, but keep evaluating anyway? But then, I thought the idea was to do this based on the reloption, not by comparing the expression cost to a magical (unmodifiable) value? In RelationGetIndexAttrBitmap(),

Re: [HACKERS] Surjective functional indexes

The rd_projidx (list of each nth element in the index list that is a projection index) thing looks odd. Wouldn't it make more sense to have a list of index OIDs that are projection indexes? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support,

Re: [HACKERS] Surjective functional indexes

Please excuse my absence from this thread. On 2 March 2018 at 12:34, Konstantin Knizhnik wrote: > > > On 01.03.2018 22:48, Andres Freund wrote: >> >> Hi, >> >> I still don't think, as commented upon by Tom and me upthread, that we >> want this feature in the current

Re: [HACKERS] Surjective functional indexes

On 01.03.2018 22:48, Andres Freund wrote: Hi, I still don't think, as commented upon by Tom and me upthread, that we want this feature in the current form. Your arguments about layering violations seem to be counteracted by the fact that ProjectionIsNotChanged() basically appears to do

Re: [HACKERS] Surjective functional indexes

Hi, I still don't think, as commented upon by Tom and me upthread, that we want this feature in the current form. Your arguments about layering violations seem to be counteracted by the fact that ProjectionIsNotChanged() basically appears to do purely executor work inside inside heapam.c.

Re: [HACKERS] Surjective functional indexes

On 1 February 2018 at 09:32, Simon Riggs wrote: > OK, thanks. Just checking my understanding and will add to the > comments in the patch. I'm feeling good about ths now, but for personal reasons won't be committing this until late Feb/early March. -- Simon Riggs

Re: [HACKERS] Surjective functional indexes

On 1 February 2018 at 08:49, Konstantin Knizhnik wrote: > > > On 01.02.2018 03:10, Simon Riggs wrote: >> >> On 10 January 2018 at 09:54, Konstantin Knizhnik >> wrote: >> >>> (new version attached) >> >> Why this comment? >> >> Current

Re: [HACKERS] Surjective functional indexes

On 01.02.2018 03:10, Simon Riggs wrote: On 10 January 2018 at 09:54, Konstantin Knizhnik wrote: (new version attached) Why this comment? Current implementation of projection optimization has to calculate index expression twice in case of hit (value of index

Re: [HACKERS] Surjective functional indexes

On 10 January 2018 at 09:54, Konstantin Knizhnik wrote: > (new version attached) Why this comment? Current implementation of projection optimization has to calculate index expression twice in case of hit (value of index expression is not changed) and three times if

Re: [HACKERS] Surjective functional indexes

On 18.01.2018 11:38, Simon Riggs wrote: On 10 January 2018 at 09:54, Konstantin Knizhnik wrote: Sorry, issue with documentation is fixed. OK, thanks. Patch appears to work cleanly now. I'm wondering now about automatically inferring "recheck_on_update = true"

Re: [HACKERS] Surjective functional indexes

On 10 January 2018 at 09:54, Konstantin Knizhnik wrote: > Sorry, issue with documentation is fixed. OK, thanks. Patch appears to work cleanly now. I'm wondering now about automatically inferring "recheck_on_update = true" for certain common datatype/operators. It

Re: [HACKERS] Surjective functional indexes

On 07.01.2018 01:59, Stephen Frost wrote: Greetings, * Konstantin Knizhnik (k.knizh...@postgrespro.ru) wrote: On 15.12.2017 01:21, Michael Paquier wrote: On Fri, Dec 15, 2017 at 6:15 AM, Alvaro Herrera wrote: Konstantin Knizhnik wrote: If you still thing that

Re: [HACKERS] Surjective functional indexes

Greetings, * Konstantin Knizhnik (k.knizh...@postgrespro.ru) wrote: > On 15.12.2017 01:21, Michael Paquier wrote: > >On Fri, Dec 15, 2017 at 6:15 AM, Alvaro Herrera > >wrote: > >>Konstantin Knizhnik wrote: > >>>If you still thing that additional 16 bytes per relation in

Re: [HACKERS] Surjective functional indexes

On 15.12.2017 01:21, Michael Paquier wrote: On Fri, Dec 15, 2017 at 6:15 AM, Alvaro Herrera wrote: Konstantin Knizhnik wrote: If you still thing that additional 16 bytes per relation in statistic is too high overhead, then I will also remove autotune. I think it's

Re: [HACKERS] Surjective functional indexes

On Fri, Dec 15, 2017 at 6:15 AM, Alvaro Herrera wrote: > Konstantin Knizhnik wrote: >> If you still thing that additional 16 bytes per relation in statistic is too >> high overhead, then I will also remove autotune. > > I think it's pretty clear that these additional

Re: [HACKERS] Surjective functional indexes

Konstantin Knizhnik wrote: > If you still thing that additional 16 bytes per relation in statistic is too > high overhead, then I will also remove autotune. I think it's pretty clear that these additional bytes are excessive. -- Álvaro Herrerahttps://www.2ndQuadrant.com/

Re: [HACKERS] Surjective functional indexes

On Wed, Dec 13, 2017 at 12:32 PM, Konstantin Knizhnik wrote: > I can not believe that there can be more than thousand non-temporary > relations in any database. I ran across a cluster with more than 5 million non-temporary relations just this week. That's extreme, but

Re: [HACKERS] Surjective functional indexes

On 13.12.2017 14:29, Simon Riggs wrote: On 4 December 2017 at 15:35, Konstantin Knizhnik wrote: On 30.11.2017 05:02, Michael Paquier wrote: On Wed, Sep 27, 2017 at 4:07 PM, Simon Riggs wrote: On 15 September 2017 at 16:34, Konstantin

Re: [HACKERS] Surjective functional indexes

Thank you for review. On 13.12.2017 14:29, Simon Riggs wrote: On 4 December 2017 at 15:35, Konstantin Knizhnik wrote: On 30.11.2017 05:02, Michael Paquier wrote: On Wed, Sep 27, 2017 at 4:07 PM, Simon Riggs wrote: On 15 September 2017 at

Re: [HACKERS] Surjective functional indexes

On 4 December 2017 at 15:35, Konstantin Knizhnik wrote: > On 30.11.2017 05:02, Michael Paquier wrote: >> >> On Wed, Sep 27, 2017 at 4:07 PM, Simon Riggs >> wrote: >>> >>> On 15 September 2017 at 16:34, Konstantin Knizhnik >>>

Re: [HACKERS] Surjective functional indexes

On 30.11.2017 05:02, Michael Paquier wrote: On Wed, Sep 27, 2017 at 4:07 PM, Simon Riggs wrote: On 15 September 2017 at 16:34, Konstantin Knizhnik wrote: Attached please find yet another version of the patch. Thanks. I'm reviewing it. Two