Re: Proposal: Local indexes for partitioned table

On Sat, Oct 7, 2017 at 7:04 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.

+1.

How about CREATE INDEX ... PARTITION OF ... FOR TABLE ...? to create
the index and attach it?

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company