Re: Fix WITHOUT OVERLAPS PKs used for functional grouping

Hi,

On 2026-10-09 18:33:39 +0800, Yilin Zhang wrote:
> At 2026-10-09 12:30:24, "Paul A Jungwirth" <pj(at)illuminatedcomputing(dot)com> wrote:
> >Andres reported that a WITHOUT OVERLAPS primary key is wrongly
> >considered valid for functional grouping.[0] The problem is that the
> >GiST equals operator may not behave the same as the default btree
> >operator, which is what GROUP BY uses. This patch disables functional
> >grouping for WITHOUT OVERLAPS PKs.
> >
> >It would be nice to still prove functional grouping if the GiST
> >equality operator has the same proc as the btree equality operator.
> >I'd be happy to update this submission if people think that is an
> >acceptable backpatch. Otherwise I'll submit it separately for v20.

Given the set of issues here and the fact that all of this is relatively new,
I think going for simpler for now is the right thign.

> >Incidentally, maybe there is another issue?: The only concrete
> >built-in opclass I could find to reproduce the problem was citext. The
> >btree opclass compares insensitively, but there is no citext opclass
> >in btree_gist, so we use the text opclass instead (by binary
> >coercion). Isn't that a problem in general (regardless of WITHOUT
> >OVERLAPS)? It only breaks GROUP BY with a primary key, but it still
> >seems weird to get case-sensitive comparisons on a citext column.
> >Should I send another patch (against v20) adding citext to btree_gist?

I don't think you can just trivially do that, given that they're two different
extensions. We don't want to install citext every time btree_gist is
installed.

> >Is there a deeper fix, where GiST doesn't use the text opclass for
> >citext columns?

> Thanks for your patch.
>
> While reviewing this patch, I found that some SQL queries which used to
> return results now throw errors after applying the patch.

That's what Paul's message above explains: "This patch disables functional
grouping for WITHOUT OVERLAPS PKs.". No?

Greetings,

Andres Freund