Обсуждение: Issue with partitioning - legitimate insert fails with no partition message
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
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).
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
> On Sep 30, 2022, at 9:21 AM, Sbob <sbob@quadratum-braccas.com> wrote: > > It seems that the range for partitions is inclusive of the starting value and exclusive of the ending value, maybe thisis documented and I missed it? Yes, it is. (and "to" is not "through") The explanation is: think of a floating-point value, if it included the end value would you use XXX.999, or XXX.9999, orXXX.99999... Same argument applies to timestamps--even though they are stored as 64-bit integers, how would you figureout the value that is 1 bit less than your end date?