Postgresql backend casts inet types to text, breaks IP operations and IPv6 lookups.
|Reported by:||eide||Owned by:||erikr|
|Component:||Database layer (models, ORM)||Version:||master|
|Severity:||Normal||Keywords:||ipv6 postgres inet dceu13|
|Cc:||kostko, mmitar@…, asandroq||Triage Stage:||Accepted|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
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 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.
Change History (22)
comment:1 Changed 7 years ago by morten.brekkevold@…
- Needs documentation unset
- Needs tests unset
- Patch needs improvement unset
comment:4 Changed 7 years ago by russellm
- Resolution set to duplicate
- Status changed from new to closed
comment:5 follow-up: ↓ 6 Changed 7 years ago by bobrobertson
- Resolution duplicate deleted
- Status changed from closed to reopened
- Version changed from 1.0 to 1.1
comment:11 Changed 4 years ago by kostko
- Cc kostko added
comment:18 Changed 3 years ago by erikr
- Owner changed from nobody to erikr
- Status changed from new to assigned
comment:21 Changed 3 years ago by Erik Romijn <eromijn@…>
- Resolution set to fixed
- Status changed from assigned to closed