[Django] #36645: PostgreSQL migration regression: `InternalError: cannot drop column id of table ... because other objects depend on it`

[Django]

#36645: PostgreSQL migration regression: `InternalError: cannot drop column id of
table ... because other objects depend on it`
-------------------------------------------+------------------------------
Reporter: Adam Johnson | Owner: Adam Johnson
Type: Bug | Status: assigned
Component: Migrations | Version: 6.0
Severity: Release blocker | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------------+------------------------------
When testing a client project on Django 6.0, its migrations fail to run,
crashing with an exception like:

{{{
django.db.utils.InternalError: cannot drop column id of table library_book
because other objects depend on it
DETAIL: constraint ratings_rating_book_id_9d017958_fk_library_book_id on
table ratings_rating depends on column id of table library_book
HINT: Use DROP ... CASCADE to drop the dependent objects too.
}}}

The exact conditions:

1. PostgreSQL (tested on 15 and reproduced on 18)
2. A model that has been migrated from vanilla to use multi-table
inheritance
3. A model in another app that FK's to the first model, with its migration
dependency on the first models' first migration.

Reproducer repository incoming.
--
Ticket URL: <https://code.djangoproject.com/ticket/36645>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36645: PostgreSQL migration regression: `InternalError: cannot drop column id of
table ... because other objects depend on it`

---------------------------------+----------------------------------------

Reporter: Adam Johnson | Owner: Adam Johnson
Type: Bug | Status: assigned
Component: Migrations | Version: 6.0

Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

---------------------------------+----------------------------------------
Description changed by Adam Johnson:

Old description:

> When testing a client project on Django 6.0, its migrations fail to run,
> crashing with an exception like:
>
> {{{
> django.db.utils.InternalError: cannot drop column id of table
> library_book because other objects depend on it
> DETAIL: constraint ratings_rating_book_id_9d017958_fk_library_book_id on
> table ratings_rating depends on column id of table library_book
> HINT: Use DROP ... CASCADE to drop the dependent objects too.
> }}}
>
> The exact conditions:
>
> 1. PostgreSQL (tested on 15 and reproduced on 18)
> 2. A model that has been migrated from vanilla to use multi-table
> inheritance
> 3. A model in another app that FK's to the first model, with its
> migration dependency on the first models' first migration.
>
> Reproducer repository incoming.

New description:

When testing a client project on Django 6.0, its migrations fail to run,
crashing with an exception like:

{{{
django.db.utils.InternalError: cannot drop column id of table library_book
because other objects depend on it
DETAIL: constraint ratings_rating_book_id_9d017958_fk_library_book_id on
table ratings_rating depends on column id of table library_book
HINT: Use DROP ... CASCADE to drop the dependent objects too.
}}}

The exact conditions:

1. PostgreSQL (tested on 15 and reproduced on 18)
2. A model that has been migrated from vanilla to use multi-table
inheritance
3. A model in another app that FK's to the first model, with its migration
dependency on the first models' first migration.

Reproduction repository: https://github.com/adamchainz/django-ticket-36645

--
--
Ticket URL: <https://code.djangoproject.com/ticket/36645#comment:1>

[Django]

#36645: PostgreSQL migration regression: `InternalError: cannot drop column id of
table ... because other objects depend on it`
---------------------------------+----------------------------------------
Reporter: Adam Johnson | Owner: Adam Johnson
Type: Bug | Status: assigned
Component: Migrations | Version: 6.0
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
---------------------------------+----------------------------------------

Comment (by Adam Johnson):

Bisects to 2a5aca38bbb37f6e7590ac6e68912bfbefb17dae.
--
Ticket URL: <https://code.djangoproject.com/ticket/36645#comment:2>

[Django]

#36645: PostgreSQL migration regression: `InternalError: cannot drop column id of
table ... because other objects depend on it`
---------------------------------+----------------------------------------
Reporter: Adam Johnson | Owner: Adam Johnson

Type: Bug | Status: closed
Component: Migrations | Version: 6.0
Severity: Release blocker | Resolution: wontfix

Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
---------------------------------+----------------------------------------

Changes (by Adam Johnson):

* resolution: => wontfix
* status: assigned => closed

Comment:

Aha, this is indeed a feature, not a bug.

It turns out that the migrations on Django 5.2 would drop the foreign key
constraint, due to the cascade. Now PostgreSQL doesn't allow that,
preserving data integrity.

With my reproducer example, if I check the table *before* running the
final migration, I see:

{{{
# \d+ ratings_rating
Table
"public.ratings_rating"
Column | Type | Collation | Nullable | Default
| Storage | Compression | Stats target | Description
---------+--------+-----------+----------+----------------------------------+---------+-------------+--------------+-------------
id | bigint | | not null | generated by default as
identity | plain | | |
book_id | bigint | | not null |
| plain | | |
Indexes:
"ratings_rating_pkey" PRIMARY KEY, btree (id)
"ratings_rating_book_id_9d017958" btree (book_id)
Foreign-key constraints:
"ratings_rating_book_id_9d017958_fk_library_book_id" FOREIGN KEY
(book_id) REFERENCES library_book(id) DEFERRABLE INITIALLY DEFERRED
Access method: heap
}}}

Then if I run the migration:

{{{
$ ./manage.py migrate
Operations to perform:
Apply all migrations: library, ratings
Running migrations:
Applying library.0002_readable_remove_book_id_book_readable_ptr... OK
}}}

…then, boom! The foreign key constraint is gone:
{{{
# \d+ ratings_rating
Table
"public.ratings_rating"
Column | Type | Collation | Nullable | Default
| Storage | Compression | Stats target | Description
---------+--------+-----------+----------+----------------------------------+---------+-------------+--------------+-------------
id | bigint | | not null | generated by default as
identity | plain | | |
book_id | bigint | | not null |
| plain | | |
Indexes:
"ratings_rating_pkey" PRIMARY KEY, btree (id)
"ratings_rating_book_id_9d017958" btree (book_id)
Access method: heap
}}}

Moreover, I've run the same verification on my client's production app and
found missing foreign key constraints. Time to roll the sleeves up and get
to re-adding them…

So I think we can mark this as "working as intended".

One other note: I found that the migration pattern didn't work on SQLite
with Django 5.2. That makes sense because it never used `CASCADE`.
--
Ticket URL: <https://code.djangoproject.com/ticket/36645#comment:3>

[Django]

#36645: PostgreSQL migration regression: `InternalError: cannot drop column id of
table ... because other objects depend on it`
---------------------------------+----------------------------------------
Reporter: Adam Johnson | Owner: Adam Johnson
Type: Bug | Status: closed
Component: Migrations | Version: 6.0
Severity: Release blocker | Resolution: wontfix
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
---------------------------------+----------------------------------------

Comment (by minusf):

Thank you for your great analysis. I have just run into this while trying
to migrate from django5 to django6 and unfortunately the fix probably
won't be that easy because the model in question has been since removed
completely and there is no "code" to fix, only the migration history... :/
--
Ticket URL: <https://code.djangoproject.com/ticket/36645#comment:4>