Pivotal Knowledge Base

Follow

Alter of partition distribution key results in "ERROR: can't set the distribution policy of "xxx" "

Environment

Product Version
Pivotal Greenplum (GPDB) All Version
OS RHEL 6.x
Others  

Symptoms

Trying to change the distribution policy of the partition table ends with the with the error

ERROR:  can't set the distribution policy of "<relation_name>"

A simple example to reproduce the issue is to

  • Create the partition table
flightdata=# create table partition_table (
flightdata(#       i int, 
flightdata(#       j int, 
flightdata(#       k int, 
flightdata(#       l char(2)
flightdata(#    ) distributed by (i)
flightdata-# partition by range(k) 
flightdata-# subpartition by list(l) 
flightdata-# subpartition template(
flightdata(#       values('A'),
flightdata(#       values('B')
flightdata(#    ) (
flightdata(#       start(30) 
flightdata(#       end(50) 
flightdata(#       every(10)
flightdata(# );
NOTICE:  CREATE TABLE will create partition "partition_table_1_prt_1" for table "partition_table"
NOTICE:  CREATE TABLE will create partition "partition_table_1_prt_2" for table "partition_table"
NOTICE:  CREATE TABLE will create partition "partition_table_1_prt_1_2_prt_1" for table "partition_table_1_prt_1"
NOTICE:  CREATE TABLE will create partition "partition_table_1_prt_1_2_prt_2" for table "partition_table_1_prt_1"
NOTICE:  CREATE TABLE will create partition "partition_table_1_prt_2_2_prt_1" for table "partition_table_1_prt_2"
NOTICE:  CREATE TABLE will create partition "partition_table_1_prt_2_2_prt_2" for table "partition_table_1_prt_2"
CREATE TABLE
  • Attempt to change the distribution key of one of its partitions
flightdata=# alter table partition_table_1_prt_2 set distributed randomly;
ERROR:  can't set the distribution policy of "partition_table_1_prt_2"
HINT:  Distribution policy may be set for an entire partitioned table or one of its leaf parts; not for an interior branch.
  • Attempt to change the distribution key of only the main (parent) partition table also results in the same error.
flightdata=# alter table only partition_table set distributed randomly;
ERROR:  can't set the distribution policy of ONLY "partition_table"
HINT:  Distribution policy may be set for an entire partitioned table or one of its leaf parts.
flightdata=# 

Cause

This is by design, the child or leaf partitions cannot have a different set of distribution key compared to its parents or parent partition cannot have separate distribution key compared to its child.

Resolution

Change the distribution key universally using the following:

flightdata=# alter table partition_table set distributed randomly;
ALTER TABLE

Comments

Powered by Zendesk