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

On Fri, Jan 5, 2018 at 4:57 PM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> On 1/4/18 23:08, David Rowley wrote:
>> On 5 January 2018 at 11:01, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>>> (The more I think of this, the more I believe that pg_inherits is a
>>> better answer. Opinions?)
>>
>> I admit to not having had a chance to look at any code with this yet,
>> but I'm just thinking about a case like the following.
>>
>> CREATE TABLE part (a INT, b INT) PARTITION BY RANGE (a);
>> CREATE TABLE part_a1 PARTITION OF part FOR VALUES FROM (0) TO (10)
>> PARTITION BY RANGE (b);
>> CREATE TABLE part_a1_b1 PARTITION OF part_a1 FOR VALUES FROM (0) TO (10);
>>
>> CREATE INDEX ON part_a1 (a); -- sub-partition index (creates index on
>> part_a1_b1)
>>
>> CREATE INDEX ON part (a); -- What do we do here?
>>
>> Should we:
>>
>> 1. Create another identical index on part_a1_b1; or
>> 2. Allow the existing index on part_a1_b1 to have multiple parents; or
>> 3. ERROR... (probably not)
>
> 4. It should adopt part_a1 and its subindexes into its hierarchy. That
> shouldn't be a problem under the current theory, should it?

+1.

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