#27213: PostgreSQL-9.4 ArrayField with null throws ProgrammingError but not ValidationError on Linux but not Windows -------------------------------------+------------------------------------- Reporter: mikofski | Owner: nobody Type: Bug | Status: closed 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 -------------------------------------+------------------------------------- Changes (by timgraham):
* status: new => closed * needs_better_patch: => 0 * component: Database layer (models, ORM) => contrib.postgres * needs_tests: => 0 * needs_docs: => 0 * resolution: => worksforme Old 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_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. :( 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 # 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. :( -- 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 <https://code.djangoproject.com/> The Web framework for perfectionists with deadlines. -- You received this message because you are subscribed to the Google Groups "Django updates" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-updates+unsubscr...@googlegroups.com. To post to this group, send email to django-updates@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.7457b633f1e1d74a8948bd432a5bb25c%40djangoproject.com. For more options, visit https://groups.google.com/d/optout.