`check_constraints` changes constraint deferral for immediate constraints in for PostgreSQL and Oracle

[Samuel Searles-Bryant]

The check_constraints methods on the PostgresSQL and Oracle database wrappers check constraints by setting all deferrable constraints to IMMEDIATE and then all deferrable constraints to DEFERRED. If a deferrable constraint started as immediate, the constraint will be deferred after check_constraints runs.

This is demonstrated with a test, e.g. for PostgreSQL:

import unittest

from psycopg import sql

from backends import models
from django.db import IntegrityError, connection, transaction
from django.test import TransactionTestCase


@unittest.skipUnless(connection.vendor == "postgresql", "PostgreSQL tests")
class CheckConstraintTests(TransactionTestCase):
    available_apps = ["backends"]

    def test_immediate_constraint_remains_immediate(self):
        with transaction.atomic():
            # There is currently no way to create a model with an immediate
            # constraint throuigh the ORM
            # (see https://github.com/django/new-features/issues/212).
            # Therefore, we must manually set the constraint to immediate.
            with connection.cursor() as cursor:
                cursor.execute("""
                    SELECT conname FROM pg_constraint
                    WHERE
                        contype = 'f'
                        AND conrelid::regclass::text = 'backends_book';
                    """)
                (constraint_name,) = cursor.fetchone()
                cursor.execute(
                    sql.SQL("SET CONSTRAINTS {} IMMEDIATE").format(
                        sql.Identifier(constraint_name)
                    )
                )

            connection.check_constraints()

            # Creating an object with an invalid foreign key immediately raises an
            # exception.
            with self.assertRaisesRegex(
                IntegrityError,
                expected_regex='insert or update on table "backends_book" violates foreign key constraint',
            ):
                models.Book.objects.create(author_id=-1)

This test fails:

$ ./runtests.py --settings test_postgresql backends.postgresql.test_check_constraints
Testing against Django installed in '/Users/sam.searles-bryant/.local/share/samueljsb/django/django' with up to 12 processes
Found 1 test(s).
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
F
======================================================================
FAIL: test_immediate_constraint_remains_immediate (backends.postgresql.test_check_constraints.CheckConstraintTests.test_immediate_constraint_remains_immediate)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/sam.searles-bryant/.local/share/samueljsb/django/tests/backends/postgresql/test_check_constraints.py", line 45, in test_immediate_constraint_remains_immediate
    with self.assertRaisesRegex(
         ~~~~~~~~~~~~~~~~~~~~~~^
        IntegrityError,
        ^^^^^^^^^^^^^^^
        expected_regex='insert or update on table "backends_book" violates foreign key constraint',
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ):
    ^
AssertionError: IntegrityError not raised

----------------------------------------------------------------------
Ran 1 test in 0.015s

FAILED (failures=1)
Destroying test database for alias 'default'...

[Samuel Searles-Bryant]

This change will cause check_constraints to fail on PostgreSQL (and maybe Oracle) if it is called outwith a transaction. That's reasonable: checking constraints in auto-commit mode doesn't make sense, but it is a change in behaviour.

We (Lily and I) think that check_constraints ought to raise a TransactionManagementError if called outwith a transaction in general. We would implement that by adding a check to the base class and calling super()... in the subclasses. We haven't done that in the PR because it probably needs a deprecation cycle, option to disable the behaviour temporarily, etc. Should we? Or can it be a separate change?

If we did introduce an option to allow calling check_constraints outwith a transaction and deprecated it, this bugfix would still cause that to fail for PostgreSQL. We could avoid that by using the old (buggy) behaviour if the option is enabled, which is not ideal ("be buggy" is an uncomfortable option to provide) but is backwards compatible.

(comment also posted on the PR: ​https://github.com/django/django/pull/22027#issuecomment-6082172748)