[Django] #27213: PostgreSQL-9.4 ArrayField with null throws ProgrammingError but not ValidationError on Linux but not Windows
Django
ValidationError on Linux but not Windows
-------------------------------------+-------------------------------------
Reporter: mikofski | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) | Keywords: postgres, arrayfield,
Severity: Normal | programmingerror
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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:
{{{#!python
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.
{{{#!python
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_full.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. :(
--
Ticket URL: <https://code.djangoproject.com/ticket/27213>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
ValidationError on Linux but not Windows
-------------------------------------+-------------------------------------
Reporter: mikofski | Owner: nobody
Component: contrib.postgres | Version: 1.9
Severity: Normal | Resolution:
| worksforme
Keywords: postgres, | Triage Stage:
arrayfield, programmingerror | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* needs_better_patch: => 0
* component: Database layer (models, ORM) => contrib.postgres
* needs_tests: => 0
* needs_docs: => 0
* resolution: => worksforme
Old description:
New description:
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:
{{{#!python
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.
{{{#!python
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
--
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/27213#comment:1>
Django
ValidationError on Linux but not Windows
-------------------------------------+-------------------------------------
Reporter: mikofski | Owner: nobody
Component: contrib.postgres | Version: 1.9
Severity: Normal | Resolution:
| worksforme
Keywords: postgres, | Triage Stage:
arrayfield, programmingerror | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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).
--
Ticket URL: <https://code.djangoproject.com/ticket/27213#comment:2>
Django
Psycopg < 2.6.1
-------------------------------------+-------------------------------------
Reporter: Mark Mikofski | Owner: nobody
Type: Bug | Status: closed
Component: contrib.postgres | Version: 1.9
Keywords: postgres, | Triage Stage:
arrayfield, programmingerror | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* resolution: worksforme => wontfix
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/27213#comment:3>
Django
Psycopg < 2.6.1
-------------------------------------+-------------------------------------
Reporter: Mark Mikofski | Owner: nobody
Type: Bug | Status: closed
Component: contrib.postgres | Version: 1.9
Severity: Normal | Resolution: wontfix
Keywords: postgres, | Triage Stage:
arrayfield, programmingerror | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27213#comment:4>