gpcheckcat error: child partition(s) are distributed differently from the root partition

Products

• VMware Tanzu Greenplum

Issue/Introduction

This issue entails a scenario that an unique index created on a partitioned table.

The issue can be reproduced via below steps:

#Create a partition table distributed with (a, b, c)
gpadmin=# CREATE TABLE foo1 (a int, b int, c int)  DISTRIBUTED BY (a,b,c) PARTITION BY RANGE(a)
gpadmin-# (PARTITION p1 START (1) END (10000) INCLUSIVE,
gpadmin(# PARTITION p2 START (10001) END (100000) INCLUSIVE,
gpadmin(# PARTITION p3 START (100001) END (1000000) INCLUSIVE);
NOTICE:  CREATE TABLE will create partition "foo1_1_prt_p1" for table "foo1"
NOTICE:  CREATE TABLE will create partition "foo1_1_prt_p2" for table "foo1"
NOTICE:  CREATE TABLE will create partition "foo1_1_prt_p3" for table "foo1"
CREATE TABLE

#Create UNIQUE index on DIFFERENT COLUMN ORDER from the distribution order
create unique index acb_idx on public.foo1 using btree(a,c,b);

#Add a parition p4
gpadmin=# alter table public.foo1 add partition p4 START (1000001) END (2000000) INCLUSIVE;
NOTICE:  CREATE TABLE will create partition "foo1_1_prt_p4" for table "foo1"
ALTER TABLE

#Now we can find the partition p4's distribution key order is getting different from other leaf partitions including the root
gpadmin=# \d+ foo1_1_prt_p4
                    Table "public.foo1_1_prt_p4"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 a      | integer |           | plain   |              | 
 b      | integer |           | plain   |              | 
 c      | integer |           | plain   |              | 
Indexes:
    "foo1_1_prt_p4_a_c_b_idx" UNIQUE, btree (a, c, b)
Check constraints:
    "foo1_1_prt_p4_check" CHECK (a >= 1000001 AND a <= 2000000)
Inherits: foo1
Distributed by: (a, c, b)

gpadmin=# \d+ foo1_1_prt_p3
                    Table "public.foo1_1_prt_p3"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 a      | integer |           | plain   |              | 
 b      | integer |           | plain   |              | 
 c      | integer |           | plain   |              | 
Indexes:
    "foo1_1_prt_p3_a_c_b_idx" UNIQUE, btree (a, c, b)
Check constraints:
    "foo1_1_prt_p3_check" CHECK (a >= 100001 AND a <= 1000000)
Inherits: foo1
Distributed by: (a, b, c)

gpadmin=# 
gpadmin=# \d+ foo1
                         Table "public.foo1"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 a      | integer |           | plain   |              | 
 b      | integer |           | plain   |              | 
 c      | integer |           | plain   |              | 
Indexes:
    "acb_idx" UNIQUE, btree (a, c, b)
Child tables: foo1_1_prt_p1,
              foo1_1_prt_p2,
              foo1_1_prt_p3,
              foo1_1_prt_p4
Distributed by: (a, b, c)
Partition by: (a)

Then you will see such error in the gpcheckcat log.

Related code for the defect

foreach(cell, index_stmt->indexParams) <- The unique index columns
{
	IndexElem *iparam = lfirst(cell);
	ListCell *dkcell;
    // ...
	foreach(dkcell, distrkeys) <- The distribution key columns
	{
		IndexElem  *dk = (IndexElem *) lfirst(dkcell);

		if (strcmp(dk->name, iparam->name) == 0)
		{
		    // The columns in the newly created distribution key

		    // follow the unique index's columns order.

			new_distrkeys = lappend(new_distrkeys, dk);

			break;
		}
	}
} 

Environment

Product Version: 6.19

Resolution

The issue has been fixed in GPDB 6.25.2. 

Workaround prior to 6.25.2

#Drop the unique index and recreate it with the same key orders as distribution key order of the root partition

gpadmin=# drop index acb_idx;
DROP INDEX
Time: 12.556 ms
gpadmin=# create unique index acb_idx on public.foo1 using btree(a,b,c);
CREATE INDEX

gpadmin=# alter table public.foo1 add partition p5 START (2000001) END (3000000) INCLUSIVE;
NOTICE:  CREATE TABLE will create partition "foo1_1_prt_p5" for table "foo1"
ALTER TABLE
Time: 9.697 ms

gpadmin=# \d+ foo1_1_prt_p5
                    Table "public.foo1_1_prt_p5"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 a      | integer |           | plain   |              | 
 b      | integer |           | plain   |              | 
 c      | integer |           | plain   |              | 
Indexes:
    "foo1_1_prt_p5_a_b_c_idx" UNIQUE, btree (a, b, c)
Check constraints:
    "foo1_1_prt_p5_check" CHECK (a >= 2000001 AND a <= 3000000)
Inherits: foo1
Distributed by: (a, b, c)