#36173: POSTGRES and SQLite migrations constantly DROP and ADD check constraint
without changes to code - possibly due to use of CONCAT function (Django
5.x)
-------------------------------------+-------------------------------------
Reporter: erchenstein | Type: Bug
Status: new | Component:
| Migrations
Version: 5.1 | Severity: Normal
Keywords: Migrations, | Triage Stage:
CheckConstraint, CONCAT | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
I have a check constraint for a model that gets constantly dropped and
added during migrations, even though there were no changes to the check
constraint.
The bug occurs with Postgres and SQLite using Django 5.0 and Django 5.1.
The CheckConstraint checks whether the checksum of an identifier is
correct via a modulo operation. The identifier is saved in a CharField. If
the identifier starts with a number greater than 0, the formula is:
{{{
checksum = 97 – (int(identifier) % 97)
}}}
If the identifier starts with 0, a 2 has to be added to the identifier:
{{{
97 – (int("2" + identifier) % 97)
}}}
I use the ORM's Concat function in the check constraint to add the 2 at
the beginning of the identifier in the latter case.
- Originally, I thought the error might occur because Django 5.0 used the
SQL Concat function instead of || to concatenate strings. Therefore, I
updated to Django 5.1, since || is used in >= 5.1.
- I also tested in a test database, from which I deleted all identifiers
starting with "0", to check whether the error would persist with a check
constraint that didn' use Concat. It did **not** occur without using
Concat.
- Because the original constraint was constructed by a function to reduce
verbose code, I also tested whether a verbose/hardcoded version would lead
to the error. It did.
- I used a test model that has only the identifier field and the
CheckConstraint to see whether the error would occur. It did.
Checkconstraint:
{{{
identifier_checksum_check = (Q(
IntegerFieldExact(
ExpressionWrapper(
Value(97) - Mod(
Cast(
Substr(F('identifier'), 1, 9),
models.BigIntegerField()
),
Value(97),
output_field=models.BigIntegerField()
),
output_field=models.BigIntegerField()
),
Cast(
Substr(F('identifier'), 10, 2),
models.BigIntegerField()
)
)
) | Q(
IntegerFieldExact(
ExpressionWrapper(
Value(97) - Mod(
Cast(
Concat(2, Substr(F('identifier'), 1, 9),
output_field=models.CharField()),
models.BigIntegerField()
),
Value(97),
output_field=models.BigIntegerField()
),
output_field=models.BigIntegerField()
),
Cast(
Substr(F('identifier'), 10, 2),
models.BigIntegerField()
)
)
))
class TestModel(models.Model):
identifier = models.CharField(max_length=11)
class Meta:
constraints = [
models.CheckConstraint(
check=identifier_checksum_check,
name="identifier_control_checksum_check",
violation_error_message=_("The checksum is not correct!")
),
]
}}}
**SQL:**
Postgres check constraint generated by Django 5.0 using CONCAT
{{{
CHECK ((97 - mod("substring"(niss::text, 1, 9)::bigint, 97::bigint)) =
"substring"(niss::text, 10, 2)::bigint OR (97 - mod(concat(2::text,
"substring"(niss::text, 1, 9))::bigint, 97::bigint)) =
"substring"(niss::text, 10, 2)::bigint)
}}}
Postgres check constraint generated by Django 5.1 using ||
{{{
CHECK ((97 - mod("substring"(niss::text, 1, 9)::bigint, 97::bigint)) =
"substring"(niss::text, 10, 2)::bigint OR (97 - mod((COALESCE(2::text,
''::text) || COALESCE("substring"(niss::text, 1, 9), ''::text))::bigint,
97::bigint)) = "substring"(niss::text, 10, 2)::bigint)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36173>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070194e19b886c-6c4574b5-f5f8-4be3-bcbe-84817868f541-000000%40eu-central-1.amazonses.com.
