David Rowley wrote: > 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.
Yeah. I don't see this as too much of a problem myself. At present, from the planner's POV each partition is its own table and incurs its own planning, so there's no restriction on having different sets of indexes. If in the future we want to see partition hierarchies as a single relation which must have identical properties such as the same index everywhere, this is easily changed: just set indisready flag false for the parent table until every partition can be proven to have the index. Example: on DETACH we set indisready=false on the parent; on ATTACH we scan the whole hierarchy and set it true if all partitions have the index. So the planner can rely on the parent's indisready. Since those operations are catalog-only, the transaction which attaches/detaches the index can be short (though AFAICS it requires AEL on the whole hierarchy, sadly). > 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. Well, UNIQUE indexes have additional restrictions of their own. I have no problem decreeing that you cannot DETACH an index from a primary key. Then a future project can implement index replacement like you propose: > 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. (My intention here is to avoid scope creep.) -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
