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

[Carl Meyer]

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

[Carl Meyer]

fix by introspection of ct_fk_field in BaseGenericInlineFormset

[Carl Meyer]

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.

[Thejaswi Puthraya]

Hah, yet another postgresql 8.3 quirk!!!

[Carl Meyer]

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.

[Thejaswi Puthraya]

Oops, extremely sorry about mis-tagging the component.

[Carl Meyer]

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.

[Carl Meyer]

patch with test and fix at model-field level

[Adrian Holovaty]

(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

[Karen Tracey]

Possible fix for 1.1.X branch

[Karen Tracey]

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

[ramusus]

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

[Karen Tracey]

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.

[arctelix@…]

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.

[Aymeric Augustin]

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