id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
37368	`check_constraints` changes constraint deferral for immediate constraints in for PostgreSQL and Oracle	Samuel Searles-Bryant	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:

{{{#!python
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:

{{{#!console
$ ./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'...
}}}"	Bug	assigned	Database layer (models, ORM)	6.1	Normal				Accepted	1	0	0	0	0	0