Postgresql – Postgres table partitioning “no partition of relation ”parsel_part“ found for row” error

partitioningpostgresqlpostgresql-10

I'm trying to use new partitioning method of PostgreSQL 10. I have a parent table that contains 1.5 million rows. I want to create partitioning on this already populated table.

I've created a new master table which has identical columns with real master table from the CREATE SCRIPT;

CREATE TABLE master_part (objectid integer,poly geometry(Geometry,2321), parcel character varying(255), m_date(date)) PARTITION BY RANGE (m_date);

Then I've created child tables, partitioned by the m_date column;

CREATE TABLE parsel_2014_04
  PARTITION OF parsel_part FOR VALUES FROM ('2014-04-01') TO ('2014-04-30');

CREATE TABLE parsel_2014_05
      PARTITION OF parsel_part FOR VALUES FROM ('2014-05-01') TO ('2014-05-31');

CREATE TABLE parsel_2014_06
      PARTITION OF parsel_part FOR VALUES FROM ('2014-06-01') TO ('2014-06-30');

CREATE TABLE parsel_2014_07
      PARTITION OF parsel_part FOR VALUES FROM ('2014-07-01') TO ('2014-07-31');

CREATE TABLE parsel_2014_08
      PARTITION OF parsel_part FOR VALUES FROM ('2014-08-01') TO ('2014-08-31');

CREATE TABLE parsel_2014_09
      PARTITION OF parsel_part FOR VALUES FROM ('2014-09-01') TO ('2014-09-30');

CREATE TABLE parsel_2014_10
      PARTITION OF parsel_part FOR VALUES FROM ('2014-10-01') TO ('2014-10-30');

CREATE TABLE parsel_2014_11
      PARTITION OF parsel_part FOR VALUES FROM ('2014-11-01') TO ('2014-11-30');

CREATE TABLE parsel_2014_12
      PARTITION OF parsel_part FOR VALUES FROM ('2014-12-01') TO ('2014-12-31');

CREATE TABLE parsel_2015_01
      PARTITION OF parsel_part FOR VALUES FROM ('2015-01-01') TO ('2015-01-31');

CREATE TABLE parsel_2015_02
      PARTITION OF parsel_part FOR VALUES FROM ('2015-02-01') TO ('2015-02-28');

CREATE TABLE parsel_2015_03
      PARTITION OF parsel_part FOR VALUES FROM ('2015-03-01') TO ('2015-03-31');

CREATE TABLE parsel_2015_04
      PARTITION OF parsel_part FOR VALUES FROM ('2015-04-01') TO ('2015-04-30');

When I run the script above to create child tables I'm getting the error below;

ERROR: no partition of relation "parsel_part" found for row
DETAIL: Partition key of the failing row contains (m_date) = (2014-10-31).
SQL state: 23514

This error seems to be very rare because I couldn't find anything about it.

Maybe some one have seen it before ?

Best Answer

Your problem is related to this point in the documentation:

When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound.

(emphasis added)

So in fact in

CREATE TABLE parsel_2014_10 PARTITION OF parsel_part 
     FOR VALUES FROM ('2014-10-01') TO ('2014-10-31');

the date 2014-10-31 is not included in this partition, and hence nowhere in all your partition tables (and same for all ending dates)

See the example at: https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

The to value of one partition must be the same as the from value of the next one (because the to part is exclusive and the from is inclusive).

Related Question