[Django] #36598: Django migrations is unable to find remove constraints from non-public schema for Postgres

[Django]

#36598: Django migrations is unable to find remove constraints from non-public
schema for Postgres
-------------------------------------+-------------------------------------
Reporter: Salaah Amin | Type:
| Uncategorized
Status: new | Component: Database
| layer (models, ORM)
Version: 4.2 | Severity: Normal
Keywords: postgres, db, orm | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36598>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36598: Django migrations is unable to find remove constraints from non-public
schema for Postgres
-------------------------------------+-------------------------------------

Reporter: Salaah Amin | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:

Keywords: postgres, db, orm | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Salaah Amin):

* type: Uncategorized => Bug

--
Ticket URL: <https://code.djangoproject.com/ticket/36598#comment:1>

[Django]

#36598: Django migrations is unable to find remove constraints from non-public
schema for Postgres
-------------------------------------+-------------------------------------
Reporter: Salaah Amin | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, db, orm | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tanishq):

Hi Salaah,

I’ve looked into the problem with migrations failing to remove constraints
from the {{{analytics.occupancy}}}table in a non-public schema, and it
seems related
to
{{{django.db.backends.postgresql.introspection.DatabaseIntrospection.get_constraints}}}
not detecting constraints due to
the{{{pg_catalog.pg_table_is_visible(cl.oid)}}}filter, despite
the{{{search_path=public,analytics}}}setting
in{{{DATABASES["OPTIONS"]}}}.The{{{ValueError: Found wrong number (0) of
constraints}}}suggests 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_constraints}}}to either:
Use the schema from{{{db_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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36598#comment:2>

[Django]

#36598: Django migrations is unable to find remove constraints from non-public
schema for Postgres
-------------------------------------+-------------------------------------
Reporter: Salaah Amin | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, db, orm | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36598#comment:3>

[Django]

#36598: Django migrations is unable to find remove constraints from non-public
schema for Postgres
-------------------------------------+-------------------------------------
Reporter: Salaah Amin | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, db, orm | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36598#comment:4>

[Django]

#36598: Django migrations is unable to find remove constraints from non-public
schema for Postgres
-------------------------------------+-------------------------------------
Reporter: Salaah Amin | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, db, orm | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tanishq):

Hi Salaah,

I agree with your finding that the issue is due to
{{{pg_table_is_visible}}}filtering out the{{{analytics}}} schema, and
your suggestion to adjust the {{{search_path}}} during migrations makes
sense. Dynamically setting {{{search_path}}}
(e.g., SET {{{search_path}}}TO {{{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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36598#comment:5>

[Django]

#36598: Django migrations is unable to find remove constraints from non-public
schema for Postgres
-------------------------------------+-------------------------------------
Reporter: Salaah Amin | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, db, orm | Triage Stage:
| Unreviewed

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tanishq):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36598#comment:6>

[Django]

#36598: Django migrations is unable to find remove constraints from non-public
schema for Postgres
-------------------------------------+-------------------------------------

Reporter: Salaah Amin | Owner: Salaah
| Amin
Type: Bug | Status: assigned

Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, db, orm | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tanishq):

* owner: (none) => Salaah Amin
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36598#comment:7>

[Django]

#36598: Django migrations is unable to find remove constraints from non-public
schema for Postgres
-------------------------------------+-------------------------------------
Reporter: Salaah Amin | Owner: Salaah
| Amin

Type: Bug | Status: closed

Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Normal | Resolution: duplicate

Keywords: postgres, db, orm | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* resolution: => duplicate
* status: assigned => closed

Comment:

I believe this might be a duplicate of #32495
Note that official support for database schemas is discussed in #6148
--
Ticket URL: <https://code.djangoproject.com/ticket/36598#comment:8>