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

On Tue, Dec 5, 2017 at 7:42 PM, 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.

I think keeping them valid is a great goal, just like I like low
interest rates and a chicken in every pot. However, I'm pretty
skeptical of our ability to always succeed in meeting that goal with
absolutely zero corner cases. What about a CREATE INDEX CONCURRENTLY
that fails midway through, or similarly DROP INDEX CONCURRENTLY?
Those operations can leave around artifacts in the unpartitioned table
case, and I bet they will also leave around artifacts for partitioned
tables, and maybe there will be cases where they don't leave the same
artifacts for every table in the hierarchy. Even if they do, there is
future development to consider. Maybe REINDEX INDEX CONCURRENTLY will
carry a possibility of creating a mix of states. Or maybe someone
will implement Simon's idea from a few years ago of allowing an
unlogged index on a permanent table, with the index being somehow
marked not-valid after a restart. In that situation, each one would
need to be reindexed independently to become valid.

I do agree with you that an index which is currently enforcing a
unique constraint has to remain continuously valid -- or if it
unavoidably doesn't, for example if we allowed an unlogged unique
index on a logged table, then we'd have to do something unpleasant
like disallow inserts and updates to the key column until that gets
fixed. However, that doesn't seem to preclude gracefully swapping out
indexes for individual partitions; instead of providing a DETACH
operation, we could provide a REPLACE operation that effectively does
DETACH + ATTACH.

It's possible that we are not that far apart here. I don't like the
ATTACH syntax because it's like what we do for partitions; I like it
because it solves the pg_dump problem. And it seems to me that your
reservations are more about DETACH than ATTACH. I have no issue with
punting DETACH to the curb, recasting it as REPLACE, or whatever.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company