Opened 9 months ago

Last modified 9 months ago

#29345 new Bug

Migrations that recreate constraints can fail on PostgreSQL if table is not in public schema

Reported by: Olav Morken Owned by: nobody
Component: Migrations Version: 2.0
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


The django.db.backends.postgresql.introspection.get_constraints(...)-function contains an SQL expression that assumes that all tables are in the public schema:

The last few lines read:

            JOIN pg_class AS cl ON c.conrelid = cl.oid
            JOIN pg_namespace AS ns ON cl.relnamespace = ns.oid
            WHERE ns.nspname = %s AND cl.relname = %s
        """, ["public", table_name])

The result is that it fails to find any constraints for tables that are not in the public schema. This either leaves us with two identical constraints, when it fails to delete the old, or results in an exception when it subsequently tries to recreate a constraint that it should have deleted:

django.db.utils.ProgrammingError: constraint "migration_app_testref_test_id_bce0807a_fk" for relation "migration_app_testref" already exists

A simple fix is to not check for the public schema, but instead check visibility using pg_catalog.pg_table_is_visible(cl.oid):

            JOIN pg_class AS cl ON c.conrelid = cl.oid
            WHERE cl.relname = %s AND pg_catalog.pg_table_is_visible(cl.oid)
        """, ["public", table_name])

This appears to give the correct result, even when there are multiple tables with the same name in the database.

I have attached a migration file and models file for a simple app migration_app that reproduces this problem. To be able to reproduce it, you must use a custom schema search path when connecting to PostgreSQL, either by setting it as the default for the role, or by specifying it in the connection options:

    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'migration_test',
        'HOST': 'localhost',
        'USER': 'postgres',
        'OPTIONS': {
            'options': '-c search_path=testschema,public',

Attachments (2) (2.2 KB) - added by Olav Morken 9 months ago.
Database migrations for migration_app (156 bytes) - added by Olav Morken 9 months ago. for migration_app

Download all attachments as: .zip

Change History (3)

Changed 9 months ago by Olav Morken

Attachment: added

Database migrations for migration_app

Changed 9 months ago by Olav Morken

Attachment: added for migration_app

comment:1 Changed 9 months ago by Tim Graham

Triage Stage: UnreviewedAccepted
Note: See TracTickets for help on using tickets.
Back to Top