BUG #18171: Dropping an index on a partitioned table drops all child indices even with a restrict - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18171
Logged by:          Nick Dujay
Email address:      nickdujay@gmail.com
PostgreSQL version: 12.8
Operating system:   macOS
Description:

Here's the setup.

postgres=# create table parent (id bigserial, created_at timestamp not null
default now()) partition by range (created_at);
postgres=# create table child1 partition of parent for values from
('2019-01-01 00:00:00') TO ('2019-01-02 00:00:00');
postgres=# create index child_id_index on child1 (id);
postgres=# create index parent_id_index on parent (id);
postgres=# alter index parent_id_index attach partition child_id_index;
postgres=# \d parent
                                  Partitioned table "public.parent"
   Column   |            Type             | Collation | Nullable |
   Default
------------+-----------------------------+-----------+----------+------------------------------------
 id         | bigint                      |           | not null |
nextval('parent_id_seq'::regclass)
 created_at | timestamp without time zone |           | not null | now()
Partition key: RANGE (created_at)
Indexes:
    "parent_id_index" btree (id)
Number of partitions: 1 (Use \d+ to list them.)
postgres=# \d child1
                                        Table "public.child1"
   Column   |            Type             | Collation | Nullable |
   Default
------------+-----------------------------+-----------+----------+------------------------------------
 id         | bigint                      |           | not null |
nextval('parent_id_seq'::regclass)
 created_at | timestamp without time zone |           | not null | now()
Partition of: parent FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-01-02
00:00:00')
Indexes:
    "child_id_index" btree (id)

When I attempt to drop the child index, I get an error.

postgres=# drop index concurrently child_id_index restrict;
ERROR:  cannot drop index child_id_index because index parent_id_index
requires it
HINT:  You can drop index parent_id_index instead.

When I attempt to drop the parent index concurrently, it fails

postgres=# drop index concurrently parent_id_index restrict;
ERROR:  cannot drop partitioned index "parent_id_index" concurrently

I can drop the parent index without concurrently, but then it will take an
ACCESS EXCLUSIVE lock which I am trying to avoid.

postgres=# drop index parent_id_index;
DROP INDEX
postgres=# \d parent
                                  Partitioned table "public.parent"
   Column   |            Type             | Collation | Nullable |
   Default
------------+-----------------------------+-----------+----------+------------------------------------
 id         | bigint                      |           | not null |
nextval('parent_id_seq'::regclass)
 created_at | timestamp without time zone |           | not null | now()
Partition key: RANGE (created_at)
Number of partitions: 1 (Use \d+ to list them.)

postgres=# \d child1
                                        Table "public.child1"
   Column   |            Type             | Collation | Nullable |
   Default
------------+-----------------------------+-----------+----------+------------------------------------
 id         | bigint                      |           | not null |
nextval('parent_id_seq'::regclass)
 created_at | timestamp without time zone |           | not null | now()
Partition of: parent FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-01-02
00:00:00')

This makes sense because the dependency is from the parent to the child, so
restrict is "correct".

I would like to be able to drop the parent index first without dropping the
child indices, and then drop all the child indices concurrently. Or vice
versa, drop the child indices concurrently first, and then drop the parent
index last.