Opened 7 years ago
Closed 2 years ago
#29345 closed Bug (duplicate)
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 |
Description
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', }, } }
Attachments (3)
Change History (7)
by , 7 years ago
Attachment: | 0001_initial.py added |
---|
comment:1 by , 7 years ago
Triage Stage: | Unreviewed → Accepted |
---|
by , 6 years ago
Attachment: | 2_fk_constraints.png added |
---|
comment:2 by , 6 years ago
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.
comment:3 by , 5 years ago
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.
comment:4 by , 2 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Duplicate of #30644, fixed by c6581a40be3bb4c1e13861f0adbb3fe01f09107f.
Database migrations for
migration_app