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

On Mon, Dec 4, 2017 at 6:57 PM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> On 2 December 2017 at 03:39, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Thu, Nov 30, 2017 at 11:39 PM, David Rowley
>> <david.rowley@2ndquadrant.com> wrote:
>>> I feel like we could do better here with little extra effort. The
>>> DETACH index feature does not really seem required for this patch.
>>
>> Because of the dump/restore considerations mentioned in
>> http://postgr.es/m/CA+TgmobUhGHg9v8SAswkHbBfyWg5A0QB+jGt0UOvq5YcBDUGig@mail.gmail.com
>> I believe we need a way to create the index on the parent without
>> immediately triggering index builds on the children, plus a way to
>> create an index on a child after-the-fact and attach it to the parent.
>> Detach isn't strictly required, but why support attach and not detach?
>
> I proposed that this worked a different way in [1]. I think the way I
> mention is cleaner as it means there's no extra reason for a
> partitioned index to be indisvalid=false than there is for any other
> normal index.

How does that proposal keep pg_dump from latching onto the wrong
index, if there's more than one index on the same columns?

> My primary reason for not liking this way is around leaving indexes
> around as indisvalid=false, however, I suppose that an index could be
> replaced atomically with a DETACH and ATTACH within a single
> transaction. I had previously not really liked the idea of
> invalidating an index by DETACHing a leaf table's index from it. Of
> course, this patch does nothing special with partitioned indexes, but
> I believe one day we will want to do something with these and that the
> DETACH/ATTACH is not the best design for replacing part of the index.

What do you propose?

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