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

Alvaro Herrera 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.

As I understand the whole purpose of this design is that there is no
point during the restore at which the index lacks indexes on partitions:
it is either complete, or it doesn't exist yet. If we create the index
on parent first, and later the indexes on partitions, that condition is
not satisfied. To solve this, we could create the children indexes
first and then the parent; but how do we indicate to the parent creation
which are the indexes that oughta be marked as children? ALTER INDEX
ATTACH PARTITION doesn't cut it, because it occurs after the index on
parent is created, which is too late. We would do something like

CREATE INDEX ON parent (columns) [other stuff]
ATTACH idx_on_child1, idx_on_child2, idx_on_child3;

but this seems mighty ugly.

Anyway if we do that, what is the point of ALTER INDEX ATTACH PARTITION?
We might as leave it out and just keep the door open for a later ALTER
INDEX REPLACE.

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