Postgres backend introspection and the 'public' schema

[Nicholas FitzRoy-Dale]

Hi there,

The Postgres introspection backend's get_constraints function will only pull in constraints from the 'public' schema* (https://github.com/django/django/blob/stable/1.11.x/django/db/backends/postgresql/introspection.py#L186). This has the effect that calling get_constraints() for a database where the default schema is not named 'public' will return no constraints. This in turn means that database migrations which affect foreign keys will fail (because foreign key constraints are deleted and then recreated during a migration, but no foreign keys are found to delete, so keys with duplicate names are created, which postgres considers an error).

Because the name 'public' is hardcoded, this is not affected by search_path.

I'm new to Django internals, so am wondering what is appropriate here and would appreciate opinions. Options as I see them:

1. Remove the constraint on 'public' in that function and just return all constraints. The introspection code already limits the list of constraints to a particular table. Database migration code assumes that it can remove and re-create foreign keys at will, so hiding keys from it based on schema seems like an invitation for further problems. But perhaps I'm missing something here.

2. Support specifying the default schema to the introspection code, via a mechanism similar to (or actually by using) psycopg2's search_path option. Then this schema name could be specified in the introspection query.

3. Don't change anything and require everyone to use the 'public' schema.

4. Something else.

Any comments appreciated!

Nicholas

* Schemas: https://www.postgresql.org/docs/9.6/static/ddl-schemas.html . I find the use of the word "schema" here a bit confusing. Internally Postgres calls these "namespaces":
https://www.postgresql.org/docs/9.6/static/catalog-pg-namespace.html

[Tim Graham]

Here's are related tickets:
https://code.djangoproject.com/ticket/22673 - inspectdb doesn't support database schema on postgresql with name different of "public"
https://code.djangoproject.com/ticket/6148 - Add generic support for database schemas

[Nicholas FitzRoy-Dale]

Thanks for those. From those I deduce that the goal is to implement full schema support incrementally, but not to pretend that they are supported at the moment.

I wonder if it's worth adding a note to that effect to the documentation? There are lots of blog posts and queries on sites like Stack Overflow and Hacker News about using Django with Postgres schemas, and the advice being given (things like "use search paths") is dangerous, since it will work in most common cases but not others.