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

On 6 December 2017 at 13:42, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 6 December 2017 at 11:35, Robert Haas <robertmhaas(at)gmail(dot)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