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

Hmm, so I'm now unsure what the actual proposals for handling pg_dump
are. We seem to have the following three proposals:

1. Alvaro: use CREATE INDEX ON ONLY <parent> (not recursive ), followed
by CREATE INDEX ON <partition>, followed by ALTER INDEX <on_parent>
ATTACH PARTITION <on_partition>. I provide an ALTER INDEX DETACH
PARTITION for symmetry and because it can be used to replace the
index.

Pros: the database is always restored identically to what was in the
original.
Con: The index hierarchy might be "partial", that is, lack a
component index on some partition.

2. David's: use CREATE INDEX ON <partition>, followed by CREATE INDEX ON
<parent>. This will use the matching mechanism to automatically
attach the index on partition to index on parent. If some partition
lacks a matching index, one is created automatically by the creation
on parent.

If you want to replace the index on a partition, use a new (as yet
unimplemented) ALTER INDEX REPLACE.

No need to add ONLY to the table name in CREATE INDEX, since the
command always recurses. (This seems good to me, because I

Pro: the index is never "partial" (missing a partition).
Con: the matching mechanism might choose a different index on restore
than what was selected in the database being dumped.

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.

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 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?

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