#29345: Migrations that recreate constraints can fail on PostgreSQL if table is
not
in public schema
---------------------------------------+------------------------
Reporter: Olav Morken | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 2.0
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
---------------------------------------+------------------------
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',
},
}
}
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29345>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.2035eacb67fcca4e2e476a5f6b1ac4a8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
