From: Sbob <sbob@quadratum-braccas.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Issue with partitioning - legitimate insert fails with no partition message
Date: Fri, 30 Sep 2022 09:21:33 -0600
Message-ID: <42b71a74-1439-4bfa-5a3f-727e712c2ca0@quadratum-braccas.com> (raw)
In-Reply-To: <2121b49f-52fb-ab79-3ea1-c398d7a8656f@quadratum-braccas.com>
References: <2121b49f-52fb-ab79-3ea1-c398d7a8656f@quadratum-braccas.com>
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
>
>
>
>
>
>
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: <42b71a74-1439-4bfa-5a3f-727e712c2ca0@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