#31735 closed Bug (fixed)
Migration crash when adding inline foreign key to different schema on PostgreSQL.
| Reported by: | Rodrigo Estevao | Owned by: | Simon Charette |
|---|---|---|---|
| Component: | Migrations | Version: | 3.0 |
| Severity: | Release blocker | Keywords: | many-to-many; relationship; intermediate; table; through |
| Cc: | Simon Charette | Triage Stage: | Ready for checkin |
| Has patch: | yes | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
I've been facing a migration problem when using a through table on many-to-many relationship.
When I create the migration using modles configured as follow ...
# src/apps/core/valuation/models.py
from django.utils import timezone
from django.db import models
from django.utils.translation import gettext_lazy as _
class Valuation(models.Model):
ref_year = models.PositiveIntegerField(
_('reference year'),
default=timezone.now().year
)
ref_month = models.PositiveSmallIntegerField(
_('reference month'),
default=timezone.now().month
)
remark = models.TextField(
_('notes'),
null=True,
blank=True,
)
class Meta:
db_table = 'core\".\"valuation'
verbose_name = _('Valuation')
verbose_name_plural = _('Valuations')
# src/apps/loader/datafile/models.py
from django.db import models
from django.utils.translation import gettext_lazy as _
class DataFile(models.Model):
path = models.FileField(_('path'), max_length=4096)
hash = models.CharField(_('file hash'), max_length=128)
remark = models.CharField(
_('remark'),
max_length=128,
null=True,
blank=True
)
uploaded_at = models.DateTimeField(
_('uploaded at'),
auto_now_add=True,
editable=False
)
uploaded_by = models.ForeignKey(
'account.Account',
db_column='uploaded_by',
verbose_name=_('uploaded by'),
related_name='+',
on_delete=models.PROTECT,
)
class Meta:
db_table = 'loader\".\"data_file'
verbose_name = 'data file'
verbose_name_plural = 'data files'
# src/apps/loader/dataload/models.py
from django.db import models
from django.utils.translation import gettext_lazy as _
from apps.loader.datafile.models import DataFile
from apps.core.valuation.models import Valuation
class DataLoad(models.Model):
valuation = models.ForeignKey(
to=Valuation,
verbose_name=_('valuation'),
related_name='dataloads',
on_delete=models.PROTECT
)
datafile = models.ManyToManyField(
to=DataFile,
related_name='dataloads',
through='dataload.DataLoadFile',
through_fields=('dataload', 'datafile',)
)
class Meta:
db_table = 'loader\".\"data_load'
verbose_name = 'data_load'
verbose_name_plural = 'data_load'
class DataLoadFile(models.Model):
dataload = models.ForeignKey(
to=DataLoad,
verbose_name=_('data load'),
related_name='datafiles',
on_delete=models.PROTECT
)
datafile = models.ForeignKey(
to=DataFile,
verbose_name=_('data file'),
related_name='+',
on_delete=models.PROTECT
)
class Meta:
db_table = 'loader\".\"data_load_files'
verbose_name = 'data load file'
verbose_name_plural = 'data load files'
... it creates the following migration file
# Generated by Django 3.0.7 on 2020-06-18 15:58
from django.db import migrations, models
import django.db.models.deletion
class Migration(migrations.Migration):
initial = True
dependencies = [
('valuation', '0001_initial'),
('datafile', '0001_initial'),
]
operations = [
migrations.CreateModel(
name='DataLoad',
fields=[
('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
],
options={
'verbose_name': 'data_load',
'verbose_name_plural': 'data_load',
'db_table': 'loader"."data_load',
},
),
migrations.CreateModel(
name='DataLoadFile',
fields=[
('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
('datafile', models.ForeignKey(on_delete=django.db.models.deletion.PROTECT, related_name='+', to='datafile.DataFile', verbose_name='data file')),
('dataload', models.ForeignKey(on_delete=django.db.models.deletion.PROTECT, related_name='datafiles', to='dataload.DataLoad', verbose_name='data load')),
],
options={
'verbose_name': 'data load file',
'verbose_name_plural': 'data load files',
'db_table': 'loader"."data_load_files',
},
),
migrations.AddField(
model_name='dataload',
name='datafile',
field=models.ManyToManyField(related_name='dataloads', through='dataload.DataLoadFile', to='datafile.DataFile'),
),
migrations.AddField(
model_name='dataload',
name='valuation',
field=models.ForeignKey(on_delete=django.db.models.deletion.PROTECT, related_name='dataloads', to='valuation.Valuation', verbose_name='valuation'),
),
]
when I run the migrate command I receive the following error:
Applying dataload.0001_initial...Traceback (most recent call last):
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedObject: constraint "data_load_valuation_id_9af1ae91_fk_valuation_id" does not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/manage.py", line 21, in <module>
main()
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/manage.py", line 17, in main
execute_from_command_line(sys.argv)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
utility.execute()
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 395, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/core/management/base.py", line 328, in run_from_argv
self.execute(*args, **cmd_options)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/core/management/base.py", line 369, in execute
output = self.handle(*args, **options)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/core/management/base.py", line 83, in wrapped
res = handle_func(*args, **kwargs)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 231, in handle
post_migrate_state = executor.migrate(
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
state = migration.apply(state, schema_editor)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/migrations/migration.py", line 124, in apply
operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/migrations/operations/fields.py", line 110, in database_forwards
schema_editor.add_field(
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 480, in add_field
self.execute(sql, params)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 142, in execute
cursor.execute(sql, params)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 100, in execute
return super().execute(sql, params)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/home/rodrigo/Workspace/fgv/sacvbackend/src/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: constraint "data_load_valuation_id_9af1ae91_fk_valuation_id" does not exist
Error when trying create and run Django migrations!
Failed! The script execution has finished with errors!
If I just remove the intermediate table from DataLoad model like following and run the makemigration command ...
class DataLoad(models.Model):
valuation = models.ForeignKey(
to=Valuation,
verbose_name=_('valuation'),
related_name='dataloads',
on_delete=models.PROTECT
)
datafile = models.ManyToManyField(
to=DataFile,
related_name='dataloads',
# through='dataload.DataLoadFile',
# through_fields=('dataload', 'datafile',)
)
... the resulting migration file is the following ...
# Generated by Django 3.0.7 on 2020-06-18 16:26
from django.db import migrations, models
import django.db.models.deletion
class Migration(migrations.Migration):
initial = True
dependencies = [
('datafile', '0001_initial'),
('valuation', '0001_initial'),
]
operations = [
migrations.CreateModel(
name='DataLoad',
fields=[
('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
('datafile', models.ManyToManyField(related_name='dataloads', to='datafile.DataFile')),
('valuation', models.ForeignKey(on_delete=django.db.models.deletion.PROTECT, related_name='dataloads', to='valuation.Valuation', verbose_name='valuation')),
],
options={
'verbose_name': 'data_load',
'verbose_name_plural': 'data_load',
'db_table': 'loader"."data_load',
},
),
migrations.CreateModel(
name='DataLoadFile',
fields=[
('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
('datafile', models.ForeignKey(on_delete=django.db.models.deletion.PROTECT, related_name='+', to='datafile.DataFile', verbose_name='data file')),
('dataload', models.ForeignKey(on_delete=django.db.models.deletion.PROTECT, related_name='datafiles', to='dataload.DataLoad', verbose_name='data load')),
],
options={
'verbose_name': 'data load file',
'verbose_name_plural': 'data load files',
'db_table': 'loader"."data_load_files',
},
),
]
And the migration runs pretty fine, without any error.
Went through Google and I tried on Django Users group but I couldn't have any answer about it. Thus, I do believe it could be a bug.
Change History (9)
comment:1 by , 5 years ago
| Cc: | added |
|---|---|
| Severity: | Normal → Release blocker |
| Summary: | Migration problems when using intermediate (through) table on many-to-many relationship → Migration crash when adding inline foreign key to different schema on PostgreSQL. |
| Triage Stage: | Unreviewed → Accepted |
comment:2 by , 5 years ago
Everything works properly when we remove db_table = '"loader"."data_load_files" from DataLoadFile.Meta.
comment:3 by , 5 years ago
This should be solveable by making sure the schema name is included in the SET CONSTRAINT as well.
That would result in SET CONSTRAINTS "loader"."data_load_valuation_id_9af1ae91_fk_valuation_id" IMMEDIATE.
comment:5 by , 4 years ago
| Owner: | changed from to |
|---|---|
| Status: | new → assigned |
| Triage Stage: | Accepted → Ready for checkin |
comment:9 by , 4 years ago
Replying to Mariusz Felisiak <felisiak.mariusz@…>:
In 453a5bf3:
Thank you guys for your help with that!
Cheers,
Rodrigo
Note:
See TracTickets
for help on using tickets.
Tentatively accepted, but I'm not sure if this is a supported use case, see #6148.
Regression in 22ce5d0031bd795ade081394043833e82046016c.
Crashing SQL:
ALTER TABLE "loader"."data_load" ADD COLUMN "valuation_id" integer NOT NULL CONSTRAINT "data_load_valuation_id_9af1ae91_fk_valuation_id" REFERENCES "core"."valuation"("id") DEFERRABLE INITIALLY DEFERRED; SET CONSTRAINTS "data_load_valuation_id_9af1ae91_fk_valuation_id" IMMEDIATE