Pivotal Knowledge Base

Follow

Query gives "ERROR: No partition for partitioning key"

Environment 

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

Symptom

When inserting data into the "log_alert_history" table in the Gpperfmon DB, it is giving the following error message:

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

Cause

In the Gpperfmon DB, there are 3 tables involved in recording alert log information from the pg_log files for a Greenplum DB. These tables are:

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.

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 of worth of data.

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

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

Resolution

There are 2 choices to resolve this issue:

1. You can 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.

Comments

Powered by Zendesk