agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: gsaviane@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19582: Query fails on mixed IPv4 IPv6 data when index added
Date: Mon, 27 Jul 2026 15:25:02 -0400
Message-ID: <3978758.1785180302@sss.pgh.pa.us> (raw)
In-Reply-To: <19582-b9e0113754eb1d62@postgresql.org>
References: <19582-b9e0113754eb1d62@postgresql.org>

PG Bug reporting form <noreply@postgresql.org> writes:
> The attached SQL code snippet shows how to reproduce the problem. A table
> with generated mixed IPv4 and IPv6 data, when queried filtering by IP family
> fails when a conditional index is being added to the table. I suspect the
> planner pre-executes the query on some samples, applying the index condition
> only partially.

I'm not seeing any particular bug here.  The & operator doesn't work
on mixed address widths:

regression=# select '::1'::inet & '127.0.0.1'::inet;
ERROR:  cannot AND inet values of different sizes

so applying it to a data column that contains mixed widths is
inherently dangerous.

You appear to be hoping that going via the partial index will prevent
applying the operator to IPv6 addresses, but that is not bulletproof
and was never claimed to be.  In particular, once there's enough data
the planner will probably use a bitmap index scan, which is lossy ---
at some point it's going to report something like "these page(s)
contain matching tuples", leaving it to the main executor to scan
all the tuples on those pages.  If any of those are IPv6, kaboom.

A safer answer might be to partition the table between IPv4 and IPv6
addresses.  If the planner can match the query condition to the
partitioning rule, it won't scan the non-matching partition at all.

I wonder if it'd make more sense to have & promote the IPv4 address
to IPv6 and then perform ANDing, rather than failing outright.
I think when these operators were extended to IPv6, it wasn't entirely
clear what the appropriate widening rule was, but surely that's been
resolved by now.

			regards, tom lane

view thread (2+ messages)

Message-ID: <3978758.1785180302@sss.pgh.pa.us>
Permalink:  ../3978758.1785180302@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3978758.1785180302@sss.pgh.pa.us

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, gsaviane@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #19582: Query fails on mixed IPv4 IPv6 data when index added
  In-Reply-To: <3978758.1785180302@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox