id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29345	Migrations that recreate constraints can fail on PostgreSQL if table is not in public schema	Olav Morken	nobody	"The `django.db.backends.postgresql.introspection.get_constraints(...)`-function contains an SQL expression that assumes that all tables are in the `public` schema:
https://github.com/django/django/blob/2.0.4/django/db/backends/postgresql/introspection.py#L178-L201

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:

{{{
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'migration_test',
        'HOST': 'localhost',
        'USER': 'postgres',
        'OPTIONS': {
            'options': '-c search_path=testschema,public',
        },
    }
}
}}}
"	Bug	closed	Migrations	2.0	Normal	duplicate			Accepted	0	0	0	0	0	0