pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIssue with partitioning - legitimate insert fails with no partition message
3+ messages / 2 participants
[nested] [flat]
* Issue with partitioning - legitimate insert fails with no partition message
@ 2022-09-30 15:16 Sbob <sbob@quadratum-braccas.com>
2022-09-30 15:21 ` Re: Issue with partitioning - legitimate insert fails with no partition message Sbob <sbob@quadratum-braccas.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sbob @ 2022-09-30 15:16 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Issue with partitioning - legitimate insert fails with no partition message
2022-09-30 15:16 Issue with partitioning - legitimate insert fails with no partition message Sbob <sbob@quadratum-braccas.com>
@ 2022-09-30 15:21 ` Sbob <sbob@quadratum-braccas.com>
2022-09-30 15:29 ` Re: Issue with partitioning - legitimate insert fails with no partition message Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sbob @ 2022-09-30 15:21 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Issue with partitioning - legitimate insert fails with no partition message
2022-09-30 15:16 Issue with partitioning - legitimate insert fails with no partition message Sbob <sbob@quadratum-braccas.com>
2022-09-30 15:21 ` Re: Issue with partitioning - legitimate insert fails with no partition message Sbob <sbob@quadratum-braccas.com>
@ 2022-09-30 15:29 ` Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 3+ messages in thread
From: Scott Ribe @ 2022-09-30 15:29 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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 this is 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, or XXX.99999... Same argument applies to timestamps--even though they are stored as 64-bit integers, how would you figure out the value that is 1 bit less than your end date?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-09-30 15:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-09-30 15:16 Issue with partitioning - legitimate insert fails with no partition message Sbob <sbob@quadratum-braccas.com>
2022-09-30 15:21 ` Sbob <sbob@quadratum-braccas.com>
2022-09-30 15:29 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox