[Django] #29345: Migrations that recreate constraints can fail on PostgreSQL if table is not in public schema
Django
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.
Django
in public schema
Reporter: Olav Morken | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 2.0
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 Olav Morken):
* Attachment "0001_initial.py" added.
Database migrations for `migration_app`
Django
in public schema
Reporter: Olav Morken | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 2.0
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 Olav Morken):
* Attachment "models.py" added.
`models.py` for migration_app
Django
in public schema
Reporter: Olav Morken | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 2.0
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/29345#comment:1>
Django
in public schema
Reporter: Olav Morken | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 2.0
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Emre Kartoglu):
* Attachment "2_fk_constraints.png" added.
Django
in public schema
Reporter: Olav Morken | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 2.0
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Emre Kartoglu):
I was trying to reproduce this issue. Although I failed to reproduce it
using the files attached by Olav, I did notice another potential issue in
the process: Django migration created 2 foreign key constraints both
pointing from the same table&column to the same table&column. The attached
file `2_fk_constraints.png` shows what I mean. Should we create a ticket
for this? I wonder if solving 29345 would solve this too, or vice versa.
--
Ticket URL: <https://code.djangoproject.com/ticket/29345#comment:2>
Django
in public schema
Reporter: Olav Morken | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 2.0
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by dperetti):
Hit by this as well, and was about to file the exact same bug report.
Obviously any query referring to the "public" schema in the postgresql
backend code is bound to cause issues to people who use multiple
connections and fancy search_paths.
The only workaround I can think of is to not use a schema named "public"
at all.
--
Ticket URL: <https://code.djangoproject.com/ticket/29345#comment:3>
Django
in public schema
Reporter: Olav Morken | Owner: nobody
Component: Migrations | Version: 2.0
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* status: new => closed
* resolution: => duplicate
Comment:
Duplicate of #30644, fixed by c6581a40be3bb4c1e13861f0adbb3fe01f09107f.
--
Ticket URL: <https://code.djangoproject.com/ticket/29345#comment:4>