On Thu, Oct 24, 2024 at 1:46 PM Tender Wang <tndrwang@gmail.com> wrote: > 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.
Yeah, you don't want to have the PK index and the partitioning logic to not be in sync about the collation rules applied to the individual rows.
> 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.
I think it's kind of similar to the message you get when a GROUP BY column's collation doesn't match the column appearing in the SELECT list:
explain SELECT c collate case_insensitive, count(c) FROM pagg_tab_case_s GROUP BY c collate "C"; ERROR: column "pagg_tab_case_s.c" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: explain SELECT c collate case_insensitive, count(c) FROM pag...
Perhaps it would be more helpful for the error message or hint or detail to mention the actual discrepancy (collation mismatch) that's causing the error.
There might be other instances of such an error and I am not sure it would be worthwhile to find and fix them all.
Thanks for the explanation. We had better focus on the wrong result issue.
I feel that it's hard only to use one struct(for example, X), which just calls equal(X, expr)
can check both the expression match and the collation match.
Maybe we should add another collation match checks in match_clause_to_partition_key(), like
