[Django] #28914: Django Generates Incorrect SQL for Altering Multiple Nullable Fields With Defaults
Django
Defaults
---------------------------------------+------------------------
Reporter: James Pulec | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.11
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
---------------------------------------+------------------------
It seems that if a model has multiple fields on it that are nullable, and
then modified to be non-nullable and have defaults, the sql generated
tries to mix updating and schema changes and results in postgres yelling
about pending trigger events. This only seems to happen if the model also
has a foreign key to another object. I think that's the pending trigger
event that is being referenced. As an example:
{{{#!python
# Initial models
class Basket(models.Model):
spam = models.IntegerField(null=True)
eggs = models.IntegerField(null=True)
bike = models.ForeignKey('baskets.Bike')
class Bike(models.Model):
name = models.TextField()
}}}
You then create initial migrations, run them, and add some data. Then
alter the models to be as follows:
{{{#!python
# Altered models
class Basket(models.Model):
spam = models.IntegerField(default=1)
eggs = models.IntegerField(default=1)
bike = models.ForeignKey('baskets.Bike')
class Bike(models.Model):
name = models.TextField()
}}}
If you then generate a migration, and try to run it, the follow error and
traceback occur.
{{{
Operations to perform:
Apply all migrations: admin, auth, baskets, contenttypes, sessions
Running migrations:
Applying baskets.0002_auto_20171209_2319...Traceback (most recent call
last):
File "/usr/local/lib/python3.6/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
psycopg2.OperationalError: cannot ALTER TABLE "baskets_basket" because it
has pending trigger events
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "mysite/manage.py", line 22, in <module>
execute_from_command_line(sys.argv)
File "/usr/local/lib/python3.6/site-
packages/django/core/management/__init__.py", line 364, in
execute_from_command_line
utility.execute()
File "/usr/local/lib/python3.6/site-
packages/django/core/management/__init__.py", line 356, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/usr/local/lib/python3.6/site-
packages/django/core/management/base.py", line 283, in run_from_argv
self.execute(*args, **cmd_options)
File "/usr/local/lib/python3.6/site-
packages/django/core/management/base.py", line 330, in execute
output = self.handle(*args, **options)
File "/usr/local/lib/python3.6/site-
packages/django/core/management/commands/migrate.py", line 204, in handle
fake_initial=fake_initial,
File "/usr/local/lib/python3.6/site-
packages/django/db/migrations/executor.py", line 115, in migrate
state = self._migrate_all_forwards(state, plan, full_plan, fake=fake,
fake_initial=fake_initial)
File "/usr/local/lib/python3.6/site-
packages/django/db/migrations/executor.py", line 145, in
_migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake,
fake_initial=fake_initial)
File "/usr/local/lib/python3.6/site-
packages/django/db/migrations/executor.py", line 244, in apply_migration
state = migration.apply(state, schema_editor)
File "/usr/local/lib/python3.6/site-
packages/django/db/migrations/migration.py", line 129, in apply
operation.database_forwards(self.app_label, schema_editor, old_state,
project_state)
File "/usr/local/lib/python3.6/site-
packages/django/db/migrations/operations/fields.py", line 221, in
database_forwards
schema_editor.alter_field(from_model, from_field, to_field)
File "/usr/local/lib/python3.6/site-
packages/django/db/backends/base/schema.py", line 515, in alter_field
old_db_params, new_db_params, strict)
File "/usr/local/lib/python3.6/site-
packages/django/db/backends/postgresql/schema.py", line 112, in
_alter_field
new_db_params, strict,
File "/usr/local/lib/python3.6/site-
packages/django/db/backends/base/schema.py", line 710, in _alter_field
params,
File "/usr/local/lib/python3.6/site-
packages/django/db/backends/base/schema.py", line 120, in execute
cursor.execute(sql, params)
File "/usr/local/lib/python3.6/site-
packages/django/db/backends/utils.py", line 79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/usr/local/lib/python3.6/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line
94, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/usr/local/lib/python3.6/site-packages/django/utils/six.py", line
685, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.6/site-
packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
django.db.utils.OperationalError: cannot ALTER TABLE "baskets_basket"
because it has pending trigger events
}}}
The SQL generated by running sql migrate is:
{{{
BEGIN;
--
-- Alter field eggs on basket
--
ALTER TABLE "baskets_basket" ALTER COLUMN "eggs" SET DEFAULT 1;
UPDATE "baskets_basket" SET "eggs" = 1 WHERE "eggs" IS NULL;
ALTER TABLE "baskets_basket" ALTER COLUMN "eggs" SET NOT NULL;
ALTER TABLE "baskets_basket" ALTER COLUMN "eggs" DROP DEFAULT;
--
-- Alter field spam on basket
--
ALTER TABLE "baskets_basket" ALTER COLUMN "spam" SET DEFAULT 1;
UPDATE "baskets_basket" SET "spam" = 1 WHERE "spam" IS NULL;
ALTER TABLE "baskets_basket" ALTER COLUMN "spam" SET NOT NULL;
ALTER TABLE "baskets_basket" ALTER COLUMN "spam" DROP DEFAULT;
COMMIT;
}}}
I ran into this on Django 1.11, but it also still appears to be an issue
when I test Django 2.0 as well.
--
Ticket URL: <https://code.djangoproject.com/ticket/28914>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Defaults
Reporter: James Pulec | Owner: nobody
Component: Migrations | Version: 1.11
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* status: new => closed
* resolution: => duplicate
Comment:
Duplicate of #25105.
--
Ticket URL: <https://code.djangoproject.com/ticket/28914#comment:1>