Opened 15 years ago

Closed 11 years ago

#10015 closed Bug (fixed)

contrib.comments inline on Postgres 8.3 fails to cast integer to text

Reported by: Carl Meyer Owned by: Carl Meyer
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords: postgresql, pk
Cc: me@…, ramusus@…, tjurewicz@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

(This bug is related to #8554 and #6523). If you display comments inline in the admin like so:

from django.contrib.contenttypes.generic import GenericTabularInline

class CarrotCommentInline(GenericTabularInline):
    model = CarrotComment
    ct_fk_field = 'object_pk'
    extra = 0
    fields = ('user', 'comment', 'ip_address', 'is_public', 'is_removed')

Postgres 8.3 will raise this error (for content object types with non-text primary keys):

Traceback (most recent call last):
  File "/home/carljm/src/py/django/django-1.0.x/django/core/servers/basehttp.py", line 635, in __call__
    return self.application(environ, start_response)
  File "/home/carljm/src/py/django/django-1.0.x/django/core/handlers/wsgi.py", line 239, in __call__
    response = self.get_response(request)
  File "/home/carljm/src/py/django/django-1.0.x/django/core/handlers/base.py", line 128, in get_response
    return self.handle_uncaught_exception(request, resolver, exc_info)
  File "/home/carljm/src/py/django/django-1.0.x/django/core/handlers/base.py", line 148, in handle_uncaught_exception
    return debug.technical_500_response(request, *exc_info)
  File "/home/carljm/src/py/django/django-1.0.x/django/core/handlers/base.py", line 86, in get_response
    response = callback(request, *callback_args, **callback_kwargs)
  File "/home/carljm/src/py/django/django-1.0.x/django/contrib/admin/sites.py", line 157, in root
    return self.model_page(request, *url.split('/', 2))
  File "/home/carljm/src/py/django/django-1.0.x/django/views/decorators/cache.py", line 44, in _wrapped_view_func
    response = view_func(request, *args, **kwargs)
  File "/home/carljm/src/py/django/django-1.0.x/django/contrib/admin/sites.py", line 176, in model_page
    return admin_obj(request, rest_of_url)
  File "/home/carljm/src/py/django/django-1.0.x/django/contrib/admin/options.py", line 197, in __call__
    return self.change_view(request, unquote(url))
  File "/home/carljm/src/py/django/django-1.0.x/django/db/transaction.py", line 238, in _commit_on_success
    res = func(*args, **kw)
  File "/home/carljm/src/py/django/django-1.0.x/django/contrib/admin/options.py", line 592, in change_view
    formset = FormSet(instance=obj)
  File "/home/carljm/src/py/django/django-1.0.x/django/contrib/contenttypes/generic.py", line 303, in __init__
    prefix=self.rel_name
  File "/home/carljm/src/py/django/django-1.0.x/django/forms/models.py", line 350, in __init__
    defaults['initial'] = [model_to_dict(obj) for obj in self.get_queryset()]
  File "/home/carljm/src/py/django/django-1.0.x/django/db/models/query.py", line 185, in _result_iter
    self._fill_cache()
  File "/home/carljm/src/py/django/django-1.0.x/django/db/models/query.py", line 618, in _fill_cache
    self._result_cache.append(self._iter.next())
  File "/home/carljm/src/py/django/django-1.0.x/django/db/models/query.py", line 275, in iterator
    for row in self.query.results_iter():
  File "/home/carljm/src/py/django/django-1.0.x/django/db/models/sql/query.py", line 206, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/home/carljm/src/py/django/django-1.0.x/django/db/models/sql/query.py", line 1734, in execute_sql
    cursor.execute(sql, params)
  File "/home/carljm/src/py/django/django-1.0.x/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: operator does not exist: text = integer
LINE 1: ...HERE ("carrot_comments_carrotcomment"."object_pk" = 8  AND "...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

This is because contrib.comments uses a text field type for the PK portion of its generic foreign key, and Postgres 8.3 does not automatically cast an integer to text.

This same core problem was already worked around once in [8641], which fixed #8554. This case is more difficult to work around, as the offending lookup is part of the BaseGenericInlineFormset code, and we can't just add an explicit cast there. Maybe the object_pk field type could be introspected to check if a cast is needed? Or perhaps there's a more appropriate general solution at the PostgreSQL db backend level.

Attachments (3)

10015_r9729.diff (902 bytes ) - added by Carl Meyer 15 years ago.
fix by introspection of ct_fk_field in BaseGenericInlineFormset
10015_r12120.diff (2.5 KB ) - added by Carl Meyer 14 years ago.
patch with test and fix at model-field level
10015_11X.diff (2.6 KB ) - added by Karen Tracey 14 years ago.
Possible fix for 1.1.X branch

Download all attachments as: .zip

Change History (19)

by Carl Meyer, 15 years ago

Attachment: 10015_r9729.diff added

fix by introspection of ct_fk_field in BaseGenericInlineFormset

comment:1 by Carl Meyer, 15 years ago

Has patch: set

I added a patch which fixes the case of a GFK with a ct_fk_field that is a TextField. This patch doesn't cover other possibilities (CharField?), though it could easily be extended to cover more.

comment:2 by Thejaswi Puthraya, 15 years ago

Component: Contrib appsdjango.contrib.comments
Keywords: comments postgresql pk added
Triage Stage: UnreviewedAccepted

Hah, yet another postgresql 8.3 quirk!!!

comment:3 by Carl Meyer, 15 years ago

Component: django.contrib.commentsContrib apps
Owner: changed from nobody to Carl Meyer
Status: newassigned

This is a contrib.contenttypes issue, not a contrib.comments issue, assuming GFK's are intended to support non-integer primary keys. If they are not, then there's only one possible fix in contrib.comments: switch to an IntegerField and don't support commenting on objects with non-integer PKs.

Accepting this, as I'm happy to improve the fix as needed until this is ready to go in.

comment:4 by Thejaswi Puthraya, 15 years ago

Oops, extremely sorry about mis-tagging the component.

comment:5 by anonymous, 15 years ago

Cc: me@… added

comment:6 by Carl Meyer, 15 years ago

Needs tests: set
Patch needs improvement: set

comment:7 by anonymous, 14 years ago

Cc: ramusus@… added

comment:8 by trent, 14 years ago

Cc: tjurewicz@… added

comment:9 by Carl Meyer, 14 years ago

Per discussion with Adrian at Chicago sprint, treating this as a database-level issue: SomeModel.objects.filter(name=3) (where name is a TextField or CharField) ought to just work across the board, but currently does not on Postgres 8.3+. The solution seems to be to give CharField and TextField a get_prep_value() method that forces conversion to a unicode string, as IntegerField does to int.

I'm attaching a patch with a test that currently fails on PG 8.3+, and the get_prep_value() fix. I also ran the full test suite several times with and without the patch to see if there's a noticeable performance impact from doing the isinstance check on every query to a CharField/TextField: there is not.

by Carl Meyer, 14 years ago

Attachment: 10015_r12120.diff added

patch with test and fix at model-field level

comment:10 by Carl Meyer, 14 years ago

Component: Contrib appsDatabase layer (models, ORM)
Keywords: comments removed
Needs tests: unset
Patch needs improvement: unset

comment:11 by Adrian Holovaty, 14 years ago

Resolution: fixed
Status: assignedclosed

(In [12150]) Fixed #10015 -- PostgreSQL 8.3+ no longer barfs when passing an integer as a filter() value for a CharField or TextField. Thanks, carljm

by Karen Tracey, 14 years ago

Attachment: 10015_11X.diff added

Possible fix for 1.1.X branch

comment:12 by Karen Tracey, 14 years ago

(In [12263]) [1.1.X] Fixed #10015 -- PostgreSQL 8.3+ no longer barfs when passing an integer as a filter() value for a CharField? or TextField?. Thanks, carljm

Backport of r12150 from trunk.

comment:13 by ramusus, 14 years ago

Resolution: fixed
Status: closedreopened

I just get similar error while try to delete object with comments as GenericRelationField:

class List(models.Model):
    ....
    comments = generic.GenericRelation(Comment, object_id_field='object_pk')

Traceback or error:


Traceback:
    File "/home/ram/workspace/movister/web_site/django/core/handlers/base.py" in get_response
      96.                     response = middleware_method(request, callback, callback_args, callback_kwargs)
    File "/home/ram/workspace/movister/web_site/firepython/middleware.py" in process_view
      369.         return self._profile_wrap(callback)(*args, **callback_kwargs)
    File "/home/ram/workspace/movister/web_site/django/contrib/admin/options.py" in wrapper
      243.                 return self.admin_site.admin_view(view)(*args, **kwargs)
    File "/home/ram/workspace/movister/web_site/django/utils/decorators.py" in __call__
      36.         return self.decorator(self.func)(*args, **kwargs)
    File "/home/ram/workspace/movister/web_site/django/utils/decorators.py" in _wrapped_view
      86.                     response = view_func(request, *args, **kwargs)
    File "/home/ram/workspace/movister/web_site/django/utils/decorators.py" in __call__
      36.         return self.decorator(self.func)(*args, **kwargs)
    File "/home/ram/workspace/movister/web_site/django/views/decorators/cache.py" in _wrapped_view_func
      70.         response = view_func(request, *args, **kwargs)
    File "/home/ram/workspace/movister/web_site/django/contrib/admin/sites.py" in inner
      190.             return view(request, *args, **kwargs)
    File "/home/ram/workspace/movister/web_site/django/utils/decorators.py" in _wrapped_view
      86.                     response = view_func(request, *args, **kwargs)
    File "/home/ram/workspace/movister/web_site/django/contrib/admin/options.py" in delete_view
      1123.             obj.delete()
    File "/home/ram/workspace/movister/web_site/django/db/models/base.py" in delete
      604.         delete_objects(seen_objs, using)
    File "/home/ram/workspace/movister/web_site/django/db/models/query.py" in delete_objects
      1192.             del_query.delete_batch_related(pk_list, using=using)
    File "/home/ram/workspace/movister/web_site/django/db/models/sql/subqueries.py" in delete_batch_related
      65.                 self.do_query(f.m2m_db_table(), where, using=using)
    File "/home/ram/workspace/movister/web_site/django/db/models/sql/subqueries.py" in do_query
      27.         self.get_compiler(using).execute_sql(None)
    File "/home/ram/workspace/movister/web_site/django/db/models/sql/compiler.py" in execute_sql
      674.         cursor.execute(sql, params)
    File "/home/ram/workspace/movister/web_site/debug_toolbar/panels/sql.py" in execute
      90.             return self.cursor.execute(sql, params)
    
    Exception Type: ProgrammingError at /admin/movister/list/1/delete/
    Exception Value: operator does not exist: text = integer
    LINE 1: DELETE FROM "django_comments" WHERE ("object_pk" IN (1) AND ...
                                                             ^
    HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I use development trunk version r12267 of Django

comment:14 by Karen Tracey, 14 years ago

Resolution: fixed
Status: reopenedclosed

The problem that was reported here is fixed. What you show is a not quite the same problem and deserves its own ticket, which is now #12721.

comment:15 by arctelix@…, 11 years ago

Easy pickings: unset
Has patch: unset
Resolution: fixed
Severity: Normal
Status: closednew
Type: Bug
UI/UX: unset
Version: 1.01.4

This error is still around!

Traceback (most recent call last):

File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 202, in wrapper

response = callback(request, *args, kwargs)

File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 441, in dispatch_list

return self.dispatch('list', request, kwargs)

File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 473, in dispatch

response = method(request, kwargs)

File "/app/.heroku/src/django-tastypie/tastypie/resources.py", line 1244, in get_list

to_be_serialized = paginator.page()

File "/app/.heroku/src/django-tastypie/tastypie/paginator.py", line 186, in page

count = self.get_count()

File "/app/.heroku/src/django-tastypie/tastypie/paginator.py", line 118, in get_count

return self.objects.count()

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/query.py", line 351, in count

return self.query.get_count(using=self.db)

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/sql/query.py", line 418, in get_count

number = obj.get_aggregation(using=using)[None]

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/sql/query.py", line 384, in get_aggregation

result = query.get_compiler(using).execute_sql(SINGLE)

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 818, in execute_sql

cursor.execute(sql, params)

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 52, in execute

return self.cursor.execute(query, args)

DatabaseError: operator does not exist: integer = text LINE 1: ... INNER JOIN "django_comments" ON ("pins_pin"."id" = "django_...


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

comment:16 by Aymeric Augustin, 11 years ago

Resolution: fixed
Status: newclosed

You've also reported this as #20271. Indeed, that's better than re-opening a ticket fixed years ago.

Note: See TracTickets for help on using tickets.
Back to Top