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

Robert Haas wrote:
> 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.

Sounds okay to me. (I had already deleted ALTER INDEX DETACH from my
patch earlier today.) I admit I had also deleted the ONLY clause from
CREATE INDEX, and I don't like having to put it back :-) But on the
whole, having it sounds better than the alternatives.

We have two options for marking valid:

1. after each ALTER INDEX ATTACH, verify whether the set of partitions
that contain the index is complete; if so, mark it valid, otherwise do
nothing. This sucks because we have to check that over and over for
every index that we attach

2. We invent yet another command, say
ALTER INDEX <idx-on-parent> VALIDATE

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services