agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible
6+ messages / 3 participants
[nested] [flat]
* BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible
@ 2026-09-18 23:36 PG Bug reporting form <noreply@postgresql.org>
2026-09-21 06:05 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2026-09-18 23:36 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: kehan5800@gmail.com
The following bug has been logged on the website:
Bug reference: 19700
Logged by: Ke Han
Email address: kehan5800@gmail.com
PostgreSQL version: 18.6
Operating system: ubuntu
Description:
An SP-GiST index on an inet column that holds both IPv4 and IPv6 values
can make IPv6 rows unreachable through the index. The rows are in the
heap and their entries are in the index, but the scan does not descend
to them. No error or warning is produced; the query simply returns
fewer rows than it should. UPDATE and DELETE are affected the same way.
Everything below is self-contained: each block can be pasted into psql
against a freshly created database on a stock server.
Steps to reproduce
------------------
CREATE TABLE m(v inet);
INSERT INTO m SELECT '10.0.0.1/32'::inet
FROM generate_series(1,100);
INSERT INTO m SELECT '0.0.0.0/0'::inet
FROM generate_series(1,100);
INSERT INTO m SELECT '::1'::inet
FROM generate_series(1,100);
CREATE INDEX ON m USING spgist (v);
SET enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT count(*) FROM m WHERE v = '::1';
SELECT count(*) AS with_index FROM m WHERE v = '::1';
SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;
SELECT count(*) AS without_index FROM m WHERE v = '::1';
The enable_* settings are only there to force the two plans so they can
be compared. 300 rows is too small for the planner to choose the index
on its own; a case where it does, with nothing set at all, is further
down.
Output I got
------------
QUERY PLAN
---------------------------------------------
Aggregate
-> Bitmap Heap Scan on m
Recheck Cond: (v = '::1'::inet)
-> Bitmap Index Scan on m_v_idx
Index Cond: (v = '::1'::inet)
with_index
------------
42
without_index
---------------
100
Output I expected
-----------------
Both counts should be 100. The table contains 100 rows with v = '::1',
the predicate is a plain equality on the indexed column, and an index
scan and a sequential scan must agree.
It also happens with nothing set at all
---------------------------------------
With a larger and more realistic population -- the same two IPv4 values,
then 200000 distinct IPv6 addresses -- the planner chooses the index by
itself and no settings are involved:
CREATE TABLE r(id int, v inet);
INSERT INTO r SELECT g, '10.0.0.1/32'::inet
FROM generate_series(1,100) g;
INSERT INTO r SELECT g, '0.0.0.0/0'::inet
FROM generate_series(1,100) g;
INSERT INTO r SELECT g, ('2001:db8::' || to_hex((g>>16)&65535) ||
':' || to_hex(g&65535))::inet
FROM generate_series(1,200000) g;
CREATE INDEX rix ON r USING spgist (v);
ANALYZE r;
RESET ALL;
EXPLAIN (COSTS OFF) SELECT count(*) FROM r WHERE v = '2001:db8::1';
SELECT count(*) FROM r WHERE v = '2001:db8::1';
SELECT EXISTS(SELECT 1 FROM r WHERE v = '2001:db8::1');
UPDATE r SET id = -1 WHERE v = '2001:db8::1';
gives
Aggregate
-> Index Only Scan using rix on r
Index Cond: (v = '2001:db8::1'::inet)
count
-------
0 -- expected 1
exists
--------
f -- expected t
UPDATE 0 -- expected UPDATE 1
and the same three with the index disabled give 1, t and UPDATE 1:
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;
SELECT count(*) FROM r WHERE v = '2001:db8::1';
SELECT EXISTS(SELECT 1 FROM r WHERE v = '2001:db8::1');
Exactly 58 of the 200000 IPv6 rows are affected, and they are the first
58 inserted -- 2001:db8::1 through 2001:db8::3a. An address outside
that set, for example 2001:db8::100, is returned correctly through the
same index. The count of lost rows does not scale with the table: it is
58 with 100 IPv6 rows and 58 with 200000. To list them:
RESET ALL;
CREATE TEMP TABLE viaidx(v inet);
CREATE TEMP TABLE alltrue(v inet);
SET enable_seqscan = off;
INSERT INTO viaidx SELECT v FROM r WHERE v << '2001:db8::/32';
RESET ALL;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;
INSERT INTO alltrue SELECT v FROM r WHERE v << '2001:db8::/32';
RESET ALL;
SELECT (SELECT count(*) FROM viaidx) AS via_index,
(SELECT count(*) FROM alltrue) AS truth;
SELECT min(v), max(v), count(*)
FROM (SELECT v FROM alltrue EXCEPT ALL SELECT v FROM viaidx) x;
via_index | truth
-----------+--------
199942 | 200000
min | max | count
-------------+--------------+-------
2001:db8::1 | 2001:db8::3a | 58
DELETE leaves rows that match its own WHERE clause
--------------------------------------------------
On the 300-row table from the first section, with the index in use:
SET enable_seqscan = off;
DELETE FROM m WHERE v = '::1';
RESET ALL;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;
SELECT count(*) FROM m WHERE v = '::1';
gives
DELETE 42
count
-------
58
58 rows matching the DELETE's own predicate survive it.
Which operators are affected
----------------------------
The DELETE above emptied part of m, so rebuild it first:
DROP TABLE IF EXISTS m CASCADE;
CREATE TABLE m(v inet);
INSERT INTO m SELECT '10.0.0.1/32'::inet
FROM generate_series(1,100);
INSERT INTO m SELECT '0.0.0.0/0'::inet
FROM generate_series(1,100);
INSERT INTO m SELECT '::1'::inet
FROM generate_series(1,100);
CREATE INDEX ON m USING spgist (v);
then:
CREATE OR REPLACE FUNCTION bothways(pred text)
RETURNS TABLE(predicate text, seqscan bigint, spgist bigint)
LANGUAGE plpgsql AS $$
DECLARE s bigint; i bigint;
BEGIN
SET LOCAL enable_seqscan = on;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off;
EXECUTE 'SELECT count(*) FROM m WHERE ' || pred INTO s;
SET LOCAL enable_seqscan = off;
SET LOCAL enable_indexscan = on;
SET LOCAL enable_bitmapscan = on;
SET LOCAL enable_indexonlyscan = on;
EXECUTE 'SELECT count(*) FROM m WHERE ' || pred INTO i;
RETURN QUERY SELECT pred, s, i;
END $$;
SELECT b.predicate, b.seqscan, b.spgist
FROM unnest(ARRAY[
'v = ''::1''', 'v >>= ''::1''', 'v <<= ''::/0''',
'v << ''::/0''', 'v && ''::/0''',
'v = ''10.0.0.1''', 'v <<= ''0.0.0.0/0''']) p,
LATERAL bothways(p) b;
gives
predicate | seqscan | spgist
--------------------+---------+--------
v = '::1' | 100 | 42
v >>= '::1' | 100 | 42
v <<= '::/0' | 100 | 42
v << '::/0' | 100 | 42
v && '::/0' | 100 | 42
v = '10.0.0.1' | 100 | 100
v <<= '0.0.0.0/0' | 200 | 200
IPv4 predicates against the same index are correct. Only the IPv6 side
is lost.
It is a window rather than a floor, and it depends on the data
--------------------------------------------------------------
CREATE OR REPLACE FUNCTION probe(n int,
v4a text DEFAULT '10.0.0.1/32',
v4b text DEFAULT '0.0.0.0/0',
v6first bool DEFAULT false,
am text DEFAULT 'spgist')
RETURNS TABLE(copies int, seqscan bigint, indexed bigint,
idxbytes bigint)
LANGUAGE plpgsql AS $$
DECLARE s bigint; i bigint;
BEGIN
DROP TABLE IF EXISTS t CASCADE;
CREATE TABLE t(v inet);
IF v6first THEN
EXECUTE format('INSERT INTO t SELECT %L::inet
FROM generate_series(1,%s)', '::1', n);
END IF;
EXECUTE format('INSERT INTO t SELECT %L::inet
FROM generate_series(1,%s)', v4a, n);
EXECUTE format('INSERT INTO t SELECT %L::inet
FROM generate_series(1,%s)', v4b, n);
IF NOT v6first THEN
EXECUTE format('INSERT INTO t SELECT %L::inet
FROM generate_series(1,%s)', '::1', n);
END IF;
EXECUTE format('CREATE INDEX tix ON t USING %s (v)', am);
SET LOCAL enable_seqscan = on;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off;
SELECT count(*) INTO s FROM t WHERE v = '::1';
SET LOCAL enable_seqscan = off;
SET LOCAL enable_indexscan = on;
SET LOCAL enable_bitmapscan = on;
SET LOCAL enable_indexonlyscan = on;
SELECT count(*) INTO i FROM t WHERE v = '::1';
RETURN QUERY SELECT n, s, i, pg_relation_size('tix');
END $$;
SELECT p.copies, p.seqscan, p.indexed, p.idxbytes
FROM unnest(ARRAY[81,82,90,144,145,200]) c,
LATERAL probe(c) p;
gives
copies | seqscan | indexed | idxbytes
--------+---------+---------+----------
81 | 81 | 81 | 24576
82 | 82 | 1 | 57344
90 | 90 | 20 | 57344
144 | 144 | 142 | 57344
145 | 145 | 145 | 57344
200 | 200 | 200 | 65536
Wrong for 82 to 144 copies and correct on both sides of that range. The
lower edge coincides with the first leaf page split (3 pages to 7).
Two further conditions:
- Two IPv4 values with no common leading bit are needed. A 0.0.0.0/0
entry is not required.
SELECT v4a, v4b, seqscan, indexed FROM (VALUES
('10.0.0.1','200.0.0.1'), ('1.2.3.4','129.2.3.4'),
('10.0.0.1','10.0.0.2'), ('192.168.0.1','192.168.0.2')
) p(v4a,v4b), LATERAL probe(100, p.v4a, p.v4b);
v4a | v4b | seqscan | indexed
------------+--------------+---------+---------
10.0.0.1 | 200.0.0.1 | 100 | 42
1.2.3.4 | 129.2.3.4 | 100 | 42
10.0.0.1 | 10.0.0.2 | 100 | 100
192.168.0.1| 192.168.0.2 | 100 | 100
- Insertion order matters. If the IPv6 rows go in first the answer is
correct.
SELECT f AS v6_first, seqscan, indexed
FROM (VALUES (true),(false)) o(f),
LATERAL probe(100, '10.0.0.1/32', '0.0.0.0/0', o.f);
v6_first | seqscan | indexed
----------+---------+---------
t | 100 | 100
f | 100 | 42
I mention the window because it makes the symptom confusing in the
field: a table can be correct, become wrong as it grows, and become
correct again, with nothing about the workload changing. A larger test
table is not evidence of absence -- a 50000/50000/100 table is clean.
Other access methods on the same data
-------------------------------------
SELECT a AS access_method, seqscan, indexed
FROM (VALUES ('btree'),('hash'),('brin'),('spgist')) m(a),
LATERAL probe(100, '10.0.0.1/32', '0.0.0.0/0', false, m.a);
access_method | seqscan | indexed
---------------+---------+---------
btree | 100 | 100
hash | 100 | 100
brin | 100 | 100
spgist | 100 | 42
Only SP-GiST is wrong.
Configuration
-------------
Stock. No configuration file changes, no command line options, no
environment variables set. The only settings touched are the enable_*
toggles shown above, used to force the two plans for comparison; the
200000-row case runs after RESET ALL with nothing set.
Nothing was done differently from the standard installation
instructions. Built with:
./configure --prefix=... --enable-debug --enable-cassert
make && make install
No assertion is tripped -- the wrong answer is returned quietly on an
assert-enabled build.
Version
-------
SELECT version();
PostgreSQL 20devel on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
That is git master at commit
7b879c485243e61a0d4cb169717a8e96e9e875c2 (2026-09-19).
Also reproduced on 18.6, built from source, on two independently built
servers.
Platform
--------
Ubuntu 22.04.2 LTS, x86-64, Linux 5.15.0-187-generic, glibc 2.35,
gcc 11.4.0.
Analysis
--------
The following is my reading of the source rather than an observation,
and the facts above do not depend on it.
inet_spg_picksplit() in src/backend/utils/adt/network_spgist.c decides
whether a group of entries spans both address families in the same loop
that computes their common prefix:
/* Examine remaining items to discover minimum common prefix
length */
for (i = 1; i < in->nTuples; i++)
{
tmp = DatumGetInetPP(in->datums[i]);
if (ip_family(tmp) != ip_family(prefix))
{
differentFamilies = true;
break;
}
if (ip_bits(tmp) < commonbits)
commonbits = ip_bits(tmp);
commonbits = bitncommon(ip_addr(prefix), ip_addr(tmp),
commonbits);
if (commonbits == 0)
break;
}
The commonbits == 0 break is correct for the prefix computation -- there
is nothing further to learn about the prefix. But the same loop is what
answers the separate question "is there an entry of the other family in
this group?", and that question is not yet answered when the break
fires. Entries after that point are never examined. If one of them is
IPv6, differentFamilies stays false and the else branch builds a
four-node inner tuple with an IPv4 CIDR prefix, mapping the IPv6 entries
into it through inet_spg_node_number().
That breaks the invariant stated at the top of the same file:
* We split inet index entries first by address family (IPv4 or
* IPv6).
The scan side then does what that invariant licenses. In
inet_spg_consistent_bitmap(), the static helper that both
inet_spg_inner_consistent() (via the prefix, line 293) and
inet_spg_leaf_consistent() (line 338) call:
if (ip_family(argument) != ip_family(prefix))
{
switch (strategy)
{
...
default:
/* For all other cases, we can be sure there is
no match */
bitmap = 0;
so an IPv6 key against an IPv4-prefixed inner tuple prunes the whole
subtree. The consistent functions look correct to me; picksplit is what
breaks their premise.
This also accounts for the conditions above: two IPv4 values with no
common leading bit are what drive commonbits to zero, insertion order
decides whether the family check or the commonbits break fires first,
and a page split is what calls picksplit at all.
A fix would need to separate the two questions. Dropping the
commonbits == 0 break is the smaller change, at the cost of always
scanning one page's worth of entries. Keeping the early exit means
answering the family question in its own pass first. I have not
submitted a patch; which of those is right, and whether the family scan
belongs somewhere cheaper, is a judgement for people who know this code.
If this is fixed, existing SP-GiST indexes on mixed-family inet columns
will need REINDEX, since the misfiled entries are already on disk.
Affected versions
-----------------
The loop above is byte-for-byte identical to the commit that introduced
the opclass -- 77e2906821e2aec3c0807866a84c2934feeac8be, "Create an
SP-GiST opclass for inet/cidr", 2016-08-23, first released in
PostgreSQL 10. git log over network_spgist.c shows no functional change
since: copyright updates, pgindent, header cleanups and the
palloc_array() conversion.
So every version carrying inet_ops for SP-GiST should be affected --
PostgreSQL 10 through master. I have verified 18.6 and master directly
and have not built the other branches.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible
2026-09-18 23:36 BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-21 06:05 ` Kirill Reshke <reshkekirill@gmail.com>
2026-09-21 07:45 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kirill Reshke @ 2026-09-21 06:05 UTC (permalink / raw)
To: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org
On Sat, 19 Sept 2026 at 05:14, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19700
> Logged by: Ke Han
> Email address: kehan5800@gmail.com
> PostgreSQL version: 18.6
> Operating system: ubuntu
> Description:
>
> An SP-GiST index on an inet column that holds both IPv4 and IPv6 values
> can make IPv6 rows unreachable through the index. The rows are in the
> heap and their entries are in the index, but the scan does not descend
> to them. No error or warning is produced; the query simply returns
> fewer rows than it should. UPDATE and DELETE are affected the same way.
>
> Everything below is self-contained: each block can be pasted into psql
> against a freshly created database on a stock server.
>
>
> Steps to reproduce
> ------------------
>
> CREATE TABLE m(v inet);
> INSERT INTO m SELECT '10.0.0.1/32'::inet
> FROM generate_series(1,100);
> INSERT INTO m SELECT '0.0.0.0/0'::inet
> FROM generate_series(1,100);
> INSERT INTO m SELECT '::1'::inet
> FROM generate_series(1,100);
> CREATE INDEX ON m USING spgist (v);
>
> SET enable_seqscan = off;
> EXPLAIN (COSTS OFF) SELECT count(*) FROM m WHERE v = '::1';
> SELECT count(*) AS with_index FROM m WHERE v = '::1';
>
> SET enable_seqscan = on;
> SET enable_indexscan = off;
> SET enable_bitmapscan = off;
> SET enable_indexonlyscan = off;
> SELECT count(*) AS without_index FROM m WHERE v = '::1';
>
> The enable_* settings are only there to force the two plans so they can
> be compared. 300 rows is too small for the planner to choose the index
> on its own; a case where it does, with nothing set at all, is further
> down.
>
>
> Output I got
> ------------
>
> QUERY PLAN
> ---------------------------------------------
> Aggregate
> -> Bitmap Heap Scan on m
> Recheck Cond: (v = '::1'::inet)
> -> Bitmap Index Scan on m_v_idx
> Index Cond: (v = '::1'::inet)
>
> with_index
> ------------
> 42
>
> without_index
> ---------------
> 100
>
>
> Output I expected
> -----------------
>
> Both counts should be 100. The table contains 100 rows with v = '::1',
> the predicate is a plain equality on the indexed column, and an index
> scan and a sequential scan must agree.
>
>
> It also happens with nothing set at all
> ---------------------------------------
>
> With a larger and more realistic population -- the same two IPv4 values,
> then 200000 distinct IPv6 addresses -- the planner chooses the index by
> itself and no settings are involved:
>
> CREATE TABLE r(id int, v inet);
> INSERT INTO r SELECT g, '10.0.0.1/32'::inet
> FROM generate_series(1,100) g;
> INSERT INTO r SELECT g, '0.0.0.0/0'::inet
> FROM generate_series(1,100) g;
> INSERT INTO r SELECT g, ('2001:db8::' || to_hex((g>>16)&65535) ||
> ':' || to_hex(g&65535))::inet
> FROM generate_series(1,200000) g;
> CREATE INDEX rix ON r USING spgist (v);
> ANALYZE r;
>
> RESET ALL;
> EXPLAIN (COSTS OFF) SELECT count(*) FROM r WHERE v = '2001:db8::1';
> SELECT count(*) FROM r WHERE v = '2001:db8::1';
> SELECT EXISTS(SELECT 1 FROM r WHERE v = '2001:db8::1');
> UPDATE r SET id = -1 WHERE v = '2001:db8::1';
>
> gives
>
> Aggregate
> -> Index Only Scan using rix on r
> Index Cond: (v = '2001:db8::1'::inet)
>
> count
> -------
> 0 -- expected 1
>
> exists
> --------
> f -- expected t
>
> UPDATE 0 -- expected UPDATE 1
>
> and the same three with the index disabled give 1, t and UPDATE 1:
>
> SET enable_indexscan = off;
> SET enable_bitmapscan = off;
> SET enable_indexonlyscan = off;
> SELECT count(*) FROM r WHERE v = '2001:db8::1';
> SELECT EXISTS(SELECT 1 FROM r WHERE v = '2001:db8::1');
>
> Exactly 58 of the 200000 IPv6 rows are affected, and they are the first
> 58 inserted -- 2001:db8::1 through 2001:db8::3a. An address outside
> that set, for example 2001:db8::100, is returned correctly through the
> same index. The count of lost rows does not scale with the table: it is
> 58 with 100 IPv6 rows and 58 with 200000. To list them:
>
> RESET ALL;
> CREATE TEMP TABLE viaidx(v inet);
> CREATE TEMP TABLE alltrue(v inet);
>
> SET enable_seqscan = off;
> INSERT INTO viaidx SELECT v FROM r WHERE v << '2001:db8::/32';
>
> RESET ALL;
> SET enable_indexscan = off;
> SET enable_bitmapscan = off;
> SET enable_indexonlyscan = off;
> INSERT INTO alltrue SELECT v FROM r WHERE v << '2001:db8::/32';
>
> RESET ALL;
> SELECT (SELECT count(*) FROM viaidx) AS via_index,
> (SELECT count(*) FROM alltrue) AS truth;
> SELECT min(v), max(v), count(*)
> FROM (SELECT v FROM alltrue EXCEPT ALL SELECT v FROM viaidx) x;
>
> via_index | truth
> -----------+--------
> 199942 | 200000
>
> min | max | count
> -------------+--------------+-------
> 2001:db8::1 | 2001:db8::3a | 58
>
>
> DELETE leaves rows that match its own WHERE clause
> --------------------------------------------------
>
> On the 300-row table from the first section, with the index in use:
>
> SET enable_seqscan = off;
> DELETE FROM m WHERE v = '::1';
>
> RESET ALL;
> SET enable_indexscan = off;
> SET enable_bitmapscan = off;
> SET enable_indexonlyscan = off;
> SELECT count(*) FROM m WHERE v = '::1';
>
> gives
>
> DELETE 42
>
> count
> -------
> 58
>
> 58 rows matching the DELETE's own predicate survive it.
>
>
> Which operators are affected
> ----------------------------
>
> The DELETE above emptied part of m, so rebuild it first:
>
> DROP TABLE IF EXISTS m CASCADE;
> CREATE TABLE m(v inet);
> INSERT INTO m SELECT '10.0.0.1/32'::inet
> FROM generate_series(1,100);
> INSERT INTO m SELECT '0.0.0.0/0'::inet
> FROM generate_series(1,100);
> INSERT INTO m SELECT '::1'::inet
> FROM generate_series(1,100);
> CREATE INDEX ON m USING spgist (v);
>
> then:
>
> CREATE OR REPLACE FUNCTION bothways(pred text)
> RETURNS TABLE(predicate text, seqscan bigint, spgist bigint)
> LANGUAGE plpgsql AS $$
> DECLARE s bigint; i bigint;
> BEGIN
> SET LOCAL enable_seqscan = on;
> SET LOCAL enable_indexscan = off;
> SET LOCAL enable_bitmapscan = off;
> SET LOCAL enable_indexonlyscan = off;
> EXECUTE 'SELECT count(*) FROM m WHERE ' || pred INTO s;
> SET LOCAL enable_seqscan = off;
> SET LOCAL enable_indexscan = on;
> SET LOCAL enable_bitmapscan = on;
> SET LOCAL enable_indexonlyscan = on;
> EXECUTE 'SELECT count(*) FROM m WHERE ' || pred INTO i;
> RETURN QUERY SELECT pred, s, i;
> END $$;
>
> SELECT b.predicate, b.seqscan, b.spgist
> FROM unnest(ARRAY[
> 'v = ''::1''', 'v >>= ''::1''', 'v <<= ''::/0''',
> 'v << ''::/0''', 'v && ''::/0''',
> 'v = ''10.0.0.1''', 'v <<= ''0.0.0.0/0''']) p,
> LATERAL bothways(p) b;
>
> gives
>
> predicate | seqscan | spgist
> --------------------+---------+--------
> v = '::1' | 100 | 42
> v >>= '::1' | 100 | 42
> v <<= '::/0' | 100 | 42
> v << '::/0' | 100 | 42
> v && '::/0' | 100 | 42
> v = '10.0.0.1' | 100 | 100
> v <<= '0.0.0.0/0' | 200 | 200
>
> IPv4 predicates against the same index are correct. Only the IPv6 side
> is lost.
>
>
> It is a window rather than a floor, and it depends on the data
> --------------------------------------------------------------
>
> CREATE OR REPLACE FUNCTION probe(n int,
> v4a text DEFAULT '10.0.0.1/32',
> v4b text DEFAULT '0.0.0.0/0',
> v6first bool DEFAULT false,
> am text DEFAULT 'spgist')
> RETURNS TABLE(copies int, seqscan bigint, indexed bigint,
> idxbytes bigint)
> LANGUAGE plpgsql AS $$
> DECLARE s bigint; i bigint;
> BEGIN
> DROP TABLE IF EXISTS t CASCADE;
> CREATE TABLE t(v inet);
> IF v6first THEN
> EXECUTE format('INSERT INTO t SELECT %L::inet
> FROM generate_series(1,%s)', '::1', n);
> END IF;
> EXECUTE format('INSERT INTO t SELECT %L::inet
> FROM generate_series(1,%s)', v4a, n);
> EXECUTE format('INSERT INTO t SELECT %L::inet
> FROM generate_series(1,%s)', v4b, n);
> IF NOT v6first THEN
> EXECUTE format('INSERT INTO t SELECT %L::inet
> FROM generate_series(1,%s)', '::1', n);
> END IF;
> EXECUTE format('CREATE INDEX tix ON t USING %s (v)', am);
> SET LOCAL enable_seqscan = on;
> SET LOCAL enable_indexscan = off;
> SET LOCAL enable_bitmapscan = off;
> SET LOCAL enable_indexonlyscan = off;
> SELECT count(*) INTO s FROM t WHERE v = '::1';
> SET LOCAL enable_seqscan = off;
> SET LOCAL enable_indexscan = on;
> SET LOCAL enable_bitmapscan = on;
> SET LOCAL enable_indexonlyscan = on;
> SELECT count(*) INTO i FROM t WHERE v = '::1';
> RETURN QUERY SELECT n, s, i, pg_relation_size('tix');
> END $$;
>
> SELECT p.copies, p.seqscan, p.indexed, p.idxbytes
> FROM unnest(ARRAY[81,82,90,144,145,200]) c,
> LATERAL probe(c) p;
>
> gives
>
> copies | seqscan | indexed | idxbytes
> --------+---------+---------+----------
> 81 | 81 | 81 | 24576
> 82 | 82 | 1 | 57344
> 90 | 90 | 20 | 57344
> 144 | 144 | 142 | 57344
> 145 | 145 | 145 | 57344
> 200 | 200 | 200 | 65536
>
> Wrong for 82 to 144 copies and correct on both sides of that range. The
> lower edge coincides with the first leaf page split (3 pages to 7).
>
> Two further conditions:
>
> - Two IPv4 values with no common leading bit are needed. A 0.0.0.0/0
> entry is not required.
>
> SELECT v4a, v4b, seqscan, indexed FROM (VALUES
> ('10.0.0.1','200.0.0.1'), ('1.2.3.4','129.2.3.4'),
> ('10.0.0.1','10.0.0.2'), ('192.168.0.1','192.168.0.2')
> ) p(v4a,v4b), LATERAL probe(100, p.v4a, p.v4b);
>
> v4a | v4b | seqscan | indexed
> ------------+--------------+---------+---------
> 10.0.0.1 | 200.0.0.1 | 100 | 42
> 1.2.3.4 | 129.2.3.4 | 100 | 42
> 10.0.0.1 | 10.0.0.2 | 100 | 100
> 192.168.0.1| 192.168.0.2 | 100 | 100
>
> - Insertion order matters. If the IPv6 rows go in first the answer is
> correct.
>
> SELECT f AS v6_first, seqscan, indexed
> FROM (VALUES (true),(false)) o(f),
> LATERAL probe(100, '10.0.0.1/32', '0.0.0.0/0', o.f);
>
> v6_first | seqscan | indexed
> ----------+---------+---------
> t | 100 | 100
> f | 100 | 42
>
> I mention the window because it makes the symptom confusing in the
> field: a table can be correct, become wrong as it grows, and become
> correct again, with nothing about the workload changing. A larger test
> table is not evidence of absence -- a 50000/50000/100 table is clean.
>
>
> Other access methods on the same data
> -------------------------------------
>
> SELECT a AS access_method, seqscan, indexed
> FROM (VALUES ('btree'),('hash'),('brin'),('spgist')) m(a),
> LATERAL probe(100, '10.0.0.1/32', '0.0.0.0/0', false, m.a);
>
> access_method | seqscan | indexed
> ---------------+---------+---------
> btree | 100 | 100
> hash | 100 | 100
> brin | 100 | 100
> spgist | 100 | 42
>
> Only SP-GiST is wrong.
>
>
> Configuration
> -------------
>
> Stock. No configuration file changes, no command line options, no
> environment variables set. The only settings touched are the enable_*
> toggles shown above, used to force the two plans for comparison; the
> 200000-row case runs after RESET ALL with nothing set.
>
> Nothing was done differently from the standard installation
> instructions. Built with:
>
> ./configure --prefix=... --enable-debug --enable-cassert
> make && make install
>
> No assertion is tripped -- the wrong answer is returned quietly on an
> assert-enabled build.
>
>
> Version
> -------
>
> SELECT version();
>
> PostgreSQL 20devel on x86_64-pc-linux-gnu, compiled by gcc
> (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
>
> That is git master at commit
> 7b879c485243e61a0d4cb169717a8e96e9e875c2 (2026-09-19).
>
> Also reproduced on 18.6, built from source, on two independently built
> servers.
>
>
> Platform
> --------
>
> Ubuntu 22.04.2 LTS, x86-64, Linux 5.15.0-187-generic, glibc 2.35,
> gcc 11.4.0.
>
>
> Analysis
> --------
>
> The following is my reading of the source rather than an observation,
> and the facts above do not depend on it.
>
> inet_spg_picksplit() in src/backend/utils/adt/network_spgist.c decides
> whether a group of entries spans both address families in the same loop
> that computes their common prefix:
>
> /* Examine remaining items to discover minimum common prefix
> length */
> for (i = 1; i < in->nTuples; i++)
> {
> tmp = DatumGetInetPP(in->datums[i]);
>
> if (ip_family(tmp) != ip_family(prefix))
> {
> differentFamilies = true;
> break;
> }
>
> if (ip_bits(tmp) < commonbits)
> commonbits = ip_bits(tmp);
> commonbits = bitncommon(ip_addr(prefix), ip_addr(tmp),
> commonbits);
> if (commonbits == 0)
> break;
> }
>
> The commonbits == 0 break is correct for the prefix computation -- there
> is nothing further to learn about the prefix. But the same loop is what
> answers the separate question "is there an entry of the other family in
> this group?", and that question is not yet answered when the break
> fires. Entries after that point are never examined. If one of them is
> IPv6, differentFamilies stays false and the else branch builds a
> four-node inner tuple with an IPv4 CIDR prefix, mapping the IPv6 entries
> into it through inet_spg_node_number().
>
> That breaks the invariant stated at the top of the same file:
>
> * We split inet index entries first by address family (IPv4 or
> * IPv6).
>
> The scan side then does what that invariant licenses. In
> inet_spg_consistent_bitmap(), the static helper that both
> inet_spg_inner_consistent() (via the prefix, line 293) and
> inet_spg_leaf_consistent() (line 338) call:
>
> if (ip_family(argument) != ip_family(prefix))
> {
> switch (strategy)
> {
> ...
> default:
> /* For all other cases, we can be sure there is
> no match */
> bitmap = 0;
>
> so an IPv6 key against an IPv4-prefixed inner tuple prunes the whole
> subtree. The consistent functions look correct to me; picksplit is what
> breaks their premise.
>
> This also accounts for the conditions above: two IPv4 values with no
> common leading bit are what drive commonbits to zero, insertion order
> decides whether the family check or the commonbits break fires first,
> and a page split is what calls picksplit at all.
>
> A fix would need to separate the two questions. Dropping the
> commonbits == 0 break is the smaller change, at the cost of always
> scanning one page's worth of entries. Keeping the early exit means
> answering the family question in its own pass first. I have not
> submitted a patch; which of those is right, and whether the family scan
> belongs somewhere cheaper, is a judgement for people who know this code.
>
> If this is fixed, existing SP-GiST indexes on mixed-family inet columns
> will need REINDEX, since the misfiled entries are already on disk.
>
>
> Affected versions
> -----------------
>
> The loop above is byte-for-byte identical to the commit that introduced
> the opclass -- 77e2906821e2aec3c0807866a84c2934feeac8be, "Create an
> SP-GiST opclass for inet/cidr", 2016-08-23, first released in
> PostgreSQL 10. git log over network_spgist.c shows no functional change
> since: copyright updates, pgindent, header cleanups and the
> palloc_array() conversion.
>
> So every version carrying inet_ops for SP-GiST should be affected --
> PostgreSQL 10 through master. I have verified 18.6 and master directly
> and have not built the other branches.
>
>
>
>
Hi!
Reproduced this bug. Also I think your analysis is correct.
With this (obvious?) diff issue disappears:
reshke@reshke:~/postgres$ git diff src/backend/utils/adt/network_spgist.c
diff --git a/src/backend/utils/adt/network_spgist.c
b/src/backend/utils/adt/network_spgist.c
index 52e3c666d4f..1cd065f57c4 100644
--- a/src/backend/utils/adt/network_spgist.c
+++ b/src/backend/utils/adt/network_spgist.c
@@ -191,9 +191,8 @@ inet_spg_picksplit(PG_FUNCTION_ARGS)
if (ip_bits(tmp) < commonbits)
commonbits = ip_bits(tmp);
- commonbits = bitncommon(ip_addr(prefix), ip_addr(tmp),
commonbits);
- if (commonbits == 0)
- break;
+ if (commonbits != 0)
+ commonbits = bitncommon(ip_addr(prefix),
ip_addr(tmp), commonbits);
}
/* Don't need labels; allocate output arrays */
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible
2026-09-18 23:36 BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible PG Bug reporting form <noreply@postgresql.org>
2026-09-21 06:05 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
@ 2026-09-21 07:45 ` Kirill Reshke <reshkekirill@gmail.com>
2026-09-21 09:28 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Andrey Borodin <x4mmm@yandex-team.ru>
0 siblings, 1 reply; 6+ messages in thread
From: Kirill Reshke @ 2026-09-21 07:45 UTC (permalink / raw)
To: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org
On Mon, 21 Sept 2026 at 11:05, Kirill Reshke <reshkekirill@gmail.com> wrote:
> With this (obvious?) diff issue disappears:
>
> reshke@reshke:~/postgres$ git diff src/backend/utils/adt/network_spgist.c
> diff --git a/src/backend/utils/adt/network_spgist.c
> b/src/backend/utils/adt/network_spgist.c
> index 52e3c666d4f..1cd065f57c4 100644
> --- a/src/backend/utils/adt/network_spgist.c
> +++ b/src/backend/utils/adt/network_spgist.c
> @@ -191,9 +191,8 @@ inet_spg_picksplit(PG_FUNCTION_ARGS)
>
> if (ip_bits(tmp) < commonbits)
> commonbits = ip_bits(tmp);
> - commonbits = bitncommon(ip_addr(prefix), ip_addr(tmp),
> commonbits);
> - if (commonbits == 0)
> - break;
> + if (commonbits != 0)
> + commonbits = bitncommon(ip_addr(prefix),
> ip_addr(tmp), commonbits);
> }
>
> /* Don't need labels; allocate output arrays */
>
> --
> Best regards,
> Kirill Reshke
This hits assert
/* allTheSame isn't possible for such a tuple */
Assert(!in->allTheSame);
```
Program received signal SIGABRT, Aborted.
0x00007f54fe69eb2c in pthread_kill () from /lib/x86_64-linux-gnu/libc.so.6
(gdb) bt
#0 0x00007f54fe69eb2c in pthread_kill () from /lib/x86_64-linux-gnu/libc.so.6
#1 0x00007f54fe64527e in raise () from /lib/x86_64-linux-gnu/libc.so.6
#2 0x00007f54fe6288ff in abort () from /lib/x86_64-linux-gnu/libc.so.6
#3 0x0000563787e6093f in ExceptionalCondition
(conditionName=conditionName@entry=0x563787ef0ff8 "!in->allTheSame",
fileName=fileName@entry=0x563787ef0fe7 "network_spgist.c",
lineNumber=lineNumber@entry=85) at assert.c:65
#4 0x0000563787db5fb2 in inet_spg_choose (fcinfo=<optimized out>) at
network_spgist.c:85
#5 0x0000563787e6b46e in FunctionCall2Coll
(flinfo=flinfo@entry=0x5637c16b8bb0, collation=<optimized out>,
arg1=arg1@entry=140721627228416, arg2=arg2@entry=140721627228464) at
fmgr.c:1163
#6 0x000056378797be95 in spgdoinsert
(index=index@entry=0x7f54fe996898, state=state@entry=0x7ffc4e9a6af0,
heapPtr=heapPtr@entry=0x5637c16b7908,
datums=datums@entry=0x7ffc4e9a6c80,
isnulls=isnulls@entry=0x7ffc4e9a6c60) at spgdoinsert.c:2188
#7 0x000056378797e087 in spginsert (index=0x7f54fe996898,
values=0x7ffc4e9a6c80, isnull=0x7ffc4e9a6c60, ht_ctid=0x5637c16b7908,
heapRel=<optimized out>, checkUnique=<optimized out>,
indexUnchanged=false, indexInfo=0x5637c16b8470) at spginsert.c:206
#8 0x0000563787ae42cd in ExecInsertIndexTuples
(resultRelInfo=resultRelInfo@entry=0x5637c16b6c30,
estate=estate@entry=0x5637c16b66e0, flags=flags@entry=0,
slot=slot@entry=0x5637c16b78d0,
arbiterIndexes=arbiterIndexes@entry=0x0,
specConflict=specConflict@entry=0x0) at execIndexing.c:449
#9 0x0000563787b1aafd in ExecInsert
(context=context@entry=0x7ffc4e9a6f00,
resultRelInfo=resultRelInfo@entry=0x5637c16b6c30,
slot=slot@entry=0x5637c16b78d0, canSetTag=<optimized out>,
inserted_tuple=inserted_tuple@entry=0x0,
insert_destrel=insert_destrel@entry=0x0) at nodeModifyTable.c:1272
#10 0x0000563787b1c457 in ExecModifyTable (pstate=0x5637c16b6a20) at
nodeModifyTable.c:4712
#11 0x0000563787ae503b in ExecProcNode (node=0x5637c16b6a20) at
../../../src/include/executor/executor.h:327
#12 ExecutePlan (dest=0x5637c176e3f0, direction=<optimized out>,
numberTuples=0, sendTuples=false, operation=CMD_INSERT,
queryDesc=0x5637c16b5af0) at execMain.c:1766
#13 standard_ExecutorRun (queryDesc=0x5637c16b5af0,
direction=<optimized out>, count=0) at execMain.c:377
#14 0x0000563787cf9f7b in ProcessQuery (plan=<optimized out>,
sourceText=0x5637c168c960 "INSERT INTO inet19700 VALUES ('8000::1');",
params=0x0, queryEnv=0x0, dest=0x5637c176e3f0, qc=0x7ffc4e9a7240)
at pquery.c:162
#15 0x0000563787cfabae in PortalRunMulti
(portal=portal@entry=0x5637c17121f0, isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=false,
dest=dest@entry=0x5637c176e3f0,
altdest=altdest@entry=0x5637c176e3f0, qc=qc@entry=0x7ffc4e9a7240)
at pquery.c:1269
#16 0x0000563787cfafd2 in PortalRun
(portal=portal@entry=0x5637c17121f0,
count=count@entry=9223372036854775807,
isTopLevel=isTopLevel@entry=true, dest=dest@entry=0x5637c176e3f0,
altdest=altdest@entry=0x5637c176e3f0, qc=qc@entry=0x7ffc4e9a7240)
at pquery.c:784
#17 0x0000563787cf6b1d in exec_simple_query
(query_string=0x5637c168c960 "INSERT INTO inet19700 VALUES
('8000::1');") at postgres.c:1297
#18 0x0000563787cf87c6 in PostgresMain (dbname=<optimized out>,
username=<optimized out>) at postgres.c:4946
#19 0x0000563787cf2673 in BackendMain (startup_data=<optimized out>,
startup_data_len=<optimized out>) at backend_startup.c:124
#20 0x0000563787c30021 in postmaster_child_launch
(child_type=<optimized out>, child_slot=2,
startup_data=startup_data@entry=0x7ffc4e9a7710,
startup_data_len=startup_data_len@entry=24,
client_sock=client_sock@entry=0x7ffc4e9a7730) at launch_backend.c:268
#21 0x0000563787c33cb2 in BackendStartup (client_sock=0x7ffc4e9a7730)
at postmaster.c:3640
#22 ServerLoop () at postmaster.c:1727
#23 0x0000563787c358ad in PostmasterMain (argc=argc@entry=3,
argv=argv@entry=0x5637c16861b0) at postmaster.c:1414
#24 0x00005637878d850e in main (argc=3, argv=0x5637c16861b0) at main.c:227
(gdb)
```
repro is alike [0]
CREATE TABLE inet19700 (c inet);
CREATE INDEX inet19700_idx ON inet19700 USING spgist (c inet_ops);
INSERT INTO inet19700 SELECT '10.0.0.1' FROM generate_series(1, 290);
-- TRAP: FailedAssertion("!in->allTheSame")
INSERT INTO inet19700 VALUES ('8000::1');
So, I updated inet_spg_choose to support the 'allTheSame' case. While
inserting, simply redirect to the first node.
inet_spg_inner_consistent needs the same fix (otherwise index scans
would hit the same assertion.)
[0] https://www.postgresql.org/message-id/flat/537BE1A9.1050006@sigaev.ru
--
Best regards,
Kirill Reshke
Attachments:
[application/octet-stream] v1-0001-Fix-different-IP-addr-families-in-SP-Gist-opclass.patch (1.8K, ../../CALdSSPiNRiL8CLxPTmg4u+_8k8MKwfRWxg=Hjk934Y1jYcifFA@mail.gmail.com/2-v1-0001-Fix-different-IP-addr-families-in-SP-Gist-opclass.patch)
download | inline diff:
From 94bbc475fc5281cc048c08620fc8133bc17e5d28 Mon Sep 17 00:00:00 2001
From: reshke <reshke@double.cloud>
Date: Mon, 21 Sep 2026 10:44:47 +0300
Subject: [PATCH v1] Fix different IP addr families in SP-Gist opclass
---
src/backend/utils/adt/network_spgist.c | 27 +++++++++++++++++++-------
1 file changed, 20 insertions(+), 7 deletions(-)
diff --git a/src/backend/utils/adt/network_spgist.c b/src/backend/utils/adt/network_spgist.c
index 52e3c666d4f..1edf90dc2f2 100644
--- a/src/backend/utils/adt/network_spgist.c
+++ b/src/backend/utils/adt/network_spgist.c
@@ -81,8 +81,19 @@ inet_spg_choose(PG_FUNCTION_ARGS)
*/
if (!in->hasPrefix)
{
- /* allTheSame isn't possible for such a tuple */
- Assert(!in->allTheSame);
+ /*
+ * We are forced allTheSame mode here if picksplit put all entries
+ * in one node
+ */
+ if (in->allTheSame)
+ {
+ out->resultType = spgMatchNode;
+ out->result.matchNode.nodeN = 0 /* Doesn't matter, will bee overwritten */;
+ out->result.matchNode.restDatum = InetPGetDatum(val);
+
+ PG_RETURN_VOID();
+ }
+
Assert(in->nNodes == 2);
out->resultType = spgMatchNode;
@@ -191,9 +202,8 @@ inet_spg_picksplit(PG_FUNCTION_ARGS)
if (ip_bits(tmp) < commonbits)
commonbits = ip_bits(tmp);
- commonbits = bitncommon(ip_addr(prefix), ip_addr(tmp), commonbits);
- if (commonbits == 0)
- break;
+ if (commonbits != 0)
+ commonbits = bitncommon(ip_addr(prefix), ip_addr(tmp), commonbits);
}
/* Don't need labels; allocate output arrays */
@@ -245,9 +255,12 @@ inet_spg_inner_consistent(PG_FUNCTION_ARGS)
int i;
int which;
- if (!in->hasPrefix)
+ if (!in->hasPrefix && in->allTheSame)
+ {
+ /* Recurse in all subtrees. */
+ which = ~0;
+ } else if (!in->hasPrefix)
{
- Assert(!in->allTheSame);
Assert(in->nNodes == 2);
/* Identify which child nodes need to be visited */
--
2.43.0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible
2026-09-18 23:36 BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible PG Bug reporting form <noreply@postgresql.org>
2026-09-21 06:05 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
2026-09-21 07:45 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
@ 2026-09-21 09:28 ` Andrey Borodin <x4mmm@yandex-team.ru>
2026-09-21 18:54 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrey Borodin @ 2026-09-21 09:28 UTC (permalink / raw)
To: Kirill Reshke <reshkekirill@gmail.com>; +Cc: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org
On 21 Sep 2026, Kirill Reshke wrote:
> So, I updated inet_spg_choose to support the 'allTheSame' case.
The code changes look correct. Could the new comment explain that
checkAllTheSame() can exclude the incoming tuple? Here picksplit did
separate the families, but the remaining old tuples all went to one
node. The file header also needs an exception to its claim that a
prefixless tuple has exactly two family-specific nodes.
Could we add regression coverage for both the original missing-rows case
and this insertion case, checking searches for both families afterwards?
The latter is a separate bug and should fail even with just the
picksplit fix applied. It would be useful to cover IPv4 arriving after
IPv6 duplicates too.
In inner_consistent, checking allTheSame first would let both cases use
the existing visit-all-nodes branch.
I'd also suggest to add the reporter's REINDEX warning into the commit
message. And few words of what is going on would be helpful too.
Thank you!
Best regards, Andrey Borodin.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible
2026-09-18 23:36 BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible PG Bug reporting form <noreply@postgresql.org>
2026-09-21 06:05 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
2026-09-21 07:45 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
2026-09-21 09:28 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Andrey Borodin <x4mmm@yandex-team.ru>
@ 2026-09-21 18:54 ` Kirill Reshke <reshkekirill@gmail.com>
2026-09-22 06:15 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Andrey Borodin <x4mmm@yandex-team.ru>
0 siblings, 1 reply; 6+ messages in thread
From: Kirill Reshke @ 2026-09-21 18:54 UTC (permalink / raw)
To: Andrey Borodin <x4mmm@yandex-team.ru>; +Cc: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org
On Mon, 21 Sept 2026 at 14:29, Andrey Borodin <x4mmm@yandex-team.ru> wrote:
>
> On 21 Sep 2026, Kirill Reshke wrote:
> > So, I updated inet_spg_choose to support the 'allTheSame' case.
>
> The code changes look correct. Could the new comment explain that
> checkAllTheSame() can exclude the incoming tuple? Here picksplit did
> separate the families, but the remaining old tuples all went to one
> node. The file header also needs an exception to its claim that a
> prefixless tuple has exactly two family-specific nodes.
> Could we add regression coverage for both the original missing-rows case
> and this insertion case, checking searches for both families afterwards?
> The latter is a separate bug and should fail even with just the
> picksplit fix applied. It would be useful to cover IPv4 arriving after
> IPv6 duplicates too.
Added regression test. It exercises indexscan part of issue. another
test that is useful here is when we split allTheSame page with
different family inet class:
+CREATE TABLE inet_tbl_allthesame (i inet);
+CREATE INDEX inet_idx_allthesame ON inet_tbl_allthesame USING spgist (i);
+INSERT INTO inet_tbl_allthesame SELECT '10.0.0.1' FROM generate_series(1, 290);
+INSERT INTO inet_tbl_allthesame VALUES ('8000::1');
+SELECT count(*) FROM inet_tbl_allthesame WHERE i = '10.0.0.1';
+SELECT count(*) FROM inet_tbl_allthesame WHERE i = '8000::1';
+DROP TABLE inet_tbl_allthesame;
But this is dependent on page size and does not actually check that
things go bad or not. So I did not include this in v2.
> In inner_consistent, checking allTheSame first would let both cases use
> the existing visit-all-nodes branch.
>
> I'd also suggest to add the reporter's REINDEX warning into the commit
> message. And few words of what is going on would be helpful too.
ok
--
Best regards,
Kirill Reshke
Attachments:
[application/octet-stream] v3-0001-Fix-SP-GiST-inet-opclass-for-mixed-IP-address-fam.patch (5.2K, ../../CALdSSPjFx8vq-EvweUa57V1wpY39SnhLrLvQcX02OJEU7pM_nw@mail.gmail.com/2-v3-0001-Fix-SP-GiST-inet-opclass-for-mixed-IP-address-fam.patch)
download | inline diff:
From 1c0427b9e83944e5afea92ef8774b38884f52acc Mon Sep 17 00:00:00 2001
From: reshke <reshke@double.cloud>
Date: Mon, 21 Sep 2026 10:44:47 +0300
Subject: [PATCH v3] Fix SP-GiST inet opclass for mixed IP address families
inet_spg_picksplit() used to lose an entry of the other address family,
so IPv6 entries were placed into IPv4-prefixed inner tuples which is a
corruption. Fix by following the same conventions as other SP-GiST opclasses.
Existing mixed-family indexes need a REINDEX after updating.
Reported-by: Ke Han
Bug: #19700
---
src/backend/utils/adt/network_spgist.c | 39 +++++++++++++++++---------
src/test/regress/expected/inet.out | 18 ++++++++++++
src/test/regress/sql/inet.sql | 9 ++++++
3 files changed, 53 insertions(+), 13 deletions(-)
diff --git a/src/backend/utils/adt/network_spgist.c b/src/backend/utils/adt/network_spgist.c
index 52e3c666d4f..a467b2f29d8 100644
--- a/src/backend/utils/adt/network_spgist.c
+++ b/src/backend/utils/adt/network_spgist.c
@@ -10,6 +10,10 @@
*
* An inner tuple that has both IPv4 and IPv6 children has a null prefix
* and exactly two nodes, the first being for IPv4 and the second for IPv6.
+ * There is one exception: allTheSame tuples, which the SP-GiST core can
+ * force here (see checkAllTheSame() in spgdoinsert.c) even though the
+ * entries span both families, in which case the nodes are duplicates of
+ * each other rather than family-specific.
*
* Otherwise, the prefix is a CIDR value representing the common prefix,
* and there are exactly four nodes. Node numbers 0 and 1 are for addresses
@@ -81,8 +85,19 @@ inet_spg_choose(PG_FUNCTION_ARGS)
*/
if (!in->hasPrefix)
{
- /* allTheSame isn't possible for such a tuple */
- Assert(!in->allTheSame);
+ /*
+ * The core can force allTheSame mode on this tuple (see
+ * checkAllTheSame() in spgdoinsert.c).
+ */
+ if (in->allTheSame)
+ {
+ out->resultType = spgMatchNode;
+ out->result.matchNode.nodeN = 0 /* Doesn't matter, will bee overwritten */;
+ out->result.matchNode.restDatum = InetPGetDatum(val);
+
+ PG_RETURN_VOID();
+ }
+
Assert(in->nNodes == 2);
out->resultType = spgMatchNode;
@@ -191,9 +206,8 @@ inet_spg_picksplit(PG_FUNCTION_ARGS)
if (ip_bits(tmp) < commonbits)
commonbits = ip_bits(tmp);
- commonbits = bitncommon(ip_addr(prefix), ip_addr(tmp), commonbits);
- if (commonbits == 0)
- break;
+ if (commonbits != 0)
+ commonbits = bitncommon(ip_addr(prefix), ip_addr(tmp), commonbits);
}
/* Don't need labels; allocate output arrays */
@@ -245,9 +259,13 @@ inet_spg_inner_consistent(PG_FUNCTION_ARGS)
int i;
int which;
- if (!in->hasPrefix)
+ if (in->allTheSame)
+ {
+ /* Must visit all nodes; we assume there are less than 32 of 'em */
+ which = ~0;
+ }
+ else if (!in->hasPrefix)
{
- Assert(!in->allTheSame);
Assert(in->nNodes == 2);
/* Identify which child nodes need to be visited */
@@ -285,7 +303,7 @@ inet_spg_inner_consistent(PG_FUNCTION_ARGS)
}
}
}
- else if (!in->allTheSame)
+ else
{
Assert(in->nNodes == 4);
@@ -293,11 +311,6 @@ inet_spg_inner_consistent(PG_FUNCTION_ARGS)
which = inet_spg_consistent_bitmap(DatumGetInetPP(in->prefixDatum),
in->nkeys, in->scankeys, false);
}
- else
- {
- /* Must visit all nodes; we assume there are less than 32 of 'em */
- which = ~0;
- }
out->nNodes = 0;
diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index 1705bff4dd3..51b031d51bf 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -709,6 +709,24 @@ SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
192.168.1.226
(3 rows)
+CREATE TABLE inet_tbl_mixedfamily (i inet);
+INSERT INTO inet_tbl_mixedfamily SELECT '10.0.0.1/32' FROM generate_series(1, 100);
+INSERT INTO inet_tbl_mixedfamily SELECT '0.0.0.0/0' FROM generate_series(1, 100);
+INSERT INTO inet_tbl_mixedfamily SELECT '::1' FROM generate_series(1, 100);
+CREATE INDEX inet_idx_mixedfamily ON inet_tbl_mixedfamily USING spgist (i);
+SELECT count(*) FROM inet_tbl_mixedfamily WHERE i = '::1';
+ count
+-------
+ 100
+(1 row)
+
+SELECT count(*) FROM inet_tbl_mixedfamily WHERE i = '10.0.0.1';
+ count
+-------
+ 100
+(1 row)
+
+DROP TABLE inet_tbl_mixedfamily;
SET enable_seqscan TO on;
DROP INDEX inet_idx3;
-- simple tests of inet boolean and arithmetic operators
diff --git a/src/test/regress/sql/inet.sql b/src/test/regress/sql/inet.sql
index 8f276856df9..6b6a852e794 100644
--- a/src/test/regress/sql/inet.sql
+++ b/src/test/regress/sql/inet.sql
@@ -135,6 +135,15 @@ EXPLAIN (COSTS OFF)
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i;
+CREATE TABLE inet_tbl_mixedfamily (i inet);
+INSERT INTO inet_tbl_mixedfamily SELECT '10.0.0.1/32' FROM generate_series(1, 100);
+INSERT INTO inet_tbl_mixedfamily SELECT '0.0.0.0/0' FROM generate_series(1, 100);
+INSERT INTO inet_tbl_mixedfamily SELECT '::1' FROM generate_series(1, 100);
+CREATE INDEX inet_idx_mixedfamily ON inet_tbl_mixedfamily USING spgist (i);
+SELECT count(*) FROM inet_tbl_mixedfamily WHERE i = '::1';
+SELECT count(*) FROM inet_tbl_mixedfamily WHERE i = '10.0.0.1';
+DROP TABLE inet_tbl_mixedfamily;
+
SET enable_seqscan TO on;
DROP INDEX inet_idx3;
--
2.43.0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible
2026-09-18 23:36 BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible PG Bug reporting form <noreply@postgresql.org>
2026-09-21 06:05 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
2026-09-21 07:45 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
2026-09-21 09:28 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Andrey Borodin <x4mmm@yandex-team.ru>
2026-09-21 18:54 ` Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible Kirill Reshke <reshkekirill@gmail.com>
@ 2026-09-22 06:15 ` Andrey Borodin <x4mmm@yandex-team.ru>
0 siblings, 0 replies; 6+ messages in thread
From: Andrey Borodin @ 2026-09-22 06:15 UTC (permalink / raw)
To: Kirill Reshke <reshkekirill@gmail.com>; +Cc: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org
On 21 Sep 2026, Kirill Reshke wrote:
> But this is dependent on page size and does not actually check that
> things go bad or not. So I did not include this in v2.
I would keep the allTheSame test. There is a direct precedent in
btree_index.sql [0], which explicitly says that a test only provides
useful coverage with the default 8K BLCKSZ.
The recent GIN incomplete-split test [1] also needs a particular physical
layout, but compares index and sequential scan results rather than
hard-coding a layout-dependent row count.
I'd be more concerned about platform-dependent failures on the buildfarm
than about losing coverage with a different page layout. Here the
expected counts are always 290 and 1, regardless of alignment or where
the split happens. Could we verify that it fails with only the picksplit
fix applied, and passes with the complete fix?
Thank you!
Best regards, Andrey Borodin.
[0] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ec986020decff322723cf7b3a2696803d...
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f20c4278342f6afc44b856e98a0850f9d...
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-09-22 06:15 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 23:36 BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible PG Bug reporting form <noreply@postgresql.org>
2026-09-21 06:05 ` Kirill Reshke <reshkekirill@gmail.com>
2026-09-21 07:45 ` Kirill Reshke <reshkekirill@gmail.com>
2026-09-21 09:28 ` Andrey Borodin <x4mmm@yandex-team.ru>
2026-09-21 18:54 ` Kirill Reshke <reshkekirill@gmail.com>
2026-09-22 06:15 ` Andrey Borodin <x4mmm@yandex-team.ru>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox