From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter@eisentraut.org>
cc: Richard Guo <guofenglinux@gmail.com>, exclusion@gmail.com,
        pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19000: gist index returns inconsistent result with
 gist_inet_ops
In-reply-to: <d570c46d-1e20-4766-93de-c6fcbc18f282@eisentraut.org>
References: <19000-2525470d200672ab@postgresql.org>
 <CAMbWs49vGc45oQ98bhx4dFpXOsimUX9TTnXOXG=i6+X6sAqBRw@mail.gmail.com>
 <d570c46d-1e20-4766-93de-c6fcbc18f282@eisentraut.org>
Comments: In-reply-to Peter Eisentraut <peter@eisentraut.org>
	message dated "Thu, 31 Jul 2025 17:43:18 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2107724.1753985855.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 31 Jul 2025 14:17:35 -0400
Message-ID: <2107725.1753985855@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2107725.1753985855%40sss.pgh.pa.us>
Precedence: bulk

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-414=
3848ec895%40proxel.se