From: Sbob <sbob@quadratum-braccas.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Issue with partitioning - legitimate insert fails with no partition message
Date: Fri, 30 Sep 2022 09:16:52 -0600
Message-ID: <2121b49f-52fb-ab79-3ea1-c398d7a8656f@quadratum-braccas.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sbob@quadratum-braccas.com, pgsql-admin@lists.postgresql.org
Subject: Re: Issue with partitioning - legitimate insert fails with no partition message
In-Reply-To: <2121b49f-52fb-ab79-3ea1-c398d7a8656f@quadratum-braccas.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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