Issue with partitioning - legitimate insert fails with no partition message

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

Responses

Browse pgsql-admin by date

  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