Code

Opened 6 years ago

Closed 5 years ago

Last modified 3 years ago

#6523 closed (fixed)

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

Reported by: pat.j.anderson@… Owned by: mtredinnick
Component: Database layer (models, ORM) Version: 1.0-beta
Severity: Keywords: postgresql
Cc: rene.puls@…, ryan@…, v.oostveen@…, johannes.beigel@…, peter.kese@…, dan.fairs@…, real.human@…, bonelake@…, andrehcampos@… Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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.

Attachments (3)

iexact.diff (484 bytes) - added by esaj 6 years ago.
Fix for iexact in PostgreSQL 8.3
iexact.patch (953 bytes) - added by charlie@… 6 years ago.
Updated iexact patch, against r7566. Supports postgresql and psycopg2_postgresql backends.
6523.diff (1.5 KB) - added by dcwatson 6 years ago.
text matching for non-text fields in PostgreSQL 8.3

Download all attachments as: .zip

Change History (31)

comment:1 Changed 6 years ago by pat.j.anderson@…

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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.

comment:2 Changed 6 years ago by gervystar@…

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

comment:3 Changed 6 years ago by esaj

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

comment:4 Changed 6 years ago by esaj

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

comment:5 Changed 6 years ago by anonymous

  • Cc rene.puls@… added

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')

comment:6 Changed 6 years ago by anonymous

  • Cc ryan@… added

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

Changed 6 years ago by esaj

Fix for iexact in PostgreSQL 8.3

comment:7 Changed 6 years ago by esaj

  • Has patch set
  • Needs tests set

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.

comment:8 Changed 6 years ago by trbs

  • Cc v.oostveen@… added

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.

comment:9 Changed 6 years ago by jbeigel

  • Cc johannes.beigel@… added

comment:10 Changed 6 years ago by mtredinnick

  • Summary changed from ProgrammingError with PostgreSQL: operator does not exist: integer ~~* unknown to PostgreSQL 8.3 cannot do text matching on uncast non-text columns
  • Triage Stage changed from Unreviewed to Accepted

(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.

comment:11 Changed 6 years ago by 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.

comment:12 follow-up: Changed 6 years ago by 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.

comment:13 in reply to: ↑ 12 Changed 6 years ago by 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)

comment:14 Changed 6 years ago by anonymous

  • Cc peter.kese@… added

Changed 6 years ago by charlie@…

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

comment:15 Changed 6 years ago by charlie@…

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

comment:16 Changed 6 years ago by danfairs

  • Cc dan.fairs@… added

comment:17 Changed 6 years ago by 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.

comment:18 Changed 6 years ago by Tai Lee <real.human@…>

  • Cc real.human@… added

comment:19 Changed 6 years ago by Brad Miller <bonelake@…>

  • Cc bonelake@… added

comment:20 Changed 6 years ago by gwilson

  • milestone set to 1.0

comment:21 Changed 6 years ago by andrehcampos

  • Cc andrehcampos@… added

comment:22 Changed 6 years ago by dcwatson

  • Needs tests unset
  • Owner changed from nobody to dcwatson

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.

Changed 6 years ago by dcwatson

text matching for non-text fields in PostgreSQL 8.3

comment:23 Changed 6 years ago by dcwatson

  • Owner changed from dcwatson to mtredinnick

comment:24 Changed 6 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

(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.

comment:25 follow-up: Changed 5 years ago by jbrainspot

  • Resolution fixed deleted
  • Status changed from closed to reopened
  • Triage Stage changed from Accepted to Unreviewed
  • Version changed from SVN to 1.0-beta-1

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.

comment:26 in reply to: ↑ 25 Changed 5 years ago by 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.

comment:27 Changed 5 years ago by kmtracey

  • Resolution set to fixed
  • Status changed from reopened to closed

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.

comment:28 Changed 3 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.