Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oeHsm-0007CM-6A for pgsql-admin@arkaria.postgresql.org; Fri, 30 Sep 2022 15:24:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oeHsl-0000AO-1s for pgsql-admin@arkaria.postgresql.org; Fri, 30 Sep 2022 15:24:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oeHpb-0007iZ-Rn for pgsql-admin@lists.postgresql.org; Fri, 30 Sep 2022 15:21:40 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oeHpW-0005yR-S0 for pgsql-admin@lists.postgresql.org; Fri, 30 Sep 2022 15:21:38 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 517855C00F8; Fri, 30 Sep 2022 11:21:34 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute2.internal (MEProxy); Fri, 30 Sep 2022 11:21:34 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= quadratum-braccas.com; h=cc:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to; s=fm1; t=1664551294; x= 1664637694; bh=0S8lagauTeKdOJ0CbxPm0txKw2iNgF0rpDYDluqFAqk=; b=I xpqGgvk/wd5/zqSGGuHwtg4/aFIq8E0fAPzRdjVK1OzmfAivhPHKQKHvksxJWf6S x0bbEnMgg0y+T+vtFG4bNvy8/44KoMR+jTzK4U4Rpx3ittT/ZOOaOo2lHyr9L371 6PQzB6otNq1VK8jV6MWi49NiP93tKdK9xgS4c/k973OEjylHrZwv9+ueQh/R9eGe dlSsrshMPAKDIf3zcbbXycT+POqaAqv+S5Y7eu4pVAeERs0aQJKwRXnKUiJKcVvT 5nqdbZGYEhfZJW1THJ+SJ0TWiIpVT/L8zW+OmMICqgrYAR023vS9TmQe5FcZ0qfi 8kEEubJ9VAtQitaSpP+fw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:date:feedback-id :feedback-id:from:from:in-reply-to:in-reply-to:message-id :mime-version:references:reply-to:sender:subject:subject:to:to :x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm2; t=1664551294; x=1664637694; bh=0S8lagauTeKdOJ0CbxPm0txKw2iN gF0rpDYDluqFAqk=; b=bHV7LHcV5tSzuRuybJd2qyMUVr/tPAX9rulPOpsuYHmB K3NzD0T87sFDDfRgHu+2uLQpOQ81BDYivyuQ5oR0aKVKXlVtax2Amz/wk6CaZFSq k6BzTX9000ssb7I8GJd5Fj42T1JuEIJ8u9Cgml/45st4ODvG/BLq94dgHxZ+5L0E O69YA7e22VbH/BlSj/UITozhIEmbV73ajTS6waqMcVfgJbPFLlriGgtvozkoZFs6 aohoEQZOKpawJqKdos+BvQy3E+Hp/CFOScxoUgcYj3j/FyOzv+BSZU6TdNF8u0yX PFKh2m4TOmdJBmmc4gXqcixLWFTMi5kgajQ1l2BYWQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvfedrfeehvddgkeeiucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpegtkfffgggfuffhvfhfjgesrgdtre ertdefjeenucfhrhhomhepufgsohgsuceoshgsohgssehquhgrughrrghtuhhmqdgsrhgr tggtrghsrdgtohhmqeenucggtffrrghtthgvrhhnpedvgeejffehveeguddtjefhueffvd dvhfdvveejvdelieetieffvefhtdekgfffvdenucevlhhushhtvghrufhiiigvpedtnecu rfgrrhgrmhepmhgrihhlfhhrohhmpehssghosgesqhhurggurhgrthhumhdqsghrrggttg grshdrtghomh X-ME-Proxy: Feedback-ID: i374947ac:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 30 Sep 2022 11:21:33 -0400 (EDT) Content-Type: multipart/alternative; boundary="------------EIpZSAq8VNy2RQYqlVyWA3Rc" Message-ID: <42b71a74-1439-4bfa-5a3f-727e712c2ca0@quadratum-braccas.com> Date: Fri, 30 Sep 2022 09:21:33 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.1 Subject: Re: Issue with partitioning - legitimate insert fails with no partition message Content-Language: en-US From: Sbob To: Pgsql-admin References: <2121b49f-52fb-ab79-3ea1-c398d7a8656f@quadratum-braccas.com> In-Reply-To: <2121b49f-52fb-ab79-3ea1-c398d7a8656f@quadratum-braccas.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------EIpZSAq8VNy2RQYqlVyWA3Rc Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > > > > > > --------------EIpZSAq8VNy2RQYqlVyWA3Rc Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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






--------------EIpZSAq8VNy2RQYqlVyWA3Rc--