Squashed migrations may try to add existing constraint if there was RunPython between create and alter some-model migrations

[anonymous]

I squashed a lot of migrations, some with RunPython functions. The squashed migration needed some fixing to work. At some point of the squashed migration there was a "CreateModel" operation that created some model with relation to User:

('user', models.ForeignKey(blank=True, to_field='id', to=settings.AUTH_USER_MODEL, null=True)),

But as the models got edited there was a migration (rather pointless, caused by editable=False):

field=models.ForeignKey(blank=True, null=True, to_field='id', to=settings.AUTH_USER_MODEL, editable=False),

In between there was a RunPython which quite likely prevented squashing those two migrations into one. Running such migrations ended up with:

 File "/var/www/buku/virtualenvs/1405079302/lib/python3.4/site-packages/django/db/backends/utils.py", line 66, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: constraint "buku_vendor_offerredir_user_id_75e4400f46095260_fk_auth_user_id" for relation "buku_vendor_offerredirect" already exists

The SQL it was trying to run was:

ALTER TABLE "buku_vendor_offerredirect" ADD CONSTRAINT buku_vendor_offerredir_user_id_75e4400f46095260_fk_auth_user_id FOREIGN KEY ("user_id") REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED []

It would be handy if that "self.cursor.execute(sql, params)" from django/db/backends/utils.py also printed the SQL it was trying to run so it would be easier to find the operation in migration (or make exception more verbose).

I had like 4 such cases. Some quite serious like removing null=True and adding unique=True

[Tim Graham]

I am not exactly clear on what the problem is (the duplicate constraint and/or the issue about SQL being printed -- if these are separate issues they belong in separate tickets). It would be quite helpful if you could add a test for Django's test suite that demonstrates the issue. Without that, it may be difficult to reproduce the issue unless you provide the minimal steps to reproduce it.

[Piotr Maliński]

The problem is with the constraint being re-added. I'll try to provide a migration flow to reproduce it.

[Piotr Maliński]

Steps to reproduce:

• create model like:

class Foo(models.Model):
    user = models.ForeignKey('auth.User')

• make migrations and migrate
• create empty migration and use RunPython that will have a function (can be blank) for example:

def foo(apps, schema_editor):
    pass

• migrate it and then edit the model to for example (change to a field with constraint):

class Foo(models.Model):
    user = models.ForeignKey('auth.User', editable=False)

• makemigrations and migrate the change
• squash migrations
• try running tests so that squashed migration will be applied to a clean database. You should get a django.db.utils.ProgrammingError - constraint exists.

[Tim Graham]

Thanks, I could reproduce this using PostgreSQL but not SQLite (no FK support there yet per #14204).

[Andrew Godwin]

This is reproduceable without the RunPython stuff - you just need to run the operations sequentially:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations
from django.conf import settings


def foo(apps, schema_editor):
    pass


class Migration(migrations.Migration):

    dependencies = [
        migrations.swappable_dependency(settings.AUTH_USER_MODEL),
    ]

    operations = [
        migrations.CreateModel(
            name='Foo',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('user', models.ForeignKey(to=settings.AUTH_USER_MODEL)),
            ],
            options={
            },
            bases=(models.Model,),
        ),
        migrations.AlterField(
            model_name='foo',
            name='user',
            field=models.ForeignKey(editable=False, to=settings.AUTH_USER_MODEL),
        ),
    ]

[Andrew Godwin <andrew@…>]

In c06e124b5e73bd99c6ec563c64fd50d6a45457d8:

Fixed #23091: CreateModel and AddField were clashing with deferred SQL

[Andrew Godwin <andrew@…>]

In a918c60c378b575ca52ebcfae977056b8bc5a4de:

[1.7.x] Fixed #23091: CreateModel and AddField were clashing with deferred SQL