`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'...