BUG #18171: Dropping an index on a partitioned table drops all child indices even with a restrict
От | PG Bug reporting form |
---|---|
Тема | BUG #18171: Dropping an index on a partitioned table drops all child indices even with a restrict |
Дата | |
Msg-id | 18171-4abd023036968941@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #18171: Dropping an index on a partitioned table drops all child indices even with a restrict
("David G. Johnston" <david.g.johnston@gmail.com>)
|
Список | 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.
В списке pgsql-bugs по дате отправления:
Предыдущее
От: PG Bug reporting formДата:
Сообщение: BUG #18170: Unexpected error: no relation entry for relid 3
Следующее
От: Vik FearingДата:
Сообщение: Re: BUG #18170: Unexpected error: no relation entry for relid 3