MIME-Version: 1.0
References: <CAHewXNno_HKiQ6PqyLYfuqDtwp7KKHZiH1J7Pqyz0nr+PS2Dwg@mail.gmail.com>
In-Reply-To: <CAHewXNno_HKiQ6PqyLYfuqDtwp7KKHZiH1J7Pqyz0nr+PS2Dwg@mail.gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Thu, 24 Oct 2024 12:46:23 +0800
Message-ID: <CAHewXN=sGCF7cp3r+8PyZXP0TT+Q1imoEe-7Z-0Sxxb76eWAXg@mail.gmail.com>
Subject: Re: Wrong result when enable_partitionwise_join is on if collation of
 PartitionKey and Column is different.
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Amit Langote <amitlangote09@gmail.com>
Content-Type: multipart/alternative; boundary="000000000000eda422062531afd4"
Archived-At: <https://www.postgresql.org/message-id/CAHewXN%3DsGCF7cp3r%2B8PyZXP0TT%2BQ1imoEe-7Z-0Sxxb76eWAXg%40mail.gmail.com>
Precedence: bulk

--000000000000eda422062531afd4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Tender Wang <tndrwang@gmail.com> =E4=BA=8E2024=E5=B9=B410=E6=9C=8823=E6=97=
=A5=E5=91=A8=E4=B8=89 21:48=E5=86=99=E9=81=93=EF=BC=9A

> Hi all,
>
> I find another issue as $SUBJECT when I work on [1].
>

When I continue to work on this, I find below issue. But I'm not sure
whether it is a bug.

postgres=3D# create table part_index(a text primary key) partition by list =
(
a collate "POSIX");
ERROR:  unique constraint on partitioned table must include all
partitioning columns
DETAIL:  PRIMARY KEY constraint on table "part_index" lacks column "a"
which is part of the partition key.
postgres=3D# create table part_index(a text) partition by list ( a collate
"POSIX");
CREATE TABLE
postgres=3D# alter table part_index add primary key (a);
ERROR:  unique constraint on partitioned table must include all
partitioning columns
DETAIL:  PRIMARY KEY constraint on table "part_index" lacks column "a"
which is part of the partition key.

It seems we can't create a primary key if the collation is different
between columnDef and PartitionKey.

By the way, I think the error message is misleading to users.
ostgres=3D# alter table part_index add primary key (a);
ERROR:  unique constraint on partitioned table must include all
partitioning columns
DETAIL:  PRIMARY KEY constraint on table "part_index" lacks column "a"
which is part of the partition key.


--=20
Thanks,
Tender Wang

--000000000000eda422062531afd4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">Tender Wang &lt;<a href=3D"mailto:tnd=
rwang@gmail.com">tndrwang@gmail.com</a>&gt; =E4=BA=8E2024=E5=B9=B410=E6=9C=
=8823=E6=97=A5=E5=91=A8=E4=B8=89 21:48=E5=86=99=E9=81=93=EF=BC=9A<br></div>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=
=3D"ltr"><div>Hi all,</div><div><br></div><div>I find another issue as $SUB=
JECT when I work on [1].</div></div></div></blockquote><div><br></div><div>=
When I continue to work on this, I find below issue. But I&#39;m not sure w=
hether it is a bug.</div><div><br></div>postgres=3D# create table part_inde=
x(a text primary key) partition by list ( a collate &quot;POSIX&quot;);<br>=
ERROR: =C2=A0unique constraint on partitioned table must include all partit=
ioning columns<br>DETAIL: =C2=A0PRIMARY KEY constraint on table &quot;part_=
index&quot; lacks column &quot;a&quot; which is part of the partition key.<=
/div><div class=3D"gmail_quote">postgres=3D# create table part_index(a text=
) partition by list ( a collate &quot;POSIX&quot;);<br>CREATE TABLE<br>post=
gres=3D# alter table part_index add primary key (a);<br>ERROR: =C2=A0unique=
 constraint on partitioned table must include all partitioning columns<br><=
div>DETAIL: =C2=A0PRIMARY KEY constraint on table &quot;part_index&quot; la=
cks column &quot;a&quot; which is part of the partition key.</div><div><br>=
</div><div>It seems we can&#39;t create a primary key if the collation is d=
ifferent between columnDef and PartitionKey.<br></div><div><br></div><div>B=
y the way, I think the error message is misleading to users.</div><div>ostg=
res=3D# alter table part_index add primary key (a);<br>ERROR: =C2=A0unique =
constraint on partitioned table must include all partitioning columns<br><d=
iv>DETAIL: =C2=A0PRIMARY KEY constraint on table &quot;part_index&quot; lac=
ks column &quot;a&quot; which is part of the partition key.</div></div><div=
><br></div></div><br clear=3D"all"><div><br></div><span class=3D"gmail_sign=
ature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div=
 dir=3D"ltr"><div>Thanks,</div><div>Tender Wang</div></div></div></div>

--000000000000eda422062531afd4--