ArrayField with null throws ProgrammingError but not ValidationError on Psycopg < 2.6.1

[Mark Mikofski]

Python-2.7.10
PostgreSQL-9.4
psycopg2-2.5.1 (linux) and psycopg2-2.6.1 (windows) (*)
Django-1.9
OS: Oracle7 vs. Windows 7

given a model:

from django.contrib.postgres.fields import ArrayField
from django.db import models


class MyModel(models.Model):
    my_array_field = ArrayField(base_field=models.FloatField(null=True))

If you try to save an array of None Django will validate it, but on Linux PostgreSQL will not insert the row, but on windows it does.

from my_app.models import MyModel

test_model = MyModel(my_array_field=[None])  # make a test instance of model

test_model.full_clean()  # check for ValidationError
# everything is okay!

test_model.save()  # insert model instance into PostgreSQL database
# Windows: Success!
# Linux:   Failure!

here is the stacktrace from Linux:

In [59]: rtest.save()
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-59-34c0fed69116> in <module>()
----> 1 rtest.save()

~/.local/lib/python2.7/site-packages/django/db/models/base.pyc in save(self, force_insert, force_update, using, update_fields)
    706
    707         self.save_base(using=using, force_insert=force_insert,
--> 708                        force_update=force_update, update_fields=update_fields)
    709     save.alters_data = True
    710

~/.local/lib/python2.7/site-packages/django/db/models/base.pyc in save_base(self, raw, force_insert, force_update, using, update_fields)
    734             if not raw:
    735                 self._save_parents(cls, using, update_fields)
--> 736             updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
    737         # Store the database on which the object was saved
    738         self._state.db = using

~/.local/lib/python2.7/site-packages/django/db/models/base.pyc in _save_table(self, raw, cls, force_insert, force_update, using, update_fields)
    818
    819             update_pk = bool(meta.has_auto_field and not pk_set)
--> 820             result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
    821             if update_pk:
    822                 setattr(self, meta.pk.attname, result)

~/.local/lib/python2.7/site-packages/django/db/models/base.pyc in _do_insert(self, manager, using, fields, update_pk, raw)
    857         """
    858         return manager._insert([self], fields=fields, return_id=update_pk,
--> 859                                using=using, raw=raw)
    860
    861     def delete(self, using=None, keep_parents=False):

~/.local/lib/python2.7/site-packages/django/db/models/manager.pyc in manager_method(self, *args, **kwargs)
    120         def create_method(name, method):
    121             def manager_method(self, *args, **kwargs):
--> 122                 return getattr(self.get_queryset(), name)(*args, **kwargs)
    123             manager_method.__name__ = method.__name__
    124             manager_method.__doc__ = method.__doc__

~/.local/lib/python2.7/site-packages/django/db/models/query.pyc in _insert(self, objs, fields, return_id, raw, using)
   1037         query = sql.InsertQuery(self.model)
   1038         query.insert_values(fields, objs, raw=raw)
-> 1039         return query.get_compiler(using=using).execute_sql(return_id)
   1040     _insert.alters_data = True
   1041     _insert.queryset_only = False

~/.local/lib/python2.7/site-packages/django/db/models/sql/compiler.pyc in execute_sql(self, return_id)
   1058         with self.connection.cursor() as cursor:
   1059             for sql, params in self.as_sql():
-> 1060                 cursor.execute(sql, params)
   1061             if not (return_id and cursor):
   1062                 return

~/.local/lib/python2.7/site-packages/django/db/backends/utils.pyc in execute(self, sql, params)
     77         start = time()
     78         try:
---> 79             return super(CursorDebugWrapper, self).execute(sql, params)
     80         finally:
     81             stop = time()

~/.local/lib/python2.7/site-packages/django/db/backends/utils.pyc in execute(self, sql, params)
     62                 return self.cursor.execute(sql)
     63             else:
---> 64                 return self.cursor.execute(sql, params)
     65
     66     def executemany(self, sql, param_list):

~/.local/lib/python2.7/site-packages/django/db/utils.pyc in __exit__(self, exc_type, exc_value, traceback)
     93                 if dj_exc_type not in (DataError, IntegrityError):
     94                     self.wrapper.errors_occurred = True
---> 95                 six.reraise(dj_exc_type, dj_exc_value, traceback)
     96
     97     def __call__(self, func):

~/.local/lib/python2.7/site-packages/django/db/backends/utils.pyc in execute(self, sql, params)
     62                 return self.cursor.execute(sql)
     63             else:
---> 64                 return self.cursor.execute(sql, params)
     65
     66     def executemany(self, sql, param_list):

ProgrammingError: column "my_array_field" is of type double precision[] but expression is of type text[]
LINE 1: ...ARRAY[NULL...
           ^
HINT:  You will need to rewrite or cast the expression.

I wonder if it has anything to do with this SO question:
​http://stackoverflow.com/questions/14713106/insert-unnested-array-of-null-values-into-a-double-precision-column-postgresql

(*) if the issue is psycopg2 version discrepancy that's a bummer, because I don't think I can build psycopg2 on a share without the postrgre dev libs. :(

[Tim Graham]

I'm not able to reproduce this. I see ValidationError: {'my_array_field': ['Item 0 in the array did not validate: This field cannot be blank.']} at the full_clean() step.

[Mark Mikofski]

My mistake, blank=True is also a property of the field. We are still experiencing this discrepency.
Which version of Django, postgresql and psycopg2 were you using to test this issue? What version of Linux?
I have a feeling this is psycopg2 because the Linux distro I'm using has a very old version (psycopg2-2.5.3).

[Tim Graham]

I could reproduce the exception with older versions of Psycopg. It seems that Psycopg 2.6.1 fixes it. I don't think it's worth trying to fix this in Django for older versions of Psycopg.

[Mark Mikofski]

Thanks Tim. I updated my linux share to psycopg2-2.6.2 and the issue went away. I agree, not worth it fix. This issue serves as sufficient documentation if anyone happens to come across this problem.

For anyone else in the RedHat family with issue ​https://www.postgresql.org/download/linux/redhat/ so you will need to upgradr , you will need to upgrade both postgresql-9.2 and psycopg2-2.5.3 to newer versions. My version of PostgreSQL on AWS is 9.4.5 (2016/10/7). There are great directions here ​https://www.postgresql.org/docs/current/static/install-procedure.html. Note readline is required for postgres, but you can pass --without-readline to skip it.

~$ curl -Ok https://ftp.postgresql.org/pub/source/v9.4.5/postgresql-9.4.5.tar.gz
~$ tar -xf postgresql-9.4.5.tar.gz
~$ mkdir build_pgsql
~$ cd build_pgsql/
~/build_pgsql $/home/<username>/postgresql-9.4.5/configure --prefix=/home/<username>/.local/pgsql
~/build_pgsql make
~/build_pgsql make check
~/build_pgsql make install

I had trouble building psycopg2 on my linux share because I could not remove my older version of postgres. I was able to resolve it by using --rpath in _psycopg2.so and it seems to work. The documentation on pscopg2 website ​http://initd.org/psycopg/ is useful - you must have pg_config on your path or you can specify it as a build_ext option, and you will need to compile versus

~$ curl -Ok http://initd.org/psycopg/tarballs/PSYCOPG-2-6/psycopg2-2.6.2.tar.gz
~$ tar -xf psycopg2-2.6.2.tar.gz
~$ cd psycopg2-2.6.2
~/psycopg2-2.6.2 $ python setup.py build_ext --pg-config /home/<username>/.local/pgsql/bin/pg_config build --include-dirs=/home/<username>/.local/pgsql/include --library-dirs=/home/<username>/.local/pgsql/lib --rpath=/home/<username>/.local/pgsql/lib:'$ORIGIN'
~$ python setup.py build
~$ python setup.py bdist_wheel
~$ pip install --user dist/psycopg2-2.6.2-cp27-cp27mu-linux_x86_64.whl

The psycopg2 website recommends adding pg_config to your path temporarily

~$ export PATH=/home/<username>/.local/pgsql/bin/:$PATH

You can call pg_config to get the lists of --includes-dirs and --library-dirs

~$ pg_config

You can all use objdump or ldd to see what postgres libraries psycopg2 is linked to.

~/psycopg2-2.6.2 $ cd build/lib.linux-x86_64-2.7/psycopg2/
~/psycopg2-2.6.2/build/lib.linux-x86_64-2.7/psycopg2 $ objdump _psycopg.so -p
~/psycopg2-2.6.2/build/lib.linux-x86_64-2.7/psycopg2 $ ldd _psycopg.so | grep libpq