No partition for the partitioning key error while "insert into emcconnect_history select * from _emcconnect_tail"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

The following error message is produced in the master log regarding a missing partitioning key for an "emcconnect_history" table when inserting data from "_emcconnect_tail".

Error message

2018-01-16 04:16:58.480682 PST,"gpmon","gpperfmon",p47906,th-1921259744,"[local]",,2018-01-16 04:16:37 PST,267147683,con3303450,cmd2,seg-1,,dx13793446,x267147683,sx1,"ERROR","22M01","no partition for partitioning key (seg39 sdw5.gphd.local:40007 pid=312495)",,,,,,"insert into emcconnect_history select * from _emcconnect_tail;",0,,"cdbdisp.c",1322,

Environment

Cause

The "emcconnect_history" table displays information about ConnectEMC events and alerts. This table is pre-partitioned into monthly partitions. Partitions are automatically added in the one-month increments as needed. Administrators must drop old partitions for the months that are no longer needed. If an administrator drops a partition and there are still unprocessed errors for that period, the error message above will be produced. 

Resolution

There are two ways to fix this issue:
 

A. If keeping the old data is a priority, add a missing partition to the "emcconnect_history" table.
 

1. Check the existing partitions in the "emcconnect_history":

psql gpperfmon -c "SELECT partitiontablename,partitionboundary from pg_partitions where tablename='emcconnect_history'"
partitiontablename | partitionboundary
--------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------
emcconnect_history_1_prt_1 | START ('2010-01-01 00:00:00'::timestamp without time zone) END ('2010-02-01 00:00:00'::timestamp without time zone) EVERY ('1 mon'::inter
val)
emcconnect_history_1_prt_r1820368615 | START ('2015-09-01 00:00:00'::timestamp without time zone) END ('2015-10-01 00:00:00'::timestamp without time zone)
emcconnect_history_1_prt_r1697446024 | START ('2015-10-01 00:00:00'::timestamp without time zone) END ('2015-11-01 00:00:00'::timestamp without time zone)
emcconnect_history_1_prt_r1544897591 | START ('2015-11-01 00:00:00'::timestamp without time zone) END ('2015-12-01 00:00:00'::timestamp without time zone)
emcconnect_history_1_prt_r709382015 | START ('2015-12-01 00:00:00'::timestamp without time zone) END ('2016-01-01 00:00:00'::timestamp without time zone)
emcconnect_history_1_prt_r1216271574 | START ('2016-01-01 00:00:00'::timestamp without time zone) END ('2016-02-01 00:00:00'::timestamp without time zone)
emcconnect_history_1_prt_r2107417173 | START ('2016-02-01 00:00:00'::timestamp without time zone) END ('2016-03-01 00:00:00'::timestamp without time zone)
(7 rows)

2. Check date range of the entries from the "_emcconnect_tail" file:

psql gpperfmon -c "select date_trunc('month',ctime) as month from _emcconnect_tail group by month order by month"
 month
---------------------
 2015-08-01 00:00:00
 2015-09-01 00:00:00
 2015-10-01 00:00:00
 2015-11-01 00:00:00
 2015-12-01 00:00:00
 2016-01-01 00:00:00
(6 rows)

3. From the example above, observe that there are entries in the "_emcconnect_tail" table for the 2015-08-01 period. The "emcconnect_history" partition range starts from 2015-09-01. In order to fix this, add the missing partition for the 2015-08-01 range that ends on 2015-09-01 (2015-09-01 is the start of the emcconnect_history_1_prt_r1820368615 partition).

psql gpperfmon
ALTER TABLE emcconnect_history ADD PARTITION START ('2015-08-01 00:00:00'::timestamp without time zone) END ('2015-09-01 00:00:00'::timestamp without time zone) ;

B. If old data is not needed, remove the entries from the "_emcconnect_tail.dat" file.

psql gpperfmon -c "\d _emcconnect_tail"| grep Command
Command: cat gpperfmon/data/_emcconnect_tail.dat 2> /dev/null || true

By default, the file is located in $MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat.
 

1. Take a backup of the file and remove the old entries from the 2015-08 period:

cp$MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat /tmp/_emcconnect_tail.dat.bkp
sed '/^2015-08/d' <$MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat >$MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat.new
mv$MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat.new$MASTER_DATA_DIRECTORY/gpperfmon/data/_emcconnect_tail.dat

2. To verify if the problem is fixed, run the following:

psql gpperfmon -c "insert into emcconnect_history select * from _emcconnect_tail"