On 6 December 2017 at 13:42, David Rowley <david.row...@2ndquadrant.com> wrote: > On 6 December 2017 at 11:35, Robert Haas <robertmh...@gmail.com> wrote: >> What are we giving up by explicitly attaching >> the correct index? > > The part I don't like about the ATTACH and DETACH of partitioned index > is that it seems to be trying to just follow the syntax we use to > remove a partition from a partitioned table, however, there's a huge > difference between the two, as DETACHing a partition from a > partitioned table leaves the partitioned table in a valid state, it > simply just no longer contains the detached partition. With the > partitioned index, we leave the index in an invalid state after a > DETACH. It can only be made valid again once another leaf index has > been ATTACHED again and that we've verified that all other indexes on > every leaf partition is also there and are valid. If we're going to > use these indexes to answer queries, then it seems like we should try > to keep them valid so that queries can actually use them for > something.
Also, ATTACH and DETACH are especially useless when it comes to UNIQUE indexes. If we simply want to replace out a bloated index using a DETACH quickly followed by an ATTACH then it leaves a non-zero window of time that we can't be certain that the uniqueness is enforced. This would still work on an individual partition level, but if we ever want to reference a UNIQUE partitioned index in a foreign key constraint then what happens to the foreign key when the index is in the invalid state? Should we just disallow DETACH when a foreign key exists? or just invalidate the foreign key constraint too? Both seem like a nightmare from a DBA point-of-view. You might argue that concurrently recreating an index used by a foreign key is just as difficult today, but there's no reason to make this as problematic, is there? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
