Re: BUG #19582: Query fails on mixed IPv4 IPv6 data when index added

PG Bug reporting form <noreply(at)postgresql(dot)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