catalog error Constraint "xxx" of partitioned table "xxx" occurs 1 times, 54 expected.

Products

• VMware Tanzu Greenplum

Issue/Introduction

The cause of the issue is that an index exists on the root partition of a partitioned table but not on its leaf partitions.
Normally adding a constraint/index using ONLY keyword as below can cause the issue as it will add index on the root partition only:

ALTER TABLE ONLY public.test ADD CONSTRAINT test_pkey PRIMARY KEY (a);

Environment

Product Version: 6.18

Resolution

The workaround/solution is to recreate the index:

ALTER TABLE ONLY public.test DROP CONSTRAINT test_pkey
ALTER TABLE public.test ADD CONSTRAINT test_pkey PRIMARY KEY (a);

It's still recommended to double confirm the index only exists on the root partition before taking the above solution.