Cannot add RANGE partition to partitioned table

Products

• VMware Tanzu Greenplum

Issue/Introduction

The following partitioned table was created:

CREATE TABLE edw_agg.beong_test (
account_key bigint,
program_key bigint,
activity_key bigint,
employer_key bigint,
umc_date date,
umc_date_key integer,
umc_week_key integer,
umc_month_key integer,
umc_quarter_key integer,
umc_year_key integer,
gender character varying(255),
age character varying(255),
state character varying(255),
relationship character varying(255),
attribute1_name character varying(255),
attribute1_value character varying(255),
attribute2_name character varying(255),
attribute2_value character varying(255),
attribute3_name character varying(255),
attribute3_value character varying(255),
attribute4_name character varying(255),
attribute4_value character varying(255),
attribute5_name character varying(255),
attribute5_value character varying(255),
is_eligible_for_program_flag boolean,
is_eligible_and_registered_flag boolean,
is_registered_flag boolean,
has_completed_activity_flag boolean,
edw_created_ts timestamp without time zone,
edw_updated_ts timestamp without time zone,
edw_created_by character varying(255),
edw_updated_by character varying(255)
) DISTRIBUTED BY (account_key, program_key, activity_key, employer_key, umc_date, umc_week_key) PARTITION BY RANGE(umc_week_key)
(
PARTITION wk31_2019 START (201931) INCLUSIVE,
PARTITION wk32_2019 START (201932) INCLUSIVE,
PARTITION wk33_2019 START (201933) INCLUSIVE,
DEFAULT PARTITION extra_wkly );

When trying to add a new  range partition to it, the following error is encountered:

testdb=# alter table edw_agg.beong_test ADD PARTITION wk34_2019 START ('201934') INCLUSIVE ;
ERROR: cannot add RANGE partition "wk34_2019" to relation "beong_test" with DEFAULT partition "extra_wkly"
HINT: need to SPLIT partition "extra_wkly"

Environment

Product Version: 5.2

Resolution

The partitioned table was incorrectly defined, so when START was used in the definition, there was no END for each partition, which causes the LAST partition to overlap with the DEFAULT partition:

...
PARTITION wk33_2019 START (201933),
DEFAULT PARTITION extra_wkly 

In addition, by GPDB/Postgres design it is not possible to add a new partition directly into the partition table.

Steps to address the issue

1. Change the partitioned table definition to include END clause for the partitions:

CREATE TABLE edw_agg.beong_test (
account_key bigint,
program_key bigint,
activity_key bigint,
employer_key bigint,
umc_date date,
umc_date_key integer,
umc_week_key integer,
umc_month_key integer,
umc_quarter_key integer,
umc_year_key integer,
gender character varying(255),
age character varying(255),
state character varying(255),
relationship character varying(255),
attribute1_name character varying(255),
attribute1_value character varying(255),
attribute2_name character varying(255),
attribute2_value character varying(255),
attribute3_name character varying(255),
attribute3_value character varying(255),
attribute4_name character varying(255),
attribute4_value character varying(255),
attribute5_name character varying(255),
attribute5_value character varying(255),
is_eligible_for_program_flag boolean,
is_eligible_and_registered_flag boolean,
is_registered_flag boolean,
has_completed_activity_flag boolean,
edw_created_ts timestamp without time zone,
edw_updated_ts timestamp without time zone,
edw_created_by character varying(255),
edw_updated_by character varying(255)
) DISTRIBUTED BY (account_key, program_key, activity_key, employer_key, umc_date, umc_week_key) PARTITION BY RANGE(umc_week_key) 
(
PARTITION wk31_2019 START (201931) INCLUSIVE END (201932) EXCLUSIVE,
PARTITION wk32_2019 START (201932) INCLUSIVE END (201933) EXCLUSIVE,
PARTITION wk33_2019 START (201933) INCLUSIVE END (201934) EXCLUSIVE,
DEFAULT PARTITION extra_wkly );

2. In order to add a new partition, drop the default partition first and then create a a new partition, followed by adding back the default partition:
 

testdb=# alter table edw_agg.beong_test drop partition extra_wkly;
ALTER TABLE
 
testdb=# alter table edw_agg.beong_test ADD PARTITION wk34_2019 START (201934) INCLUSIVE END (201935) EXCLUSIVE ;
NOTICE:  CREATE TABLE will create partition "beong_test_1_prt_wk34_2019" for table "beong_test"
ALTER TABLE
 
testdb=# alter table edw_agg.beong_test ADD PARTITION wk34_2019 START (201934) INCLUSIVE END (201935) EXCLUSIVE ;
NOTICE:  CREATE TABLE will create partition "beong_test_1_prt_wk34_2019" for table "beong_test"
ALTER TABLE