Django migrations is unable to find remove constraints from non-public schema for Postgres

[Salaah Amin]

I have a table in another schema (analytics) which is created and managed by Django. The following is a snippet of the table:

class Meta:
        db_table = '"analytics"."occupancy"'
        indexes = [models.Index(fields=("business_id", "-dt"))]
        constraints = [
            models.CheckConstraint(
                check=models.Q(occupancy_tables__gte=0.0)
                & models.Q(occupancy_tables___lte=1.0),
                name="occupancy_tables_between_0_and_1",
            ),
            models.CheckConstraint(
                check=models.Q(occupancy_covers__gte=0.0)
                & models.Q(occupancy_covers__lte=1.0),
                name="occupancy_covers_between_0_and_1",
            ),
        ]

If i create a new migration that results in these constraints being removed, it breaks.

Here is the traceback:

Traceback (most recent call last):
  File "..../manage.py", line 25, in <module>
    main()
  File "..../manage.py", line 21, in main
    execute_from_command_line(sys.argv)
  File "....django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "....django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "....django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "....django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "....django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "....django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "....django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "....django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "....django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "....django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "....django/db/migrations/operations/models.py", line 659, in database_forwards
    alter_together(
  File "....django/db/backends/base/schema.py", line 554, in alter_unique_together
    self._delete_composed_index(
  File "....django/db/backends/base/schema.py", line 611, in _delete_composed_index
    raise ValueError(
ValueError: Found wrong number (0) of constraints for "analytics"."occupancy"(business_id, dt)

Taking a look at the code, the problem seems to be caused by `django.lib.backends.postgresql.introspection.DatabaseIntrospection.get_constraints.

The query adds "pg_catalog.pg_table_is_visible(cl.oid)". When I ran that query in pgadmin, removing that condition, the missing constraints appeared.

Looking at the postgres docs, pg_table_is_visible searches the search_path. However, I have added the schema to the search path in the db setup in settings.py:

DATABASES = {
    ...
    "OPTIONS": {
            "options": "-c search_path=public,analytics",
     },
}

Not sure if this is a misconfiguration on my part (couldn't find anything in Django docs), or if this is a bug/missing feature.
I am able to migrate ok when creating new tables, just deleting constraints seem to be a problem.

Note: I am currently using Django version 4.2. But looking at the source code, this (potential) issue may also be in v5.2.

[Tanishq]

Hi Salaah,

I’ve looked into the problem with migrations failing to remove constraints from the analytics.occupancytable in a non-public schema, and it seems related
to django.db.backends.postgresql.introspection.DatabaseIntrospection.get_constraints not detecting constraints due to thepg_catalog.pg_table_is_visible(cl.oid)filter, despite thesearch_path=public,analyticssetting inDATABASES["OPTIONS"].TheValueError: Found wrong number (0) of constraintssuggests that the introspection query isn’t picking up schema-qualified constraints during removal operations. This could be a bug or a missing feature, as creation works fine. A potential fix might involve modifying get_constraintsto either:
Use the schema fromdb_table(e.g., "analytics"."occupancy") explicitly in the query, or Adjust the SQL to include the schema in the JOIN condition, bypassing the visibility filter when the schema is specified.

[Salaah Amin]

Hi Tanishq,
I don't think that would work.
In fact, I ran two queries:

SELECT
	c.conname,
	array(
		SELECT attname
		FROM unnest(c.conkey) WITH ORDINALITY cols(colid, arridx)
		JOIN pg_attribute AS ca ON cols.colid = ca.attnum
		WHERE ca.attrelid = c.conrelid
		ORDER BY cols.arridx
	),
	c.contype,
	(SELECT fkc.relname || '.' || fka.attname
	FROM pg_attribute AS fka
	JOIN pg_class AS fkc ON fka.attrelid = fkc.oid
	WHERE fka.attrelid = c.confrelid AND fka.attnum = c.confkey[1]),
	cl.reloptions
FROM pg_constraint AS c
JOIN pg_class AS cl ON c.conrelid = cl.oid
WHERE cl.relname like '%occupancy' AND pg_catalog.pg_table_is_visible(cl.oid);

Here I kept the pg_table_is_visible constraint in and it didn't return anything.

However, it works when I run:

SELECT
	c.conname,
	array(
		SELECT attname
		FROM unnest(c.conkey) WITH ORDINALITY cols(colid, arridx)
		JOIN pg_attribute AS ca ON cols.colid = ca.attnum
		WHERE ca.attrelid = c.conrelid
		ORDER BY cols.arridx
	),
	c.contype,
	(SELECT fkc.relname || '.' || fka.attname
	FROM pg_attribute AS fka
	JOIN pg_class AS fkc ON fka.attrelid = fkc.oid
	WHERE fka.attrelid = c.confrelid AND fka.attnum = c.confkey[1]),
	cl.reloptions
FROM pg_constraint AS c
JOIN pg_class AS cl ON c.conrelid = cl.oid
WHERE cl.relname like '%occupancy';

So the issue really is pg_table_is_visible.

[Salaah Amin]

I think I found the issue.

When I run:

select pg_catalog.pg_table_is_visible(<the model oid>)

I get false.

When I run:

show search_path

I get a few things, but I notice analytics isn't there.

If I update my search_path using set search_path to ..., and then run select pg_catalog.pg_table_is_visible(<the model oid>) I get true.

Now, I'm not entirely sure if there are any consequences to adding all schemas to the search path. But I assume it should be fine to add it just before running migrations so that it only affects the user's session when they are running the actual migration.

If you agree to this solution, let me know (and mark the ticket accordingly), and I'd be happy to apply the fix.

[Tanishq]

Hi Salaah,

I agree with your finding that the issue is due to pg_table_is_visiblefiltering out theanalytics schema, and your suggestion to adjust the search_path during migrations makes sense. Dynamically setting search_path
(e.g., SET search_pathTO public,analytics) in the migration session should resolve the constraint visibility problem without affecting other operations, as long as it’s scoped to the migration context.

So, I say go ahead with the solution.

[Sarah Boyce]

I believe this might be a duplicate of #32495
Note that official support for database schemas is discussed in #6148