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

On 15 November 2017 at 01:30, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> David Rowley wrote:
>> hmm, but surely the all those indexes must already exist if the
>> partitioned index exists. Won't we be disallowing DROP INDEX of the
>> leaf partition indexes if that index is marked as being part of the
>> partitioned index?
>
> In normal cases, sure -- but you can do ALTER INDEX DETACH on a
> partition, then drop the index.  This is useful for example if you want
> to replace some partition's index because of bloat: create a replacement
> index, detach the old one, attach the new one, drop the old one.

But if you can DETACH a partition from requiring an index to back up
this partitioned index, then the partitioned index is not valid while
the leaf table's index is missing. Maybe I misunderstood what you mean
by DETACH here, but if you remove an index and there's some period of
time where none exists for a leaf partition, then you're not going to
be able to ever do partitioned UNIQUE indexes.

I'd have thought some sort of: ALTER INDEX name_of_partitioned_index
REPLACE INDEX FOR partitioned_tablename WITH
name_of_new_matching_bloat_free_index;

... or something along those lines, and just have an atomic swap out
of the index with some new one that's been created. Something similar
would be quite good for a foreign key constraint. Today we have no
standard way to replace the referenced table's index without dropping
the FK first.

-- David Rowley                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services