On 15 November 2017 at 01:30, Alvaro Herrera <alvhe...@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
