Wrapping a primary key retype in SeparateDatabaseAndState results in a different schema than running it alone

[Gavin Wahl]

If a SeparateDatabaseAndState has an operation on an unrelated model before an AlterField that retypes a primary key, the foreign keys to that primary key keep their old type. The same operations without SeparateDatabaseAndState retype them. I reproduced using postgres, on Django 5.2, 6.0, 6.1, and main.

Start with these models:

class Parent(models.Model):
    id = models.AutoField(primary_key=True)

class Child(models.Model):
    parent = models.ForeignKey(Parent, models.CASCADE)

class Confounder(models.Model):
    pass

Generate an initial migration. It's the standard CreateModels so I won't quote it here. Then change Parent.id to a BigAutoField and add a new field to Confounder. makemigrations gives:

class Migration(migrations.Migration):
    dependencies = [
        ('app', '0001_initial'),
    ]
    operations = [
        migrations.AddField(
            model_name='confounder',
            name='note',
            field=models.TextField(default=None),
            preserve_default=False,
        ),
        migrations.AlterField(
            model_name='parent',
            name='id',
            field=models.BigAutoField(primary_key=True, serialize=False),
        ),
    ]

Edit this migration to wrap the operations in a SeparateDatabaseAndState, using the same operations for both database_operations and state_operations. By definition, SeparateDatabaseAndState with the same database and state should be the same as the operations alone:

OPERATIONS = [
    migrations.AddField(
        model_name='confounder',
        name='note',
        field=models.TextField(default=None),
        preserve_default=False,
    ),
    migrations.AlterField(
        model_name='parent',
        name='id',
        field=models.BigAutoField(primary_key=True, serialize=False),
    ),
]
class Migration(migrations.Migration):
    dependencies = [
        ('app', '0001_initial'),
    ]
    operations = [
        migrations.SeparateDatabaseAndState(
            state_operations=OPERATIONS,
            database_operations=OPERATIONS,
        ),
    ]

$ ./manage.py migrate app
  Applying app.0001_initial... OK
  Applying app.0002_confounder_note_alter_parent_id... OK

This is supposed to change the type of Parent.id and all foreign keys to it, but it didn't. In the database, app_child.parent_id is type integer while app_parent.id is type bigint. psql output, trimmed:

\d app_child
  Column   |  Type   
-----------+---------
 parent_id | integer

\d app_parent
 Column    |  Type  
-----------+--------
 id        | bigint

manage.py sqlmigrate app 0002 does produce the correct SQL, but it's not what migrate actually did:

--
-- Custom state/database change combination
--
ALTER TABLE "app_confounder" ADD COLUMN "note" text NOT NULL;
SET CONSTRAINTS "app_child_parent_id_5690a59f_fk" IMMEDIATE; ALTER TABLE "app_child" DROP CONSTRAINT "app_child_parent_id_5690a59f_fk";
ALTER TABLE "app_parent" ALTER COLUMN "id" TYPE bigint USING "id"::bigint;
ALTER SEQUENCE IF EXISTS "app_parent_id_seq" AS bigint;
ALTER TABLE "app_child" ALTER COLUMN "parent_id" TYPE bigint USING "parent_id"::bigint;
ALTER TABLE "app_child" ADD CONSTRAINT "app_child_parent_id_5690a59f_fk" FOREIGN KEY ("parent_id") REFERENCES "app_parent" ("id") DEFERRABLE INITIALLY DEFERRED;

Running the same migration without SeparateDatabaseAndState produces the correct schema, with types matching:

\d app_child
  Column   |  Type   
-----------+---------
 parent_id | bigint

\d app_parent
 Column    |  Type  
-----------+--------
 id        | bigint

Note that the Confounder operation actually does matter. If the PK AlterField operation goes first, it works as expected. An operation on Parent or Child instead of Confounder doesn't trigger it. It also matters that 0001 and 0002 are run together. If you apply 0001 and then 0002 in separate migrate commands, you get the correct types.

It also isn't postgres-specific, you can reproduce on sqlite with a CharField primary key going from max_length=10 to 20. app_child.parent_id will stay varchar(10).

I found this while fuzz testing a migration extension that manages database views with Django migrations. The fuzzer makes random model and view changes, runs makemigrations, and applies the migrations in every order their dependencies allow, forwards and backwards. It uses an oracle that compares the schema in the Postgres catalogs after each migrate with the schema the generated models describe, including column types. After one migration was unapplied and applied again, a foreign key column came out integer while the primary key it points at was bigint. The extension copies the same pattern SeparateDatabaseAndState.database_forwards uses, so I tried to find a minimal repro with SeparateDatabaseAndState alone.