[Django] #25678: migrate scalar to array field in PostrgreSQL fails
Django
-----------------------------+---------------------------------------------
Reporter: mikofski | Owner: nobody
Type: | Status: new
Uncategorized |
Component: Migrations | Version: 1.8
Severity: Normal | Keywords: PostgreSQL, migrate, ArrayField
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------+---------------------------------------------
Django-1.8.5
Python 2.7.10
PostgreSQL-9.4
Problem: Scalar type might not be cast to array of same type explicitly
with `USING` SQL command?
Solution: Use psql to migrate fields manually
{{{
dev=# ALTER TABLE my_app_mymodel ALTER COLUMN "my_field" TYPE double
precision [] USING array["my_field"]::double precision[];
ALTER TABLE
}}}
== Details ==
given an initial model:
{{{
class MyModel(models.Model):
my_field = models.FloatField()
}}}
change this to `ArrayField`
{{{
class MyModel(models.Model):
my_field = ArrayField(
base_field=models.FloatField(),
default=list
)
}}}
then make migrations and migrate
{{{
$ ./manage.py makemigrations
$ ./manage.py migrate
}}}
returns the following traceback:
{{{
Operations to perform:
Synchronize unmigrated apps: staticfiles, messages
Apply all migrations: admin, contenttypes, my_app, auth, sessions
Synchronizing apps without migrations:
Creating tables...
Running deferred SQL...
Installing custom SQL...
Running migrations:
Rendering model states... DONE
Applying my_app.0XYZ_auto_YYYYMMDD_hhmm...Traceback (most recent call
last):
File "./manage.py", line 10, in <module>
execute_from_command_line(sys.argv)
File "C:\Python27\lib\site-packages\django\core\management\__init__.py",
line 351, in execute_from_command_line
utility.execute()
File "C:\Python27\lib\site-packages\django\core\management\__init__.py",
line 343, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "C:\Python27\lib\site-packages\django\core\management\base.py",
line 394, in run_from_argv
self.execute(*args, **cmd_options)
File "C:\Python27\lib\site-packages\django\core\management\base.py",
line 445, in execute
output = self.handle(*args, **options)
File "C:\Python27\lib\site-
packages\django\core\management\commands\migrate.py", line 222, in handle
executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
File "C:\Python27\lib\site-packages\django\db\migrations\executor.py",
line 110, in migrate
self.apply_migration(states[migration], migration, fake=fake,
fake_initial=fake_initial)
File "C:\Python27\lib\site-packages\django\db\migrations\executor.py",
line 148, in apply_migration
state = migration.apply(state, schema_editor)
File "C:\Python27\lib\site-packages\django\db\migrations\migration.py",
line 115, in apply
operation.database_forwards(self.app_label, schema_editor, old_state,
project_state)
File "C:\Python27\lib\site-
packages\django\db\migrations\operations\fields.py", line 201, in
database_forwards
schema_editor.alter_field(from_model, from_field, to_field)
File "C:\Python27\lib\site-packages\django\db\backends\base\schema.py",
line 484, in alter_field
old_db_params, new_db_params, strict)
File "C:\Python27\lib\site-packages\django\db\backends\base\schema.py",
line 636, in _alter_field
params,
File "C:\Python27\lib\site-packages\django\db\backends\base\schema.py",
line 111, in execute
cursor.execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\backends\utils.py", line
79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\backends\utils.py", line
64, in execute
return self.cursor.execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\utils.py", line 97, in
__exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "C:\Python27\lib\site-packages\django\db\backends\utils.py", line
64, in execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "my_field " cannot be cast
automatically to type double precision[]
HINT: Specify a USING expression to perform the conversion.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25678>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: mikofski | Owner: nobody
Type: Uncategorized | Status: new
Component: Migrations | Version: 1.8
Severity: Normal | Resolution:
Keywords: PostgreSQL, | Triage Stage:
migrate, ArrayField | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by mikofski):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
using `sqlmigrate` it can be seen that the SQL used does not include the
`USING` command to cast the old column to the new type
{{{
$ ./manage.py sqlmigrate my_app 0XYZ_auto_YYYYMMDD_hhmm
BEGIN;
ALTER TABLE "my_app_mymodel" ALTER COLUMN "my_field" TYPE double
precision[];
COMMIT;
}}}
it should be ...
{{{
BEGIN;
ALTER TABLE "my_app_mymodel" ALTER COLUMN "my_field" TYPE double
precision[] USING array["my_field"]::double precision[];
COMMIT;
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25678#comment:1>
Django
Reporter: mikofski | Owner: nobody
Type: Uncategorized | Status: new
Component: Migrations | Version: master
Severity: Normal | Resolution:
Keywords: PostgreSQL, | Triage Stage:
migrate, ArrayField | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* version: 1.8 => master
Comment:
The `USING` part was added by #25002 which should be part of Django 1.9
but scalar to array conversion might require adjustment.
Can you confirm the issue also exists in 1.9?
--
Ticket URL: <https://code.djangoproject.com/ticket/25678#comment:2>
Django
Reporter: mikofski | Owner: nobody
Type: Uncategorized | Status: new
Component: Migrations | Version: master
Severity: Normal | Resolution:
Keywords: PostgreSQL, | Triage Stage:
migrate, ArrayField | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by mikofski):
I'm working on a patch for this:
* add new SQL statement here:
[https://github.com/django/django/blob/1.8.5/django/db/backends/base/schema.py#L45
django/db/backends/base/schema.py:45]
{{{
sql_alter_column_array = "ALTER COLUMN %(column)s TYPE %(type)s USING
array[%(column)s]::%(type)s"
}}}
* in `_alter_field()` at
[https://github.com/django/django/blob/1.8.5/django/db/backends/base/schema.py#L555
L555] check if casting to an array and call something similar to
`_alter_column_type_sql()` at
[https://github.com/django/django/blob/1.8.5/django/db/backends/base/schema.py#L753
L753] with new SQL statements then return fragments.
--
Ticket URL: <https://code.djangoproject.com/ticket/25678#comment:3>
Django
Reporter: mikofski | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: master
Severity: Normal | Resolution:
migrate, ArrayField |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/25678#comment:4>
Django
Reporter: mikofski | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: master
Severity: Normal | Resolution:
Keywords: PostgreSQL, | Triage Stage: Accepted
migrate, ArrayField |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by charettes):
@mikofski I suggest we name the attribute
`sql_alter_scalar_column_to_array` instead and add a test to make sure
`array` to `array` conversion doesn't use it (e.g. `text[]` -> `int[]`)
--
Ticket URL: <https://code.djangoproject.com/ticket/25678#comment:5>
Django
Reporter: mikofski | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: master
Severity: Normal | Resolution:
Keywords: PostgreSQL, | Triage Stage: Accepted
migrate, ArrayField |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by mikofski):
@charettes okay, just catching up - I haven't checked django-1.9 yet, I
should do that first to see if any changes are needed. Thanks!
--
Ticket URL: <https://code.djangoproject.com/ticket/25678#comment:6>
Django
Reporter: mikofski | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: master
Severity: Normal | Resolution:
Keywords: PostgreSQL, | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* keywords: PostgreSQL, migrate, ArrayField => PostgreSQL, ArrayField
--
Ticket URL: <https://code.djangoproject.com/ticket/25678#comment:7>
Django
Reporter: Mark Mikofski | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: master
Severity: Normal | Resolution: fixed
Keywords: PostgreSQL, | Triage Stage: Accepted
ArrayField |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mark Mikofski):
* status: new => closed
* resolution: => fixed
Comment:
this was fixed in #25002 in
[https://github.com/django/django/blob/73040e584a9ccc770593a3885f5fe40fda142e0d/django/db/backends/postgresql_psycopg2/schema.py#L8
postgresql.schema.py:8] by
[https://github.com/django/django/commit/73040e584a9ccc770593a3885f5fe40fda142e0d
commit 73040e58] thanks!
--
Ticket URL: <https://code.djangoproject.com/ticket/25678#comment:8>