Error "cannot add RANGE partition to relation xxxx with DEFAULT partition other"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Each month, the master log will show the following error when trying to add a new partition to a gpperfmon table:

ERROR: cannot add RANGE partition to relation "database_history" with DEFAULT partition "other"

Note- This error only applies to tables in the gpperfmon database.

 

Environment

Cause

The documentation for several gpperfmon tables:

database_history is a regular table that stores historical database-wide query workload data. It is pre-partitioned into monthly partitions. Partitions are automatically added in two-month increments as needed. Administrators must drop old partitions for the months that are no longer needed.

When these tables are created, they are not given a default partition and the DDL statements that automatically adds the new monthly partitions are written with that assumption. If the original partition table's definition is manually changed by a user to add a default partition, then you will get the ERROR message once or twice a month for the changing table.

Note- Some may choose to do this to avoid the need to manually clean up old partitions. However, all the new monitoring entries will be added to the default partition table. If that partition gets too big, it can affect query performance and result in the need to manually split the partition later.

An example is shown below:

gpperfmon=# \d+ database_history
 Table "public.database_history"
 Column | Type | Modifiers | Storage | Description
-----------------+--------------------------------+-----------+---------+-------------
 ctime | timestamp(0) without time zone | not null | plain |
 queries_total | integer | not null | plain |
 queries_running | integer | not null | plain |
 queries_queued | integer | not null | plain |
Child tables: database_history_1_prt_1,
 database_history_1_prt_r1575462924,
 database_history_1_prt_r261921944,
 database_history_1_prt_r630565216
Has OIDs: no
Options: fillfactor=100
Distributed by: (ctime)
Partition by: (ctime)

Each month, the following alter statement will be run:

alter table database_history add partition start ('2017-12-01 00:00:00'::timestamp without time zone) 
inclusive end ('2018-01-01 00:00:00'::timestamp without time zone) exclusive;
gpperfmon=# alter table database_history add partition start ('2017-11-01 00:00:00'::timestamp without time zone) 
inclusive end ('2017-12-01 00:00:00'::timestamp without time zone) exclusive;
NOTICE: CREATE TABLE will create partition "database_history_1_prt_r1474380594" for table "database_history"
ALTER TABLE>

As you can see, the new monthly partition is added with no problem. Now let's see what happens when we add a default partition to the table:

ALTER TABLE database_history ADD DEFAULT PARTITION other;
gpperfmon=# ALTER TABLE database_history ADD DEFAULT PARTITION other;
NOTICE: CREATE TABLE will create partition "database_history_1_prt_other" for table "database_history2"
ALTER TABLE
gpperfmon=# alter table database_history add partition start ('2017-12-01 00:00:00'::timestamp without time zone) 
inclusive end ('2018-01-01 00:00:00'::timestamp without time zone) exclusive;
ERROR: cannot add RANGE partition to relation "database_history" with DEFAULT partition "other"
HINT: need to SPLIT partition "other"

gpperfmon will no longer be able to automatically add new monthly partitions.  

 

Resolution

As long as data is continuing to be added to the table, the ERROR message can be ignored. If the data volume is too large, then split the partition as needed.

If you want to restore the old functionality, you can use the following workaround to split the default partition which will move the data from the default partition. 

1. Determine your partition ranges to make sure there will be no overlap:

SELECT tablename, 
partitiontablename, partitionrangestart, 
partitionstartinclusive, partitionrangeend, 
partitionendinclusive 
FROM pg_partitions WHERE
tablename = 'database_history2';

2. Find out the timestamp range of the data in your default partition table:

select min(ctime), max(ctime) from database_history_1_prt_other;

3. Split the default partition using the date range found in step 2. Usually, this range is from the date the default partition was added to the current date. For example:

ALTER TABLE database_history SPLIT DEFAULT PARTITION 
START ('2017-12-01') INCLUSIVE 
END ('2018-03-01') EXCLUSIVE 
INTO (PARTITION Dec2017_Feb2018, PARTITION other);

4. This will move the data from the "other" partition to the newly named "Dec2017_Feb2018" partition. Verify that there is no data in the database_history_1_prt_other partition table. If so, you can safely drop the default partition and the error message will disappear beginning with the next non-overlapping new month.

gpperfmon=# alter table database_history drop default partition;
NOTICE: dropped partition "other" for relation "database_history"
ALTER TABLE