Feedback-ID: i374947ac:Fastmail
Content-Type: multipart/alternative;
 boundary="------------YQf9kNaDagQWkcb9agJv9UH3"
Message-ID: <cfa50ec5-eca3-4aa3-aa6e-d22f969f3ee7@quadratum-braccas.com>
Date: Tue, 22 Oct 2024 11:05:02 -0600
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: ERROR Inserting into partition
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-admin@lists.postgresql.org>
References: <7bc2cd63-dff7-46bf-9cca-ee950463f13a@quadratum-braccas.com>
 <CAKFQuwZxzmLvTZcgUkaiN2rrJuZ6LmyOVPMVRCEMKYWujyZCxQ@mail.gmail.com>
Content-Language: en-US
From: Sbob <sbob@quadratum-braccas.com>
In-Reply-To: 
 <CAKFQuwZxzmLvTZcgUkaiN2rrJuZ6LmyOVPMVRCEMKYWujyZCxQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/cfa50ec5-eca3-4aa3-aa6e-d22f969f3ee7%40quadratum-braccas.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------YQf9kNaDagQWkcb9agJv9UH3
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Thanks, it turns out it was subtle user error


On 10/22/24 8:53 AM, David G. Johnston wrote:
> On Tue, Oct 22, 2024 at 7:36 AM Sbob <sbob@quadratum-braccas.com> wrote:
>
>
>     INSERT INTO part_schema.cust_info_5
>     ( cust_last_name)
>     SELECT l_name from public.global_addr WHERE l_name = 'NELL';
>     ERROR:  new row for relation "cust_info_5" violates partition
>     constraint
>     DETAIL:  Failing row containd (null, null, null, null, null, 'NELL',
>     null, null, null, null, null,null).
>
>
> Also, I am unable to reproduce this in development:
>
> CREATE TABLE cust_part (
>             cust_int_id     bigint,
>            cust_ext_id    bigint,
>            cust_tax_id   varchar(20),
>            cust_dob        date,
>            cust_dob_str   varchar(20),
>           cust_last_name  varchar(100),
>          cust_first_name   varchar(100),
>           cust_middle_name  varchar(100),
>          cust_addr1                text,
>         cust_city                     text,
>         cust_state                  text,
>         cust_zip                     varchar(5)
> ) PARTITION BY LIST (cust_last_name);
>
> CREATE TABLE cust_info_5
> PARTITION OF cust_part
> FOR VALUES IN ('NELL');
>
> create table global_addr (l_name text);
>
> insert into global_addr values ('NELL');
>
> INSERT INTO cust_info_5
> ( cust_last_name)
> SELECT l_name from global_addr WHERE l_name = 'NELL';
>
> David J.
--------------YQf9kNaDagQWkcb9agJv9UH3
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Thanks, it turns out it was subtle user error</p>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 10/22/24 8:53 AM, David G. Johnston
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAKFQuwZxzmLvTZcgUkaiN2rrJuZ6LmyOVPMVRCEMKYWujyZCxQ@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif"><span
              style="font-family:Arial,Helvetica,sans-serif">On Tue, Oct
              22, 2024 at 7:36 AM Sbob &lt;<a
                href="mailto:sbob@quadratum-braccas.com"
                moz-do-not-send="true" class="moz-txt-link-freetext">sbob@quadratum-braccas.com</a>&gt;
              wrote:</span></div>
        </div>
        <div class="gmail_quote">
          <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><br>
            INSERT INTO part_schema.cust_info_5<br>
            ( cust_last_name)<br>
            SELECT l_name from public.global_addr WHERE l_name = 'NELL';<br>
            ERROR:  new row for relation "cust_info_5" violates
            partition constraint<br>
            DETAIL:  Failing row containd (null, null, null, null, null,
            'NELL', <br>
            null, null, null, null, null,null).<br>
            <br>
          </blockquote>
          <div><br>
          </div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif">Also, I am
            unable to reproduce this in development:</div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif"><br>
          </div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif">CREATE TABLE
            cust_part (<br>
                        cust_int_id     bigint,<br>
                       cust_ext_id    bigint,<br>
                       cust_tax_id   varchar(20),<br>
                       cust_dob        date,<br>
                       cust_dob_str   varchar(20),<br>
                      cust_last_name  varchar(100),<br>
                     cust_first_name   varchar(100),<br>
                      cust_middle_name  varchar(100),<br>
                     cust_addr1                text,<br>
                    cust_city                     text,<br>
                    cust_state                  text,<br>
                    cust_zip                     varchar(5)<br>
            ) PARTITION BY LIST (cust_last_name);<br>
            <br>
            CREATE TABLE cust_info_5<br>
            PARTITION OF cust_part<br>
            FOR VALUES IN ('NELL');</div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif"><br>
            create table global_addr (l_name text);<br>
            <br>
            insert into global_addr values ('NELL');<br>
            <br>
            INSERT INTO cust_info_5<br>
            ( cust_last_name)<br>
            SELECT l_name from global_addr WHERE l_name = 'NELL';<br>
            <br>
          </div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif">David J.</div>
        </div>
      </div>
    </blockquote>
  </body>
</html>

--------------YQf9kNaDagQWkcb9agJv9UH3--