public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tender Wang <[email protected]>
To: Amit Langote <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.
Date: Thu, 24 Oct 2024 15:01:07 +0800
Message-ID: <CAHewXNk1K2G56C7kXvuYzQexeQs2EuHA3x-7v68ihn843svEZQ@mail.gmail.com> (raw)
In-Reply-To: <CA+HiwqFmtOjVX0Gy2HCMGDRJg3zKNmhdt=o=K=pTvE2VhEe-1A@mail.gmail.com>
References: <CAHewXNno_HKiQ6PqyLYfuqDtwp7KKHZiH1J7Pqyz0nr+PS2Dwg@mail.gmail.com>
	<CAHewXN=sGCF7cp3r+8PyZXP0TT+Q1imoEe-7Z-0Sxxb76eWAXg@mail.gmail.com>
	<CA+HiwqFmtOjVX0Gy2HCMGDRJg3zKNmhdt=o=K=pTvE2VhEe-1A@mail.gmail.com>

Amit Langote <[email protected]> 于2024年10月24日周四 14:33写道：

> Hi,
>
> On Thu, Oct 24, 2024 at 1:46 PM Tender Wang <[email protected]> wrote:
> > Tender Wang <[email protected]> 于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
partition pruning logic does.

Any thoughts?

-- 
Thanks,
Tender Wang

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.
  In-Reply-To: <CAHewXNk1K2G56C7kXvuYzQexeQs2EuHA3x-7v68ihn843svEZQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox