PostgreSQL 8.3 cannot do text matching on uncast non-text columns

[pat.j.anderson@…]

I have just created my few models and connected my django apps to PostgreSQL 8.3 beta2 on my Fedora 8 server. When I try to create a new record I receive this error:

Environment:

Request Method: POST
Request URL: http://localhost:8001/admin/publications/publication/add/
Django Version: 0.97-pre-SVN-7047
Python Version: 2.5.1
Installed Applications:
['django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'activemembers.apps.publications',
 'activemembers.apps.world',
 'django.contrib.sites',
 'django.contrib.markup',
 'django.contrib.humanize',
 'django.contrib.admin',
 'tagging',
 'template_utils',
]
Installed Middleware:
('django.middleware.cache.CacheMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.locale.LocaleMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.middleware.doc.XViewMiddleware',
 'django.middleware.gzip.GZipMiddleware'
)


Traceback:
File "/usr/lib/python2.5/site-packages/django/core/handlers/base.py" in get_response
  82.                 response = callback(request, *callback_args, **callback_kwargs)
File "/usr/lib/python2.5/site-packages/django/contrib/admin/views/decorators.py" in _checklogin
  56.             return view_func(request, *args, **kwargs)
File "/usr/lib/python2.5/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  39.         response = view_func(request, *args, **kwargs)
File "/usr/lib/python2.5/site-packages/django/contrib/admin/views/main.py" in add_stage
  260.         errors = manipulator.get_validation_errors(new_data)
File "/usr/lib/python2.5/site-packages/django/oldforms/__init__.py" in get_validation_errors
  62.             errors.update(field.get_validation_errors(new_data))
File "/usr/lib/python2.5/site-packages/django/oldforms/__init__.py" in get_validation_errors
  379.                     self.run_validator(new_data, validator)
File "/usr/lib/python2.5/site-packages/django/oldforms/__init__.py" in run_validator
  369.                 validator(new_data.get(self.field_name, ''), new_data)
File "/usr/lib/python2.5/site-packages/django/utils/functional.py" in _curried
  3.         return _curried_func(*(args+moreargs), **dict(kwargs, **morekwargs))
File "/usr/lib/python2.5/site-packages/django/db/models/manipulators.py" in manipulator_validator_unique_together
  302.         old_obj = self.manager.get(**kwargs)
File "/usr/lib/python2.5/site-packages/django/db/models/manager.py" in get
  69.         return self.get_query_set().get(*args, **kwargs)
File "/usr/lib/python2.5/site-packages/django/db/models/query.py" in get
  261.         obj_list = list(clone)
File "/usr/lib/python2.5/site-packages/django/db/models/query.py" in __iter__
  114.         return iter(self._get_data())
File "/usr/lib/python2.5/site-packages/django/db/models/query.py" in _get_data
  483.             self._result_cache = list(self.iterator())
File "/usr/lib/python2.5/site-packages/django/db/models/query.py" in iterator
  189.         cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params)
File "/usr/lib/python2.5/site-packages/django/db/backends/util.py" in execute
  18.             return self.cursor.execute(sql, params)

Exception Type: ProgrammingError at /admin/publications/publication/add/
Exception Value: operator does not exist: integer ~~* unknown
LINE 1: ....1' AND "publications_publication__language"."id" ILIKE '3')
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I think this is related to ticket:6210. I have a foreign key relation to Language in my Publication model and it is combined with another field in my Publication model in 'unique_together' attribute in its Meta class.

I'm posting the traceback here, because this is the best way I can describe the problem. The person who closed ticket:6210, might have had a reason for it, but I wonder if it is worth looking into.

I'd like to use PostgreSQL with Django, I really do, but this kind of error scares me and I think I'd have to live with MySQL, which worked.

[pat.j.anderson@…]

I can confirm that this most likely has something to do with unique_together Meta class attribute and a ForeignKey being one of the fields present. When I removed it from Meta class, I was able to create records successfully using the Admin interface.

As a side note, when I was adding records to that table before outside of Admin, using my own forms, I didn't experience this problem.

[gervystar@…]

I can confirm this behaviour using django-trunk on postgres-8.3 on Ubuntu Hardy.
It works using pg 8.2

[Jason Davies]

Confirmed using PostgreSQL 8.3 too, it seems to be a problem with unique_together.

[Jason Davies]

I imagine this can be solved by adding an explicit cast to text. See ​http://www.postgresql.org/docs/8.3/static/release-8-3.html#AEN85667

[anonymous]

Adding redirects using the (old) admin site no longer works after upgrading to PosgreSQL 8.3, similar error message:

ERROR:  operator does not exist: integer ~~* unknown at character 290
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT:  SELECT "django_redirect"."id","django_redirect"."site_id","django_redirect"."old_path","django_redirect"."new_path" FROM "django_redirect"
INNER JOIN "django_site" AS "django_redirect__site" ON "django_redirect"."site_id" = "django_redirect__site"."id" 
WHERE ("django_redirect__site"."id" ILIKE '1' AND "django_redirect"."old_path" ILIKE '/agrarbuero')

[anonymous]

I'm seeing this error too, if any of the fields in a unique_together() list are also foreign keys. Using PostgreSQL 8.3.0

[Jason Davies]

Fix for iexact in PostgreSQL 8.3

[Jason Davies]

I've attached a patch which fixes the problem. Could someone please review and check that this is the correct approach to fixing this? This should be high-priority as many people are using Django with PostgreSQL and 8.3 has been released now.

[trbs]

Havn't looked to much into this, but it seems to stand to reason that other operations like LIKE should also be casted.
Like: 'iexact', 'contains', 'icontains', 'startswith', 'endswith', 'istartswith', 'iendswidth'; All are currently auto-casted to strings in PostgreSQL <8.3 as the release notes of 8.3 explains.

Possibly this problem can also appear with normal comparisons (not sure here) if you would try to compare a date or something to a raw string. (like FooModel.objects.filter(my_date_fieldeq='01-01-2008') ? again not tested this myself.)

Essentially as i read the release notes of PostgreSQL 8.3, the driver should cast every case where auto-casting is now disabled to be fully compatible with pre 8.3 releases.

[Malcolm Tredinnick]

(Changing ticket title to reflect the problem and closing #6605 in favour of this one, since there are more details of the impact here.)

Following ​a discussion on django-dev about this, we're inclined, if at all possible, to not make any changes here. Instead, the right comparison types for the data should be used.

However, there may be some core functionality that isn't then possible, so that needs checking.

If there's a relatively simple patch that only has an impact on non-text fields (having a pointless cast for text fields is a performance penalty on people doing the right thing, which isn't cool) and only for PostgreSQL (other backends shouldn't have to pay the penalty), we can include it. But I, and others, would prefer that we instead avoided the need for this if at all possible.

[anonymous]

Maybe there is another, better ticket for this, but I'd like to point out again that it affects unique_together(). Example:

class ServiceCategory(models.Model):

service = models.ForeignKey(Service)
category = models.ForeignKey(Category)

class Meta:

order_with_respect_to = 'service'
unique_together = (('service', 'category'),)

This will cause a ProgrammingError with PostgreSQL 8.3 with the following error:

operator does not exist: integer * unknown LINE 1: ...ory" WHERE "service_servicecategory"."service_id" ILIKE '1' ... ^{HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.}

I don't think this problem can be ignored, and it's only a matter of time before more people run into it, as they upgrade their PostgreSQL installations to 8.3.

[anonymous]

(sorry for the double post, I'll format this one so it's actually readable)

Maybe there is another, better ticket for this, but I'd like to point out again that it affects unique_together(). Example:

class ServiceCategory(models.Model):
    service = models.ForeignKey(Service)
    category = models.ForeignKey(Category)

    class Meta:
        order_with_respect_to = 'service'
        unique_together = (('service', 'category'),)

This will cause a ProgrammingError with PostgreSQL 8.3 with the following error:

operator does not exist: integer ~~* unknown LINE 1: ...ory" WHERE "service_servicecategory"."service_id" ILIKE '1' ... ^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I don't think this problem can be ignored, and it's only a matter of time before more people run into it, as they upgrade their PostgreSQL installations to 8.3.

[trbs]

Replying to anonymous:

This is related to ticket #4562

​http://code.djangoproject.com/ticket/4562

Because the orm mapper incorrectly uses iexact instead of exact for manipulators unique_together.

That patch also solves this problem.

Also at this moment i cannot think of anything why on earth iexact is used. Seem to me that only can
create errors, as databases are suppose to be case-sensitive. (And that MySQL is case-insensitive by default is imho a very very big bug in there database)

[charlie@…]

Updated iexact patch, against r7566. Supports postgresql and psycopg2_postgresql backends.

[charlie@…]

I added an updated patch. It adds the functionality for the psycopg2_postgresql backend as well as the postgresql backend.

[Tai Lee <real.human@…>]

It also affects generic relations. You will run into problems trying to create a generic relation on models that have a non-integer primary key (if you've followed the examples and used IntegerField for object_id on the generic model.

If you've previously worked around this by using CharField for object_id, you will then run into problems when trying to access the GenericRelatedObjectManager on models that have an integer primary key.

Without PostgreSQL's implicit type cast the only solution is to modify GenericRelatedObjectManager to explicitly cast the content object's primary key to match the generic model's object_id field. This fix would still require users to manually cast primary key values when accessing generic relations manually.

If we're not going to fix this everywhere (e.g. field lookups in userland, where the user is able to cast their data manually) we should at least fix it everywhere in Django core where the user is unable to cast their data.

[Dan Watson]

I can't reproduce the unique_together problem specifically after the newforms-admin merge, but here is a patch (with tests) to allow text matching on non-text fields in PostgreSQL 8.3.

[Dan Watson]

text matching for non-text fields in PostgreSQL 8.3

[Malcolm Tredinnick]

(In [8242]) Fixed #6523 -- Use the correct cast on field types for PostgreSQL when
searching within a field column (e.g. "like", "contains", etc). Required for
PostgreSQL 8.3. Thanks to Dan Watson for the patch.

[jbrainspot]

It seems like the bug is still around. I am using PostgresSQL 8.3.5 on Mac OS 10.5.6

Here are my models:

from django.contrib.comments.models import Comment
class Review(Comment):

....
special_mentions = generic.GenericRelation("SpecialMention")

class Business(models.Model):

....
reviews = generic.GenericRelation("reviews.Review", object_id_field="object_pk")

From django shell:

from businesses.models import *

Business.objects.get(id=20).reviews.all()

Traceback (most recent call last):

File "<console>", line 1, in <module>
File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 147, in repr

data = list(self[:REPR_OUTPUT_SIZE + 1])

File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 162, in len

self._result_cache.extend(list(self._iter))

File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 275, in iterator

for row in self.query.results_iter():

File "/Library/Python/2.5/site-packages/django/db/models/sql/query.py", line 206, in results_iter

for rows in self.execute_sql(MULTI):

File "/Library/Python/2.5/site-packages/django/db/models/sql/query.py", line 1734, in execute_sql

cursor.execute(sql, params)

File "/Library/Python/2.5/site-packages/django/db/backends/util.py", line 19, in execute

return self.cursor.execute(sql, params)

ProgrammingError: operator does not exist: text = integer
LINE 1: ...ments"."id") WHERE ("django_comments"."object_pk" = 20 AND ...

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

[jbrainspot]

I apologize for using the wrong heading size. I did not know the font-size property of h1 was so large.

Replying to jbrainspot:

It seems like the bug is still around. I am using PostgresSQL 8.3.5 on Mac OS 10.5.6

Here are my models:

from django.contrib.comments.models import Comment
class Review(Comment):

....
special_mentions = generic.GenericRelation("SpecialMention")

class Business(models.Model):

....
reviews = generic.GenericRelation("reviews.Review", object_id_field="object_pk")

From django shell:

from businesses.models import *

Business.objects.get(id=20).reviews.all()

Traceback (most recent call last):

File "<console>", line 1, in <module>
File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 147, in repr

data = list(self[:REPR_OUTPUT_SIZE + 1])

File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 162, in len

self._result_cache.extend(list(self._iter))

File "/Library/Python/2.5/site-packages/django/db/models/query.py", line 275, in iterator

for row in self.query.results_iter():

File "/Library/Python/2.5/site-packages/django/db/models/sql/query.py", line 206, in results_iter

for rows in self.execute_sql(MULTI):

File "/Library/Python/2.5/site-packages/django/db/models/sql/query.py", line 1734, in execute_sql

cursor.execute(sql, params)

File "/Library/Python/2.5/site-packages/django/db/backends/util.py", line 19, in execute

return self.cursor.execute(sql, params)

ProgrammingError: operator does not exist: text = integer
LINE 1: ...ments"."id") WHERE ("django_comments"."object_pk" = 20 AND ...

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

[Karen Tracey]

Please open new tickets for new problems. This one was marked fixed more than six months ago, it would have been reopened almost immediately if the original fix really did not fix the problem, so you are likely seeing something at least slightly different.

Also, please use WikiFormatting, not html (follow the link that's on every page to learn about it) and please please please use the "Preview" button. Both of your updates to this ticket are largely unreadable because the formatting is so messed up. It is also not necessary to quote the whole comment you are responding to when replying to a comment -- it's all right there in the ticket so quoting should be used only in cases where you want to identify something specific in a previous comment that you want to reply to.

Finally, you changed the version from SVN to 1.0-beta1 -- surely you are not actually running 1.0-beta1 at this point? If so the first thing to try would be updating to the latest 1.0 release which is 1.0.2.

[Jacob]

Milestone 1.0 deleted