Fix WITHOUT OVERLAPS PKs used for functional grouping

At 2026-10-09 12:30:24, "Paul A Jungwirth" <pj@illuminatedcomputing.com> wrote:

Hi Hackers,

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.

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?
Is there a deeper fix, where GiST doesn't use the text opclass for
citext columns?

Hi，
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.

Example:
create table n1 (id int4range, valid_at int4range, title text not null,
primary key (id, valid_at without overlaps));
insert into n1 values ('[1,5)', '[2000,2010)', 'a');
insert into n1 values ('[1,5)', '[2011,2015)', 'b');
select * from n1 order by valid_at;
select id, valid_at, title from n1 group by id, valid_at;

Result:
id | valid_at | title
-------+-------------+-------
[1,5) | [2000,2010) | a
[1,5) | [2011,2015) | b
(2 rows)

Should this case be handled more precisely so that the query results are preserved?

Best regards,
Yilin Zhang

Hi,

On 2026-10-08 21:30:24 -0700, Paul A Jungwirth 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.

Without commenting about anything else, can you please not start separate
threads for all these? Hackers is too busy to be able to keep up with
everything, no need to make it even worse.

Greetings,

Andres Freund

Hi,

On 2026-10-09 18:33:39 +0800, Yilin Zhang wrote:

At 2026-10-09 12:30:24, "Paul A Jungwirth" <pj@illuminatedcomputing.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

On Fri, Oct 9, 2026 at 5:10 AM Andres Freund <andres@anarazel.de> wrote:

Without commenting about anything else, can you please not start separate
threads for all these? Hackers is too busy to be able to keep up with
everything, no need to make it even worse.

I apologize. I'm happy to do whatever makes things easier for you. I'm
a little surprised by your request though. The six bugs you reported
don't have much in common. (I did merge the two replication bugs
though, since they had the same underlying cause.) Earlier in this
cycle we had one FOR PORTION OF thread with several concurrent patches
being discussed, and it was very confusing. And one of the new threads
already has eleven messages.[0]/messages/by-id/CA+renyWzTBC4GetPzJM1KqrejrmOWcGnn2NaA7tpee17HFxRPw@mail.gmail.com Anyway I think it is too late to smash
them all together again, but if I get another batch of bugs I'll check
first before I separate them.

[0]: /messages/by-id/CA+renyWzTBC4GetPzJM1KqrejrmOWcGnn2NaA7tpee17HFxRPw@mail.gmail.com

Yours,

--
Paul ~{:-)
pj@illuminatedcomputing.com

On 2026-Oct-09, Andres Freund wrote:

Without commenting about anything else, can you please not start separate
threads for all these? Hackers is too busy to be able to keep up with
everything, no need to make it even worse.

I'm not sure about this ... if we want these patches to have commitfest
entries (which sounds to me like they should, otherwise we tend to
forget them), I think it's better to have separate threads. This is
especially true if the proposed fixes are likely to spawn discussion.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
Maybe there's lots of data loss but the records of data loss are also lost.
(Lincoln Yeoh)