From: | Sbob <sbob(at)quadratum-braccas(dot)com> |
---|---|
To: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Issue with partitioning - legitimate insert fails with no partition message |
Date: | 2022-09-30 15:16:52 |
Message-ID: | 2121b49f-52fb-ab79-3ea1-c398d7a8656f@quadratum-braccas.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sbob | 2022-09-30 15:21:33 | Re: Issue with partitioning - legitimate insert fails with no partition message |
Previous Message | Adriano Peluso | 2022-09-23 10:25:52 | Re: getting a ER diagram from an existing database |