[Django] #11442: Postgresql backend casts inet types to text, breaks IP operations and IPv6 lookups.

[Django]

#11442: Postgresql backend casts inet types to text, breaks IP operations and IPv6
lookups.
------------------------------------------+---------------------------------
Reporter: eide | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: 1.0
Keywords: ipv6 postgres inet | Stage: Unreviewed
Has_patch: 0 |
------------------------------------------+---------------------------------
Ticket #708 describes a problem with LIKE operations on inet types in
postgresql. The solution was to cast inet to text using the HOST()
function.

But by casting inet to text none of the network operations in postgresql
will work, and IPv6 lookups are pretty much broken. In the database I'm
currently using, doing a HOST() on a IPv6 address will always produce a
compressed URL. So if I'm checking against a fullsize address in django
the lookup will fail, even though they are the same address.

Here's an example of what I'm talking about:
{{{
my_db=# CREATE TABLE my_ips (ip inet);
CREATE TABLE
^
my_db=# INSERT INTO my_ips VALUES
('2001:0db8:0000:0000:0000:0000:0000:0001');
INSERT 0 1

my_db=# SELECT * FROM my_ips WHERE ip =
'2001:0db8:0000:0000:0000:0000:0000:0001';
ip
-------------
2001:db8::1
(1 row)

my_db=# SELECT * FROM my_ips WHERE ip = '2001:db8::1';
ip
-------------
2001:db8::1
(1 row)
}}}
So far so good, but when you throw HOST() into the picture, this happens:
{{{
my_db=# SELECT * FROM my_ips WHERE HOST(ip) = '2001:db8::1';
ip
-------------
2001:db8::1
(1 row)

my_db=# SELECT * FROM my_ips WHERE HOST(ip) =
'2001:0db8:0000:0000:0000:0000:0000:0001';
ip
----
(0 rows)
}}}
2001:db8::1 and 2001:0db8:0000:0000:0000:0000:0000:0001 are the same
address, just displayed on different forms.

Currently I always make sure that I pass a compressed IP to the models
with IPAddressFields. That does however assume that all postgresql
databases will always return IPv6 addresses on the compressed form, and I
do not know if that's correct.

The correct solution would be to not cast inet to text.

Also, the postgresql documentation on
[http://www.postgresql.org/docs/8.2/static/functions-net.html Network
Address Functions and Operators] states that:
{{{
The host, text, and abbrev functions are primarily intended to offer
alternative display formats.
}}}
So using HOST() for lookups is acctually kind of wrong in the first place.

--
Ticket URL: <http://code.djangoproject.com/ticket/11442>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#11442: Postgresql backend casts inet types to text, breaks IP operations and IPv6
lookups.

---------------------------------------------------+------------------------

Reporter: eide | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: 1.0

Resolution: | Keywords: ipv6 postgres inet
Stage: Unreviewed | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Changes (by morten.b...@uninett.no):

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0

Comment:

Not only will using HOST yield wrong results, there are also severe
performance implications to using the HOST function call in lookups, as it
fails to utilize indexes on INET type fields. See the following example:

{{{

nav=# select count(*) from arp;
count
---------
6391765
(1 row)

nav=# explain analyze select * from arp where ip = '2001:700::1';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using arp_ip_btree on arp (cost=0.00..905.80 rows=232
width=67) (actual time=0.021..0.021 rows=0 loops=1)
Index Cond: (ip = '2001:700::1'::inet)
Total runtime: 0.051 ms
(3 rows)

nav=# explain analyze select * from arp where HOST(ip) = '2001:700::1';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on arp (cost=0.00..200239.38 rows=32911 width=67) (actual
time=9410.175..9410.175 rows=0 loops=1)
Filter: (host(ip) = '2001:700::1'::text)
Total runtime: 9410.196 ms
(3 rows)

nav=#
}}}

--
Ticket URL: <http://code.djangoproject.com/ticket/11442#comment:1>

[Django]

#11442: Postgresql backend casts inet types to text, breaks IP operations and IPv6
lookups.

---------------------------------------------------+------------------------

Reporter: eide | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: 1.0

Resolution: | Keywords: ipv6 postgres inet
Stage: Unreviewed | Has_patch: 0

Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------

Comment (by kristia...@uninett.no):

Morten: The perfomance issue can be "solved" by adding a host(ip)-index on
the table;

{{{
CREATE INDEX arp_host_ip ON arp (host(ip));
}}}

{{{
klette=# SELECT ip from ips where host(ip) = '2001:700:300:1800::b';
host
----------------------
2001:700:300:1800::b
(1 row)

Time: 1781.635 ms
klette=# CREATE INDEX ips_host_ip_index ON ips ( host(ip));
CREATE INDEX
Time: 31937.661 ms
klette=# SELECT ip from ips where host(ip)::text = '2001:700:300:1800::b';
host
----------------------
2001:700:300:1800::b
(1 row)

Time: 0.805 ms
}}}

Doesn't really solve the bug though, but boost performance at least.

--
Ticket URL: <http://code.djangoproject.com/ticket/11442#comment:2>

[Django]

#11442: Postgresql backend casts inet types to text, breaks IP operations and IPv6
lookups.

---------------------------------------------------+------------------------

Reporter: eide | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: 1.0

Resolution: | Keywords: ipv6 postgres inet
Stage: Accepted | Has_patch: 0

Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------

Changes (by russellm):

* stage: Unreviewed => Accepted

--
Ticket URL: <http://code.djangoproject.com/ticket/11442#comment:3>

[Django]

#11442: Postgresql backend casts inet types to text, breaks IP operations and IPv6
lookups.

---------------------------------------------------+------------------------
Reporter: eide | Owner: nobody
Status: closed | Milestone:

Component: Database layer (models, ORM) | Version: 1.0

Resolution: duplicate | Keywords: ipv6 postgres inet

Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Changes (by russellm):

* status: new => closed
* resolution: => duplicate

Comment:

On second thought - closing as a dupe of #811. IPv6 support is spotty
across the board - if we're going to fix it, it isn't just a Postgres
issue.

--
Ticket URL: <http://code.djangoproject.com/ticket/11442#comment:4>

[Django]

#11442: Postgresql backend casts inet types to text, breaks IP operations and IPv6
lookups.

---------------------------------------------------+------------------------
Reporter: eide | Owner: nobody
Status: reopened | Milestone:
Component: Database layer (models, ORM) | Version: 1.1
Resolution: | Keywords: ipv6 postgres inet

Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------

Changes (by bobrobertson):

* status: closed => reopened
* version: 1.0 => 1.1
* resolution: duplicate =>

Comment:

The resolution assumes this is just an IPv6 problem, and completely
ignores the enormous performance problem introduced by casting every inet
record in the database to a string. This is understandable for LIKE
queries, but it even uses HOST() on exact match queries.

These two queries return the same results. The first is how Django
currently runs this query, and is roughly 2000x slower than the second.
(Yes, I restarted Postgres between tests and flushed the OS buffers, so it
is a fair comparison.)

The difference is performing n inet->string casts vs. performing 1
string->inet cast.[[BR]]
This also fixes the original IPv6 problem in this ticket.

Takes ~30.0 sec:
{{{
SELECT ip from ips where host(ip) = '10.0.0.1'
}}}


Takes ~0.15 sec:
{{{
SELECT ip from ips where ip = inet '10.0.0.1'
}}}

--
Ticket URL: <http://code.djangoproject.com/ticket/11442#comment:5>

[Django]

#11442: Postgresql backend casts inet types to text, breaks IP operations and IPv6
lookups.

---------------------------------------------------+------------------------
Reporter: eide | Owner: nobody

Status: reopened | Milestone:
Component: Database layer (models, ORM) | Version: 1.1
Resolution: | Keywords: ipv6 postgres inet
Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------

Comment (by bobrobertson):

Replying to [comment:5 bobrobertson]:

Excuse my typo.[[BR]]
> Takes ~0.15 sec:
should have been:[[BR]]
> Takes ~0.015 sec:

--
Ticket URL: <http://code.djangoproject.com/ticket/11442#comment:6>

[Django]

#11442: Postgresql backend casts inet types to text, breaks IP operations and IPv6
lookups.

---------------------------------------------------+------------------------
Reporter: eide | Owner: nobody

Status: reopened | Milestone:
Component: Database layer (models, ORM) | Version: 1.1
Resolution: | Keywords: ipv6 postgres inet
Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------

Comment (by dseomn):

This also breaks ordering of IPv4 addresses, making some querysets return
completely incorrect results.

{{{
foo=# select inet '127.0.0.3' < inet '127.0.0.10';
?column?
----------
t
(1 row)

foo=# select '127.0.0.3' < '127.0.0.10';
?column?
----------
f
(1 row)
}}}

--
Ticket URL: <http://code.djangoproject.com/ticket/11442#comment:7>