Query errors out "no partition for partitioning key"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

When inserting data into the "log_alert_history" table in the GPDB, the following error message is produced:

insert into log_alert_history select * from log_alert_tail;
ERROR: no partition for partitioning key (seg1 sdw1:40002 pid=2792)

Environment

Cause

In the GPDB, there are 3 tables involved in recording alert log information from the pg_log files. These tables are:
 

1. log_alert_now: Current pg_log errors and warnings data is stored in log_alert_now during the period between data collection from the Command Center agents and automatic commitment to the log_alert_history table.
 

2. log_alert_tail: This is the transitional table for query workload data that has been cleared from log_alert_now but has not yet been committed to log_alert_history. It typically only contains a few minutes worth of data.
 

3. log_alert_history: This is the regular table that stores historical database-wide errors and warnings data.
 

The error message above indicates that one of the partition tables for the table "log_alert_history" has been dropped and the insert statement is trying to insert data for a time range to the dropped partition table.

Resolution

There are two ways to resolve this issue:
 

1. Recreate the partition table that has been dropped for the time period the data is being inserted for.
 

2. Delete the data from the "log_alert_now' or log_alert_tail" tables for the time period that the partition table has been dropped for.