How to Fix No Partition of Relation Found for Row in Postgres Databases
ERROR: No Partition of Relation Found for Row
The error message ERROR: no partition of relation {table-name} found for row is reported by PostgreSQL (and will appear in the console and the log) when a table has been configured with declarative partitioning, and data is INSERTed before a child table has been defined with constraints that match the data. This will cause the insert to fail, potentially losing the data which was in flight.
You will find this error message in other PostgreSQL-based databases, such as Amazon RDS for PostgreSQL and Amazon Aurora. But it can be avoided in Timescale when you use our hypertable abstraction. In this blog post, we’ll explain this database error in more detail to learn why.
Explanation
Let’s dive deeper into what causes a no partition of relation found for row error. When a table is partitioned using PostgreSQL declarative partitioning, it becomes a parent to which multiple child partitions can be attached. Each of these children can handle a specific non-overlapping subset of data. When partitioning by time (the most common use case), each partition would be attached for a particular date range. For example, seven daily partitions could be attached, representing the upcoming week.
When inserts are made into the parent table, these are transparently routed to the child table, matching the partitioning criteria. So an insert of a row that referenced tomorrow would be sent automatically to tomorrow’s partition. If this partition doesn’t exist, then there is a problem—there is no logical place to store this data. PostgreSQL will fail the INSERT and report no partition of relation {table-name} found for row.
How to Resolve
There are two ways around this problem, although neither is perfect. Keep reading to see the Timescale approach with hypertables that avoids these pitfalls.
Partitions can be made ahead of time—perhaps a scheduler could be used to create a month's worth of partitions automatically in advance. This works in theory (as long as that scheduler keeps running!) but will cause locking issues while the partitions are being created. Plus, it doesn’t account for data in the past or the far future.
A default partition can also be added that automatically catches all data that doesn’t have a home, but this is problematic, too, as it collects data that needs to eventually be moved into freshly created partitions. As the amount of orphaned data in the default partition grows, it will also slow down query times.
Documentation and Resources
- Timescale hypertables work like regular PostgreSQL tables but provide a superior user experience when handling time-series data.
- Need some advice on how to model your time-series data using hypertables? Read our best practices about choosing between a narrow, medium, or wide hypertable layout and learn when to use single or multiple hypertables.
How Timescale Can Help
As mentioned earlier, another solution is enabling the TimescaleDB extension and converting the table into a hypertable instead of using PostgreSQL declarative partitioning. This removes the need to worry about partitions (which in Timescale jargon are called chunks), as they are transparently made when inserts happen with no locking issues.
You’ll never have to see this error, worry about scheduling potentially disruptive partition creation, or think about default partitions ever again!
New to Timescale? Sign up for Timescale (30-day free trial, no credit card required) for fast performance, seamless user experience, and the best compression ratios.
