[Django] #37368: `check_constraints` changes constraint deferral for immediate constraints in for PostgreSQL and Oracle

[Django]

#37368: `check_constraints` changes constraint deferral for immediate constraints
in for PostgreSQL and Oracle
-------------------------------------+-------------------------------------
Reporter: Samuel Searles- | Type: Bug
Bryant | Component: Database
Status: new | layer (models, ORM)
Version: 6.1 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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'...
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/37368>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#37368: `check_constraints` changes constraint deferral for immediate constraints
in for PostgreSQL and Oracle
-------------------------------------+-------------------------------------

Reporter: Samuel Searles- | Owner: (none)
Bryant |
Type: Bug | Status: new
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:

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

Description changed by Samuel Searles-Bryant:

Old description:

New description:

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

{{{#!console

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

[Django]

#37368: `check_constraints` changes constraint deferral for immediate constraints
in for PostgreSQL and Oracle
-------------------------------------+-------------------------------------
Reporter: Samuel Searles- | Owner: (none)
Bryant |
Type: Bug | Status: new
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted

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

Changes (by Lily):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/37368#comment:2>

[Django]

#37368: `check_constraints` changes constraint deferral for immediate constraints
in for PostgreSQL and Oracle
-------------------------------------+-------------------------------------

Reporter: Samuel Searles- | Owner: Samuel
Bryant | Searles-Bryant
Type: Bug | Status: assigned

Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Samuel Searles-Bryant):

* owner: (none) => Samuel Searles-Bryant
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/37368#comment:3>

[Django]

#37368: `check_constraints` changes constraint deferral for immediate constraints
in for PostgreSQL and Oracle
-------------------------------------+-------------------------------------
Reporter: Samuel Searles- | Owner: Samuel
Bryant | Searles-Bryant
Type: Bug | Status: assigned
Component: Database layer | Version: 6.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Samuel Searles-Bryant):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/37368#comment:4>