Squashing migrations from unique=True to unique=False to UniqueConstraint produces irreversible migration on Postgres

[Jacob Walls]

Rhymes a bit with #31503, just in the reverse direction.

• Create a model with a unique=True field, create a migration. I used URLField.
• Create an empty migration, e.g with migrations.RunSQL(sql="SELECT 1", reverse_sql=""). (This will prevent the next AlterField from optimizing out when squashing. There are likely other possible reproducers without this step.)
• Alter the field from step 1 to have unique=False, create a migration
• Add a UniqueConstraint to the model that involves just that field, create a migration
• Squash the four migrations
• Migrate forward
• Migrate to zero, with or without removing the other migrations or the replaced attribute

Result:

  Unapplying polls.0001_initial_squashed_0004_menu_unique_site...Traceback (most recent call last):
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.DuplicateTable: relation "polls_menu_site_61d71486_like" already exists


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/jwalls/prj/night/manage.py", line 22, in <module>
    main()
  File "/Users/jwalls/prj/night/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/migrations/executor.py", line 141, in migrate
    state = self._migrate_all_backwards(plan, full_plan, fake=fake)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/migrations/executor.py", line 219, in _migrate_all_backwards
    self.unapply_migration(states[migration], migration, fake=fake)
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/migrations/executor.py", line 279, in unapply_migration
    state = migration.unapply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/migrations/migration.py", line 193, in unapply
    operation.database_backwards(
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/migrations/operations/fields.py", line 240, in database_backwards
    self.database_forwards(app_label, schema_editor, from_state, to_state)
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/migrations/operations/fields.py", line 235, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/backends/base/schema.py", line 831, in alter_field
    self._alter_field(
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/backends/postgresql/schema.py", line 304, in _alter_field
    self.execute(like_index_statement)
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/backends/postgresql/schema.py", line 48, in execute
    return super().execute(sql, None)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/jwalls/release/lib/python3.12/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: relation "polls_menu_site_61d71486_like" already exists

*
failing squashed migration:

# Generated by Django 4.2.13 on 2024-05-21 00:59

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = []

    operations = [
        migrations.CreateModel(
            name="Menu",
            fields=[
                (
                    "id",
                    models.BigAutoField(
                        auto_created=True,
                        primary_key=True,
                        serialize=False,
                        verbose_name="ID",
                    ),
                ),
                ("site", models.URLField(unique=True)),
            ],
        ),
        migrations.RunSQL(
            sql="SELECT 1",
            reverse_sql="",
        ),
        migrations.AlterField(
            model_name="menu",
            name="site",
            field=models.URLField(),
        ),
        migrations.AddConstraint(
            model_name="menu",
            constraint=models.UniqueConstraint(fields=("site",), name="unique_site"),
        ),
    ]

My final model looked like:

from django.db import models

class Menu(models.Model):
    site = models.URLField()

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=["site"], name="unique_site")
        ]

Tested on postgres 14.3.2

[Sarah Boyce]

Hi Jacob, thank you for this. I tried to replicate on main with SQLite and couldn't, so this might be postgres specific (I will try out again later).
I was wondering if you have seen #28646 and whether you think this is related or this issue would be resolved with that ticket?

[Sarah Boyce]

Replicated on Postgres, accepting 👍

[Jacob Walls]

Thanks for the link to #28646. It's related, but most of the discussion there centers around the "boolean logic" cited in that ticket's OP. I tried the various patches, and they don't fix my report because they just shuffle the logic fathoming the booleans db_index and unique on old_field and new_fields, whereas the problem here seems to be that one of those values is wrong.

#26805 inspired me to check SlugField. I can't reproduce with SlugField, leading me to wonder if it's something to do with URLField's different implementation of deconstruct()?

When using SlugField, and breaking on the comment "Added an index? ..." in db/backends/postgresql/schema.py, I get:

(Pdb) new_field.db_index
True

versus with URLField:

(Pdb) new_field.db_index
False

[Jacob Walls]

Sorry, still forming first impressions here, but just piping up to clarify that my last comment mostly barks up the wrong tree: the field values for db_index and whatnot look fine. And on a closer read of #28646, it strikes me as mostly focused on SlugField, which *doesn't* have the problem presented here. So I think having separate tickets makes sense.

[Sarah Boyce]

Confirmed the patch wouldn't fix it and agree let's track this separately, thank you for taking a look 👍

[Jacob Walls]

​PR

[Sarah Boyce <42296566+sarahboyce@…>]

In 99f23ea:

Fixed #35469 -- Removed deferred SQL to create index removed by AlterField operation.

[Sarah Boyce <42296566+sarahboyce@…>]

In db349fc4:

[5.1.x] Fixed #35469 -- Removed deferred SQL to create index removed by AlterField operation.

Backport of 99f23eaabd8da653f046dc1d19f5008c030a4f79 from main.