Renaming table got error "duplicate key value violates unique constraint "pg_class_relname_nsp_index""

Products

• VMware Tanzu Greenplum

Issue/Introduction

If we rename a partitioned table, we may possibly get following error message:

"ERROR","23505","duplicate key value violates unique constraint ""pg_class_relname_nsp_index""","Key (relname, relnamespace)=(abcdefg_abcdef_abcdefghij_abc_abc_20240315185024_1_prt_p2024030, 4504410) already exists.",,,,,"alter table abc.abc_def_hijklmn rename to abcdefg_abcdef_abcdefghij_abc_abc_20240315185024",0,,"nbtinsert.c",430,

Our initial thought is maybe it's due to index corruption. After validation, the index is good. So what can be the cause？

Environment

Product Version: 6.25

Resolution

The real cause is related to the max table name length that is controlled by "NAMEDATALEN-1", which is 63.

When we rename a partitioned table to a long name, the leaf partition table names will also be renamed with an additional suffix(like "_1_prt_pxxxxxx" added to it. So there is a potential risk that the leaf partition name may exceed 63 characters. Thus, the leaf partition name will be truncated to 63 characters, and the next leaf partition name will be truncated as the same, then it will fail reporting duplicate table names like:

"ERROR","23505","duplicate key value violates unique constraint ""pg_class_relname_nsp_index""","Key (relname, relnamespace)=(abcdefg_abcdef_abcdefghij_abc_abc_20240315185024_1_prt_p2024030, 4504410) already exists.",,,,,"alter table abc.abc_def_hijklmn rename to abcdefg_abcdef_abcdefghij_abc_abc_20240315185024",0,,"nbtinsert.c",430, 

This is an expected behavior. Solution is to shorten the name to be renamed to ensure the leaf partition names will not exceed the value of "NAMEDATALEN-1", which is 63.