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

On Fri, Dec 15, 2017 at 4:02 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> 3. Robert's: use CREATE INDEX ON ONLY <parent>, which creates a shell
> index on parent only (no recursion), followed by CREATE INDEX ON
> <partition>. DETACH is not provided. If you ATTACH an index for a
> partition that already has one index attached, then (1) the newly
> attached one replaces the original (i.e. effectively REPLACE) or (2)
> you get an error and we implement a separate ALTER INDEX REPLACE
> command. It's not clear to me how or when the shell index becomes a
> real index.

With this proposal, I think the index can be invalid initially, but
once you've attached an index for every child partition, it becomes
irrevocably valid. After that, the only supported operation is
REPLACE, which preserves validity.

> Robert, can you please clarify the terms of your proposal? How is it
> better than mine? Is David's concern about a "partial" index (i.e. an
> index that doesn't exist in some partition) solved by it?

I think the perceived advantage is that, once valid, the index can't
subsequently become not-valid. That seemed to be David's big concern
(which is not without foundation).

> I have code for proposals 1 and 2. I don't like proposal 2, and David &
> Ashutosh don't like (1). Maybe if we all understand (3) we can agree on
> using that one?

Yes, it would be nice to achieve some sort of consensus and I think
(3) gives everyone a little of what they want.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company