Re: Issue with partitioning - legitimate insert fails with no partition message

[Sbob <sbob@xxxxxxxxxxxxxxxxxxxxx>]

a bit more info:

this is on PostgreSQL 14

It seems that the range for partitions is inclusive of the starting value and exclusive of the ending value, maybe this is documented and I missed it?

I assume this because this insert also fails, using the ending value of the second partition:

insert into localdata2
values (5000000, now(), 21, 55, 'description goes here');       
ERROR:  no partition of relation "localdata2" found for row
DETAIL:  Partition key of the failing row contains (local_id) = (5000000).

On 9/30/22 09:16, Sbob wrote:

All;

I created a partitioned table (using declarative partitioning) and I am seeing an error inserting a row, the db claims there is no partition for my insert but as far as I can thell there is.

Here is my setup:


CREATE TABLE localdata2 (  
           local_id    bigserial ,
           logts       timestamp without time zone,
           mintemp     int,
           maxtemp     int,
           description     text
           ) PARTITION BY RANGE (local_id) ;

CREATE TABLE remote1_data1 PARTITION OF localdata2
               FOR VALUES FROM ('1') TO ('500000') ;

CREATE TABLE remote2_data1 PARTITION OF localdata2
               FOR VALUES FROM ('500001') TO ('5000000') ;

\d+ localdata2
                                                                Partitioned table "public.localdata2"
  Column    |            Type             | Collation | Nullable |                   Default                    | Storage  | Compression | Stats target | Description  
-------------+-----------------------------+-----------+----------+----------------------------------------------+----------+-------------+--------------+-------------
local_id    | bigint                      |           | not null | nextval('localdata2_local_id_seq'::regclass) | plain    |             |              |  
logts       | timestamp without time zone |           |          |                                              | plain    |             |              |  
mintemp     | integer                     |           |          |                                              | plain    |             |              |  
maxtemp     | integer                     |           |          |                                              | plain    |             |              |  
description | text                        |           |          |                                              | extended |             |              |  
Partition key: RANGE (local_id)
Partitions: remote1_data1 FOR VALUES FROM ('1') TO ('500000'),
           remote2_data1 FOR VALUES FROM ('500001') TO ('5000000')

insert into localdata2                             
values (500000, now(), 21, 55, 'description goes here');  
ERROR:  no partition of relation "localdata2" found for row
DETAIL:  Partition key of the failing row contains (local_id) = (500000).

The remote1_data1 partition should allow the value 500000 for the local_id but it is failing...

Thoughts?

Thanks in advance