Dropping index on a leaf partition fails with ERROR "can't drop a constraint; it is part of a partitioned table" in Greenplum 5.x

Products

• VMware Tanzu Greenplum

Issue/Introduction

You may have an orphan index left on only one or a few leaf partitions of a partitioned table. When you try to drop them from the leaf partitions, it will report error like:

gpadmin=# alter table foo1_1_prt_p1 drop constraint aaa;
ERROR:  can't drop a constraint from "foo1_1_prt_p1"; it is part of a partitioned table 

Environment

Greenplum Database 5.x

Resolution

The GUC gp_enable_drop_key_constraint_child_partition may allow the index to be dropped.

Enable the GUC, and the drop can proceed:

gpadmin=# set gp_enable_drop_key_constraint_child_partition=on;
SET
gpadmin=# alter table foo1_1_prt_p1 drop constraint aaa;
ALTER TABLE

Note: This GUC only works in Greenplum Database 5.x. It is not present in version 6.x and above.

Additional Information

It's strongly recommended to engage support if you need to change the GUC for non-EOL Greenplum versions. If it is an EOL version, please upgrade your GPDB to a non-EOL version first and then engage support for the change.