Code

Opened 6 years ago

Closed 15 months ago

#10015 closed Bug (fixed)

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

Reported by: carljm Owned by: carljm
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 carljm 6 years ago.
fix by introspection of ct_fk_field in BaseGenericInlineFormset
10015_r12120.diff (2.5 KB) - added by carljm 5 years ago.
patch with test and fix at model-field level
10015_11X.diff (2.6 KB) - added by kmtracey 4 years ago.
Possible fix for 1.1.X branch

Download all attachments as: .zip

Change History (19)

Changed 6 years ago by carljm

fix by introspection of ct_fk_field in BaseGenericInlineFormset

comment:1 Changed 6 years ago by carljm

  • Has patch set
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 Changed 6 years ago by thejaswi_puthraya

  • Component changed from Contrib apps to django.contrib.comments
  • Keywords comments, postgresql, pk added
  • Triage Stage changed from Unreviewed to Accepted

Hah, yet another postgresql 8.3 quirk!!!

comment:3 Changed 6 years ago by carljm

  • Component changed from django.contrib.comments to Contrib apps
  • Owner changed from nobody to carljm
  • Status changed from new to assigned

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 Changed 6 years ago by thejaswi_puthraya

Oops, extremely sorry about mis-tagging the component.

comment:5 Changed 5 years ago by anonymous

  • Cc me@… added

comment:6 Changed 5 years ago by carljm

  • Needs tests set
  • Patch needs improvement set

comment:7 Changed 5 years ago by anonymous

  • Cc ramusus@… added

comment:8 Changed 5 years ago by trent

  • Cc tjurewicz@… added

comment:9 Changed 5 years ago by carljm

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.

Changed 5 years ago by carljm

patch with test and fix at model-field level

comment:10 Changed 5 years ago by carljm

  • Component changed from Contrib apps to Database layer (models, ORM)
  • Keywords comments, removed
  • Needs tests unset
  • Patch needs improvement unset

comment:11 Changed 5 years ago by adrian

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

(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

Changed 4 years ago by kmtracey

Possible fix for 1.1.X branch

comment:12 Changed 4 years ago by kmtracey

(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 Changed 4 years ago by ramusus

  • Resolution fixed deleted
  • Status changed from closed to reopened

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 Changed 4 years ago by kmtracey

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

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 Changed 15 months ago by arctelix@…

  • Easy pickings unset
  • Has patch unset
  • Resolution fixed deleted
  • Severity set to Normal
  • Status changed from closed to new
  • Type set to Bug
  • UI/UX unset
  • Version changed from 1.0 to 1.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 Changed 15 months ago by aaugustin

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

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

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.