Re: [HACKERS] Proposal: Local indexes for partitioned table

On 18 December 2017 at 15:59, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sun, Dec 17, 2017 at 9:38 PM, David Rowley
> <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>> On 18 December 2017 at 15:04, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> On Sun, Dec 17, 2017 at 5:29 AM, David Rowley
>>> <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>>>> I'm now not that clear on what the behaviour is if the ONLY keyword is
>>>> not specified on the CREATE INDEX for the partitioned index. Does that
>>>> go and create each leaf partition index regardless of if there is a
>>>> suitable candidate to ATTACH?
>>>
>>> No, the other way around. ONLY is being proposed as a way to create
>>> an initially-not-valid parent to which we can then ATTACH
>>> subsequently-created child indexes. But because we will have REPLACE
>>> rather than DETACH, once you get the index valid it never goes back to
>>> not-valid.
>>
>> I understand what the ONLY is proposed to do. My question was in
>> regards to the behaviour without ONLY.
>
> Oh, sorry -- I was confused. I'm not sure whether that should try to
> attach to something if it exists, or just create unconditionally...
> what do you think?

I think you feel quite strongly about not having the code select a
random matching index, so if we want to stick to that rule, then we'll
need to create a set of new leaf indexes rather than select a random
one.

If we don't do that, then we might as well go with my idea and ditch
the ONLY syntax and have the CREATE INDEX try to find a suitable leaf
index, or create a new leaf index if one cannot be found.

Would it be surprising to users if CREATE INDEX ON partitioned_table
created a bunch of duplicate new indexes on the leaf tables?

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services