#22666: (Generic)IPAddressField index never used on PostgreSQL, inconsistent
behavior
-------------------------------+--------------------
Reporter: intgr | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.6
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
This isn't a problem for us currently, but something I noticed in the
queries generated by Django, which I find to be an antifeature: fields
that have GenericIPAddressField(db_index=True) are currently incapable of
actually using the index in filters on PostgreSQL. The indexing does work
on other databases because it's just a char/varchar field.
For example, given model:
{{{#!python
class Ip(models.Model):
ip = models.GenericIPAddressField(db_index=True)
}}}
The index is created as:
{{{#!sql
CREATE INDEX "asd_ip_ip" ON "asd_ip" ("ip");
}}}
And query filter clauses are generated using the `host()` database
function. Since the index was created without the function, it cannot be
used:
{{{
Ip.objects.filter(ip='1.2.3.4')
# SELECT ... FROM "asd_ip" WHERE HOST("asd_ip"."ip") = '1.2.3.4'
Ip.objects.filter(ip__gte='1.2.3.4')
# SELECT ... FROM "asd_ip" WHERE HOST("asd_ip"."ip") >= '1.2.3.4'
}}}
Worse, since host() converts the IP address to text, the `__gte` filter
stops making much sense, it will consider the IP address '255.0.0.0' to be
less than '3.0.0.0'
The index ''can'' be used for natural ordering, but the ordering will be
inconsistent with the above `__gte` example and other greater/less than
operators.
{{{
Ip.objects.order_by('ip')
# SELECT ... FROM "asd_ip" ORDER BY "asd_ip"."ip" ASC
}}}
I understand that this was done to fix #708 -- the ability to use
`__contains=` for IP addresses, but I think the cure is worse than the
disease. In order to support an operation that doesn't make much sense for
IP addresses, the change sacrifices the advantages that PostgreSQL's
native `inet` type provides and makes ordering inconsistent with filter
comparsisons.
----
I think the "correct" way to address this is to revert that fix
(a9b4efc82b23383038fed6da6ba97242aece27c1) and implement it the same way
how `__contains` works for integers. The `::text` cast is universal and
works with all data types in PostgreSQL:
{{{
Ip.objects.filter(pk__contains=123)
# SELECT ... FROM "asd_ip" WHERE "asd_ip"."id"::text LIKE '%123%'
}}}
But this will break for users who are depending on the current `__gte` etc
behavior in PostgreSQL, or expecting it to behave the same way in all
databases.
Another approach is to simply use `char`/`varchar` type (like other
databases) instead of the PostgreSQL-specific `inet`, so it always uses
text-ordering behavior and behaves the same in all databases without any
hackery.
--
Ticket URL: <https://code.djangoproject.com/ticket/22666>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/048.421f7442f6d00dc379384c4515617281%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
