Robert Haas wrote: > On Fri, Jan 5, 2018 at 4:57 PM, Peter Eisentraut > <peter.eisentr...@2ndquadrant.com> wrote: > > On 1/4/18 23:08, David Rowley wrote:
> >> 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. This is what the code does today. IIRC there's a test for this exact scenario. Now, part_a1_b1 is grandchild of part, not direct parent -- so there exists an intermediate relkind=I index in part_a1, and that is the one that becomes parent of part_a1_b1, not part's directly. Now, if you drop the index in part, then it gets dropped in all descendants too, including part_a1_b1. If you DETACH the partition first, then the index remains. All of that seems good to me, and is as discussed previously. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
