migrations.DeleteModel locks DB when it has ForeignKey field

[taylor-cedar]

Describe the bug When dropping a table with a foreign key (pointing to another table) Postgres will lock the foreign table as well as the table being dropped. This causes all queries to be blocked by the operation.

Here is an article describing the problem: http://db-oriented.com/2018/04/18/excessive-locking-when-dropping-a-table-in-11g/

3 Options for a fix here:

1. Throw an error when a table contains foreign keys (forcing foreign keys to be dropped in a different operation)
2. Rewrite the SQL operation in DeleteModel to drop foreign keys first
3. Add lock_timeout to DROP TABLE operation

To Reproduce

1. What model did you have?

class ModelA(models.Model):
  pass

class ModelB(models.Model):
  related_a = models.ForeignKey(ModelA)

2. How did you change the model? Removed ModelB
3. What migration were generated?

class Migration(migrations.Migration):	
    dependencies = [...]	

    operations = [	
        migrations.DeleteModel(name="ModelB"),
    ]

4. What SQL was executed?

DROP TABLE model_b CASCADE;

5. What issue did you get? Locked model_b table (expected) and locked model_a for 8 minutes

Expected behavior Not lock model_a or throw a lock timeout after 2 seconds (ZERO_DOWNTIME_MIGRATIONS_LOCK_TIMEOUT)

Versions:

• Postgres: 10.6
• Python: 3.6
• Django: 2.2.13
• django-pg-zero-downtime-migrations library: 0.8

[tbicr]

Thanks for reporting, pretty interesting case, I like idea to rewrite sql to do table dropping more safe (don't lock 2 tables for long time) - in case if no issues with different FK options.

[taylor-cedar]

It looks like you need access to system tables to query foreign keys dynamically. Is that something we should expect? I assume some users that are running migrations may not have that permission. Thoughts?

[tbicr]

There are 2 states:

1. django migration state - representation of db state in django that can be translated to db real state
2. postgres real state, there are really can be issues access restrictions, but django also use some introspection features (https://github.com/django/django/blob/master/django/db/backends/postgresql/introspection.py#L177) that can be required for normal django work

So access to system tables looks that django migrations requires for normal work.

Do you have issue with system tables restrictions?

[taylor-cedar]

Make sense. It looks like normal Django migrations make use of introspection. We can get use that get_constraints method, unless you have any objections.