Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#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 Mariusz Felisiak, 4 years ago

Cc: Simon Charette 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

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

comment:2 by Mariusz Felisiak, 4 years ago

Everything works properly when we remove db_table = '"loader"."data_load_files" from DataLoadFile.Meta.

comment:3 by Simon Charette, 4 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:4 by Simon Charette, 4 years ago

Has patch: set

​PR

Last edited 4 years ago by Mariusz Felisiak (previous) (diff)

comment:5 by Mariusz Felisiak, 4 years ago

Owner: changed from nobody to Simon Charette
Status: new → assigned
Triage Stage: Accepted → Ready for checkin

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: → fixed
Status: assigned → closed

In 2e8941b6:

Fixed #31735 -- Fixed migrations crash on namespaced inline FK addition on PostgreSQL.

The namespace of the constraint must be included when making the
constraint immediate.

Regression in 22ce5d0031bd795ade081394043833e82046016c.

Thanks Rodrigo Estevao for the report.

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In b8cb14e8:

[3.1.x] Fixed #31735 -- Fixed migrations crash on namespaced inline FK addition on PostgreSQL.

The namespace of the constraint must be included when making the
constraint immediate.

Regression in 22ce5d0031bd795ade081394043833e82046016c.

Thanks Rodrigo Estevao for the report.

Backport of 2e8941b6f90e65ffad3f07083b8de59e8ed29767 from master

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In 453a5bf3:

[3.0.x] Fixed #31735 -- Fixed migrations crash on namespaced inline FK addition on PostgreSQL.

The namespace of the constraint must be included when making the
constraint immediate.

Regression in 22ce5d0031bd795ade081394043833e82046016c.

Thanks Rodrigo Estevao for the report.

Backport of 2e8941b6f90e65ffad3f07083b8de59e8ed29767 from master

in reply to:  8 comment:9 by Rodrigo Estevao, 4 years ago

Replying to Mariusz Felisiak <felisiak.mariusz@…>:

In 453a5bf3:

[3.0.x] Fixed #31735 -- Fixed migrations crash on namespaced inline FK addition on PostgreSQL.

The namespace of the constraint must be included when making the
constraint immediate.

Regression in 22ce5d0031bd795ade081394043833e82046016c.

Thanks Rodrigo Estevao for the report.

Backport of 2e8941b6f90e65ffad3f07083b8de59e8ed29767 from master

Thank you guys for your help with that!

Cheers,
Rodrigo

Note: See TracTickets for help on using tickets.
Back to Top