Horton

Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

Tender Wang <tndrwang@gmail.com>

View thread View on postgr.es
From: Tender Wang <tndrwang@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Amit Langote <amitlangote09@gmail.com>
Date: 2024-10-24T04:46:23Z
Lists: pgsql-hackers 
Tender Wang <tndrwang@gmail.com> 于2024年10月23日周三 21:48写道：

> 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=# 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=# create table part_index(a text) partition by list ( a collate
"POSIX");
CREATE TABLE
postgres=# 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=# 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.



-- 
Thanks,
Tender Wang

Commits

  1. Disallow partitionwise join when collations don't match

  2. Disallow partitionwise grouping when collations don't match