CODEWITHSUNDEEP
pythondjangopostgresqldjango-migrations
takje
takje

Reputation: 2800

Django rename field and create new one with the same name returns psycopg2 error: DuplicateTable: relation already exists

I have two Django (foreign key) fields - FieldA and FieldB - referring to the same class but being different objects. Then I wanted to rename FieldB to FieldC. I renamed this in one migration (automatic detection).

Then I realised I actually need a new field with the same name as the old FieldB (also foreign key to the same class). Therefore I created a second migration to add a new field: FieldB. Since I just renamed the other one I assumed that this would not give any issues in the DB.

Locally I develop on an SQLite DB and this works fine. When I pushed it to our Postgres DB, this returned an error.

Model class

class ModelClass(Model):
    field_a: ForeignClassA = models.ForeignKey(ForeignClassA, on_delete=models.SET_NULL, blank=True, null=True, related_name='FieldA')
    # this one should be renamed to field_c after which I create another field with the same name and properties.
    field_b: ForeignClassA = models.ForeignKey(ForeignClassA, on_delete=models.SET_NULL, blank=True, null=True, related_name='FieldB')

Migration one: rename

operations = [
        migrations.RenameField(
            model_name='modelname',
            old_name='FieldB',
            new_name='FieldC',
        ),]

Migration two: add field

operations = [
        migrations.AddField(
            model_name='modelname',
            name='FieldB',
            field=models.ForeignKey(blank=True, null=True, on_delete=django.db.models.deletion.SET_NULL, related_name='FieldB', to='app.othermodel'),
        ),]

When I run this migration I get the following error

  Applying app.xxx1_previous... OK
  Applying app.xxx2_rename_field... OK
  Applying app.xxx3_add_field...Traceback (most recent call last):
  File "/app/.heroku/python/lib/python3.10/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.DuplicateTable: relation "app_modelname_fieldB_id_8c448c6a" already exists

Normally this column should have been deleted and replaced with a new column with the new name. I found this issue describing a similar issue in Postgress and now I wonder if this is a bug in Django. Could it be that the cleanup from the rename is not done correctly?

EDIT 1

After a closer inspection of the Postgres DB I can see that after the rename I still have two columns in my table: FieldA_id and FieldB_id, while I would expect to have FieldA_id and FieldC_id. Obviously this creates an issue if I subsequently try to add FieldB again.

Could it be that Postgres (or the Django controller) does not rename this column for some reason?

EDIT 2

I inspected the SQL query to the Postgres DB. The following SQL is produced:

BEGIN;
--
-- Rename field field_b on modelclass to field_c
--
SET CONSTRAINTS "app_modelclass_field_b_id_8c448c6a_fk_otherapp_otherclass_id" IMMEDIATE; ALTER TABLE "app_modelclass" DROP CONSTRAINT "app_modelclass_field_b_id_8c448c6a_fk_otherapp_otherclass_id";
ALTER TABLE "app_modelclass" RENAME COLUMN "field_b_id" TO "field_c_id";
ALTER TABLE "app_modelclass" ADD CONSTRAINT "app_modelclass_field_c_id_9f82ac2c_fk_otherapp_otherclass_id" FOREIGN KEY ("field_c_id") REFERENCES "otherapp_otherclass" ("id") DEFERRABLE INITIALLY DEFERRED;
COMMIT;

This rename however is seems to be only executed partially since the next step reports an issue, but the migration succeeds.

However, when I manually run this SQL query, the command succeeds and the upgrade is partly successful. In summary:

  1. The SQL query works
  2. While running the SQL, the column name is updated
  3. While running the SQL, the constraint is updated
  4. Still the next step complains about a reference to this rename. Still clueless at the moment what this might be.

Upvotes: 0

Views: 2593

Answers (1)

takje
takje

Reputation: 2800

After a long search down the SQL rabbit hole, I found out that the rename migration for PostgresQL does not drop the old index.

When I wanted to create a new field, it tried to create a new index with the same name as the old index (which wasn't removed).

A simple way to avoid it is to 'sandwich' the rename with two alter operations. One to unset the index and one to set it back afterwards.

operations = [
        migrations.AlterField(
            model_name='modelclass',
            name='field_b',
            field=models.ForeignKey(blank=True, db_index=False, null=True, on_delete=django.db.models.deletion.SET_NULL,
                                    to='otherapp.otherclass'),
        ),
        migrations.RenameField(
            model_name='modelclass',
            old_name='field_b',
            new_name='field_c',
        ),
        migrations.AlterField(
            model_name='modelclass',
            name='field_c',
            field=models.ForeignKey(blank=True, db_index=True, null=True, on_delete=django.db.models.deletion.SET_NULL,
                                    to='otherapp.otherclass'),
        ),
    ]

I reported this issue also on the Django bug tracker after which they closed it since there exists a duplicate from 7 years ago.

Upvotes: 7

Related Questions