Re: Proposal: Local indexes for partitioned table

On Fri, Oct 6, 2017 at 12:37 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> 2. create one index for each existing partition. These would be
> identical to what would happen if you created the index directly on
> each partition, except that there is an additional dependency to the
> parent's abstract index.

One thing I'm a bit worried about is how to name these subordinate
indexes. They have to have names because that's how pg_class works,
and those names can't all be the same, again because that's how
pg_class works. There's no problem right away when you first create
the partitioned index, because you can just pick names out of a hat
using whatever name-generating algorithm seems best. However, when
you dump-and-restore (including but not limited to the pg_upgrade
case) you've got to preserve those names. If you just generate a new
name that may or may not be the same as the old one, then it may
collide with a user-specified name that only occurs later in the dump.
Also, you'll have trouble if the user has applied a COMMENT or a
SECURITY LABEL to the index because that command works by name, or if
the user has a reference to the index name inside a function or
whatever.

These are pretty annoying corner-case bugs because they're not likely
to come up very often. Most people won't notice or care if the index
name changes. But I don't think it's acceptable to just ignore the
problem. An idea I had was to treat the abstract index - to use your
term - sort of the way we treat an extension. Normally, when you
create an index on a partitioned table, it cascades, but for dump and
restore purpose, we tag on some syntax that says "well, don't actually
create the subordinate indexes, i'll tell you about those later".
Then for each subordinate index we issue a separate CREATE INDEX
command followed by ALTER INDEX abstract_index ATTACH PARTITION
concrete_index or something of that sort. That means you can't
absolutely count on the parent index to have all of the children it's
supposed to have but maybe that's OK.

Another thing that would let you do is CREATE INDEX CONCURRENTLY
replacement_concrete_index; ALTER INDEX abstract_index DETACH
PARTITION old_concrete_index, ATTACH PARTITION
replacement_concrete_index; DROP INDEX CONCURRENTLY
old_concrete_index, which seems like a thing someone might want to do.

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