Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. ecpg: Fix memory leaks in ecpg_auto_prepare()

  1. BUG #19000: gist index returns inconsistent result with gist_inet_ops

    PG Bug reporting form <noreply@postgresql.org> — 2025-07-27T05:00:02Z

    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.

  2. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Richard Guo <guofenglinux@gmail.com> — 2025-07-28T02:23:13Z

    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

  3. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Stepan Neretin <slpmcf@gmail.com> — 2025-07-28T06:38:54Z

    On Mon, Jul 28, 2025 at 9:23 AM Richard Guo <guofenglinux@gmail.com> 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.
>
> CC'ing Peter to have a look.
>
> Thanks
> Richard
>
>
>
Hi,

I tried to reproduce this issue on PostgreSQL 18beta2 (commit 3151c264) on
Ubuntu 24.04, but I could not observe the incorrect behavior described.

Here’s what I did:

psql (19devel)
Type "help" for help.

postgres=# CREATE EXTENSION btree_gist;

CREATE TABLE t AS SELECT '192.168.1.0/25'::inet AS i;
CREATE EXTENSION
SELECT 1
postgres=# CREATE INDEX ON t USING gist(i);
CREATE INDEX
postgres=# SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
       i
----------------
 192.168.1.0/25
(1 row)

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.

Best regards,
Stepan Neretin

  4. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Richard Guo <guofenglinux@gmail.com> — 2025-07-28T08:20:45Z

    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 incorrect behavior 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

  5. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Tender Wang <tndrwang@gmail.com> — 2025-07-31T05:30:45Z

    Richard Guo <guofenglinux@gmail.com> 于2025年7月28日周一 10:23写道：

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

Before  be1cc9aaf, because :
      if (opfamily != NETWORK_BTREE_FAM_OID)
             return NIL;
So the planner creates seqscan.

After be1cc9aaf, above if block was removed,  so the planner creates an
index scan, as below:
postgres=# explain SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
                                  QUERY PLAN

-------------------------------------------------------------------------------
 Index Scan using t_i_idx on t  (cost=0.12..8.15 rows=1 width=32)
   Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <=
'192.168.1.255'::inet))
   Filter: (i << '192.168.1.0/24'::inet)
(3 rows)

However, the gistgettuple() function returned NULL, so the above query has
no output.
I created another table t2 and used btree index, its plan was same with t,
as below:
postgres=# explain SELECT * FROM t2 WHERE i << '192.168.1.0/24'::cidr;
                                  QUERY PLAN

-------------------------------------------------------------------------------
 Index Scan using t2_i_idx on t2  (cost=0.12..8.15 rows=1 width=32)
   Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <=
'192.168.1.255'::inet))
   Filter: (i << '192.168.1.0/24'::inet)
(3 rows)

I hacked match_network_sub (), changing is_eq to true, so the plan of t is
as below:

postgres=# explain SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
                                  QUERY PLAN

-------------------------------------------------------------------------------
 Index Scan using t_i_idx on t  (cost=0.12..8.15 rows=1 width=32)
   Index Cond: ((i >= '192.168.1.0/24'::inet) AND (i <=
'192.168.1.255'::inet))
   Filter: (i << '192.168.1.0/24'::inet)
(3 rows)

The above plan will return a tuple.

It seems that gist_inet_ops the index's opfamily does not support
the '<<' operator correctly, as Richard said. Or the Index Cond for the
gist index
is not correct.

 --
Thanks,
Tender Wang

  6. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Peter Eisentraut <peter@eisentraut.org> — 2025-07-31T15:43:18Z

    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.

  7. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-31T18:17:35Z

    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

  8. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Richard Guo <guofenglinux@gmail.com> — 2025-08-01T02:38:31Z

    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

  9. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-01T03:33:28Z

    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

  10. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Tender Wang <tndrwang@gmail.com> — 2025-08-01T04:38:00Z

    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].  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.
>

What I debugged yesterday was, in  gbt_inet_consistent(),

query = convert_network_to_scalar(dquery, INETOID, &failure);
(gdb) p query
$2 = 11822170368
then in gbt_num_consistent(), called tinfo->f_lt(query, key->upper,
flinfo), which is gbt_inetlt()
and
(gdb) p *(double *) b
$5 = 11822170368

a is equal to b, gbt_inet_consistent() returned false, no index tuple found.


-- 
Thanks,
Tender Wang

  11. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-01T04:53:23Z

    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

  12. Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-01T19:04:48Z

    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