Обсуждение: BUG #19000: gist index returns inconsistent result with gist_inet_ops

The following bug has been logged on the website:

Bug reference:      19000
Logged by:          Alexander Lakhin
Email address:      exclusion@gmail.com
PostgreSQL version: 18beta2
Operating system:   Ubuntu 24.04
Description:

The following script:
CREATE EXTENSION btree_gist;

CREATE TABLE t AS SELECT '192.168.1.0/25'::inet AS i;

SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
       i
----------------
 192.168.1.0/25

CREATE INDEX ON t USING gist(i);

SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
 i
---
(0 rows)

shows an inconsistency when using the "<<" operator with a gist index.

Reproduced starting from be1cc9aaf.

On Mon, Jul 28, 2025 at 5:16 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
> CREATE EXTENSION btree_gist;
>
> CREATE TABLE t AS SELECT '192.168.1.0/25'::inet AS i;
>
> SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
>        i
> ----------------
>  192.168.1.0/25
>
> CREATE INDEX ON t USING gist(i);
>
> SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
>  i
> ---
> (0 rows)

It seems that with gist_inet_ops the index's opfamily does not support
the '<<' operator correctly.

With inet_ops, the query works correctly.

CC'ing Peter to have a look.

Thanks
Richard

On Mon, Jul 28, 2025 at 3:39 PM Stepan Neretin <slpmcf@gmail.com> wrote:
> I tried to reproduce this issue on PostgreSQL 18beta2 (commit 3151c264) on Ubuntu 24.04, but I could not observe the
incorrectbehavior described. 

> So the query still returns the expected row after the GiST index is created.
> If there are any additional settings or steps required to reproduce, please let me know.

You'll need to ensure that the plan uses an index scan.  Setting
enable_seqscan to off should be sufficient to do so.

Thanks
Richard

On 28.07.25 04:23, Richard Guo wrote:
> On Mon, Jul 28, 2025 at 5:16 AM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> CREATE EXTENSION btree_gist;
>>
>> CREATE TABLE t AS SELECT '192.168.1.0/25'::inet AS i;
>>
>> SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
>>         i
>> ----------------
>>   192.168.1.0/25
>>
>> CREATE INDEX ON t USING gist(i);
>>
>> SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
>>   i
>> ---
>> (0 rows)
> 
> It seems that with gist_inet_ops the index's opfamily does not support
> the '<<' operator correctly.
> 
> With inet_ops, the query works correctly.

The generated index condition is the same for the gist and the btree 
index, namely:

((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))

If I run the query with the lower bound directly, like

SELECT * FROM t WHERE i > '192.168.1.0/24'::inet;

then I also get no result rows for the gist index, but I do get one for 
the btree index.  (The upper bound works correctly in either case.)

This can be reproduced even in PG17.

My mind is a bit boggled about what the actual meaning of the > operator 
should be in this case, but in any case it seems there might be a 
pre-existing discrepancy in the btree_gist module.

Peter Eisentraut <peter@eisentraut.org> writes:
> On 28.07.25 04:23, Richard Guo wrote:
>> It seems that with gist_inet_ops the index's opfamily does not support
>> the '<<' operator correctly.
>> With inet_ops, the query works correctly.

> This can be reproduced even in PG17.
> My mind is a bit boggled about what the actual meaning of the > operator
> should be in this case, but in any case it seems there might be a
> pre-existing discrepancy in the btree_gist module.

We've known about this for, um, decades: btree_gist's support for
inet/cidr is fundamentally broken [1][2].  It's still there
only because nobody's been able to think of a way of removing it
without causing pain for anyone who has indexes like that.
But maybe we should just accept that it's going to cause pain
and remove it.

Actually ... I vaguely recall that we have, or once had, a
kluge in CREATE INDEX that would silently substitute another
opclass name for a user-specified opclass that wasn't there
anymore.  That would help with dump/restore scenarios, although
it'd be pretty disastrous in pg_upgrade.  But we could add a
preflight check to pg_upgrade that looked for gist_inet_ops
indexes and told users to replace them with network_ops indexes.
So maybe there is a way forward?

            regards, tom lane

[1] https://www.postgresql.org/message-id/flat/201010112055.o9BKtZf7011251%40wwwmaster.postgresql.org
[2] https://www.postgresql.org/message-id/flat/7891efc1-8378-2cf2-617b-4143848ec895%40proxel.se

On Fri, Aug 1, 2025 at 3:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> We've known about this for, um, decades: btree_gist's support for
> inet/cidr is fundamentally broken [1][2].  It's still there
> only because nobody's been able to think of a way of removing it
> without causing pain for anyone who has indexes like that.
> But maybe we should just accept that it's going to cause pain
> and remove it.

I think correctness should take priority over avoiding pain.

Thanks
Richard

Richard Guo <guofenglinux@gmail.com> writes:
> On Fri, Aug 1, 2025 at 3:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> We've known about this for, um, decades: btree_gist's support for
>> inet/cidr is fundamentally broken [1][2].  It's still there
>> only because nobody's been able to think of a way of removing it
>> without causing pain for anyone who has indexes like that.
>> But maybe we should just accept that it's going to cause pain
>> and remove it.

> I think correctness should take priority over avoiding pain.

Yeah.  I spent a little time investigating this today, and hope
to have a patch to propose tomorrow.

            regards, tom lane

Tender Wang <tndrwang@gmail.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> 于2025年8月1日周五 11:33写道：
>> Richard Guo <guofenglinux@gmail.com> writes:
>>> On Fri, Aug 1, 2025 at 3:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> We've known about this for, um, decades: btree_gist's support for
>>>> inet/cidr is fundamentally broken [1][2].

> What I debugged yesterday was, in  gbt_inet_consistent(),
>     query = convert_network_to_scalar(dquery, INETOID, &failure);

Right --- as I mentioned in one of the linked threads, the thing that
is fundamentally wrong here is relying on convert_network_to_scalar
in the first place.  That has no charter to deliver exact results,
and it doesn't.  In the present example I think the issue is that
it doesn't consider the netmask at all; but there are many other
cases where it's not an exact representation of inet's sort order.
That's not a big problem for its intended purpose for statistical
estimates, but it's completely fatal for index infrastructure.

            regards, tom lane

I wrote:
> Richard Guo <guofenglinux@gmail.com> writes:
>> I think correctness should take priority over avoiding pain.

> Yeah.  I spent a little time investigating this today, and hope
> to have a patch to propose tomorrow.

Done at
https://www.postgresql.org/message-id/2483812.1754072263%40sss.pgh.pa.us

            regards, tom lane