Re: Proposal: Local indexes for partitioned table

On 10.04.2017 13:46, Greg Stark wrote:
> On 4 April 2017 at 17:10, Maksim Milyutin <m(dot)milyutin(at)postgrespro(dot)ru> wrote:
>>
>> 3. As I noticed early pg_depend table is used for cascade deleting indexes
>> on partitioned table and its children. I also use pg_depend to determine
>> relationship between parent and child indexes when reindex executes
>> recursively on child indexes.
>>
>> Perhaps, it's not good way to use pg_depend to determine the relationship
>> between parent and child indexes because the kind of this relationship is
>> not defined. I could propose to add into pg_index table specific field of
>> 'oidvector' type that specify oids of dependent indexes for the current
>> local index.
>
>
> Alternately you could have an single oid in pg_index on each of the
> children that specifies which local index is its parent. That would
> probably require a new index on that column so you could look up all
> the children efficiently.
>
> I think it would behave more sensibly when you're adding or removing a
> partition, especially if you want to add many partitions in parallel
> using multiple transactions. An oidvector of children would
> effectively mean you could only be doing one partition creation or
> deletion at a time.
>

Thanks for your comment. Your approach sounds better than mine. I'll try it.

--
Maksim Milyutin
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company