Native JSON type for PostgreSQL 9.2+ causing problems with Django 1.5 in some conditions

[Karmak23]

Hi,

I managed to hunt a bug back to #32 . I use PG 9.2 on OSX, and the native JSON type has been causing me a headache in some very corner-cased situation. Here the stacktrace given by Django:

http://dev.1flow.net/development/1flow-dev/group/82/

As you can see, the traditionnal Django message about transaction aborted doesn't help at all, but if I manage to run the culprit SQL query on my PG9.2, I get:

ERROR:  could not identify an equality operator for type json
LINE 1: ...mail_announcements", "base_user"."last_modified", "base_user...
                                                             ^
********** Erreur **********

ERROR: could not identify an equality operator for type json
État SQL :42883
Caractère : 181

Having trying to hunt down the problem during a fair long amount of time, I found these:

http://michael.otacoo.com/postgresql-2/postgres-9-2-highlight-json-data-type/
http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

Which clearly define that JSON operators comes only in PG9.3. I thus consider the 9.2 support for JSON kind of incomplete. In django, selecting any json column in a query seems to crash PG. Any request on the table, but not involving the json fields, is OK.

On my production machines I have PG 9.1 and luckily the JSON fields are stored as text there, so they don't crash Django. I think that in jsonfield/fields.py:

def db_type(self, connection):
    if connection.vendor == 'postgresql' and connection.pg_version >= 90200:
        return 'json'
    else:
        return super(JSONFieldBase, self).db_type(connection)

Should really be connection.pg_version >= 90300: To avoid messing with the 9.2 incomplete implementation. In fact, i've create a pull request because reverting to text makes everything work again on my 9.2 server. Sorry, I don't seem to find how to merge the pull request with the current issue in the github interface.

NOTE: I have no PG 9.3 to test if the implementation works or not.

Best regards,

[bradjasper]

Hey @Karmak23 thanks for the bug report and pull request. Actually just yesterday I saw a reference to an incomplete 9.2 implementation and was planning on looking into it––thanks for doing it for me!

PR is pulled in. Cheers :)

[ewjoachim]

Actually the same problem happens in 9.3.

It seems to be because of a SELECT DISTINCT user query that django does when the model containing the json field is the User model, at admin / user creation.

[mkhattab]

Agree with @ewjoachim. For SELECT DISTINCT queries, the query fails if a column is included of type json. Django's distinct method on querysets accepts a list of fields which will result in a query with SELECT DISTINCT ON. So you can work around this issue by excluding the fields which are of type json. I'm having problems with this in Django's admin site and I haven't figured out a work around for that yet.

For reference: https://docs.djangoproject.com/en/dev/ref/models/querysets/#distinct

I'm using Postgresql version 9.3.1

[mkhattab]

The work around for this in the Django admin is to subclass QuerySet and Manager and override the distinct method.

[mkhattab]

@ewjoachim

I think the better and less volatile work around would be to subclass the postgres db backend class and from json type fields from there.

https://github.com/django/django/blob/1.5.1/django/db/backends/postgresql_psycopg2/operations.py#L191

[mkhattab]

Or perhaps it's better to subclass django's sql compiler. Oh well.