What to do about the broken btree_gist for inet/cidr?
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t43306psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 27, 2026 at 06:02 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t43306_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t43306_1 && git checkout t43306_1Patchset v1 (message #1) is on t43306_1
Hi,
As discussed in this thread
(/messages/by-id/201010112055.o9BKtZf7011251@wwwmaster.postgresql.org)
btree_gist is broken for the inet and cidr types. The reason is that it
uses convert_network_to_scalar() to make a lossy GiST index, but
convert_network_to_scalar() throws away the netmask which is necessary
to preserve the correct sort order of the keys.
This has been broken for as long as we have had btree_gist indexes over
inet. And personally I am not a fan of PostgreSQL shipping with known
broken features. But it is also not obvious to me what the best way to
fix it is.
To refresh my memory I quickly hacked together a proof of concept patch
which adds a couple of test cases to reproduce the bug plus uses
basically the same implementation of the btree_gist as text and numeric,
which changes to index keys from 64 bit floats to a variable sized
bytea. This means that the indexes are no longer lossy.
Some questions/thoughts:
Is it even worth fixing btree_gist for inet when we already have
inet_ops which can do more things and is not broken. We could just
deprecate and then rip out gist_inet_ops?
If we are going to fix it I cannot see any reasonably sized fix which
does not also corrupt all existing indexes on inet, even those which do
not contain any net masks (those which contain netmasks are already
broken anyway). Do we have some way to handle this kind of breakage nicely?
I see two ways to fix the inet indexes, either do as in my PoC patch and
make the indexes no longer lossy. This will make it more similar to how
btree_gist works for other data types but requires us to change the
storage type of the index keys, but since all indexes will break anyway
that might not be an issue.
The other way is to implement correct lossy indexes, using something
similar to what network_abbrev_convert() does.
Does anyone have any thoughts on this?
Andreas
Andreas Karlsson <andreas@proxel.se> writes:
This has been broken for as long as we have had btree_gist indexes over
inet. And personally I am not a fan of PostgreSQL shipping with known
broken features. But it is also not obvious to me what the best way to
fix it is.
Yeah, this has been lingering unfixed precisely for lack of a clearly-
good fix.
Is it even worth fixing btree_gist for inet when we already have
inet_ops which can do more things and is not broken. We could just
deprecate and then rip out gist_inet_ops?
That might be the thing to do; it doesn't seem very nice to carry
essentially duplicate functionality. However
(1) is performance as good?
(2) gist inet_ops is not marked default, so we'd have to change that.
I don't recall what happens if there are two default opclasses
for the same AM and input type, but it's likely not great.
If we are going to fix it I cannot see any reasonably sized fix which
does not also corrupt all existing indexes on inet, even those which do
not contain any net masks (those which contain netmasks are already
broken anyway). Do we have some way to handle this kind of breakage nicely?
Not really. If the fix involves changing the declared storage type for
an inet column, we might be able to have the support routines check that,
and either error out or continue to do what they used to. But I'm not
sure whether that info is available to all the support routines.
pg_upgrade scenarios might be messy, too.
regards, tom lane