#31622: Support CREATE CONSTRAINT TRIGGER constraints in postgres
-------------------------------------+-------------------------------------
Reporter: Jerome | Owner: (none)
Leclanche |
Type: New | Status: new
feature |
Component: | Version: 3.1
contrib.postgres | Keywords: constraints,
Severity: Normal | postgres, triggers
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Context: My ultimate goal is to be able to define a function (with its
body) and make it a table constraint in Django. Sqlalchemy has no support
for either of those, FWIW.
In that light, I wanted to start by implementing constraint triggers, and
for now just have them reference pre-existing functions implemented
separately, using `Func(function="function_name")`..
I have some initial working code here:
https://github.com/jleclanche/django/commit/postgres-trigger-constraints
Tested that code in my app with creating/dropping constraints, constraint
trigger equality, deferrability (using Django 3.1's new Deferrable object
from the new deferrable index impl). I also tested conditions using
RawSQL(), couldn't figure out a way of referencing my conditions directly
otherwise.
I'd like to help upstream this but I don't have a lot of free time. I
could use the help.
Example usage from my app:
{{{
class Account(models.Model):
class Meta:
constraints = [
ConstraintTrigger(
name="update_full_account_codes_trigger",
events=[TriggerEvent.INSERT,
TriggerEvent.UPDATE, TriggerEvent.DELETE],
condition=RawSQL("pg_trigger_depth() <
%s", (1,)),
function=models.Func(function="update_full_account_codes"),
),
ConstraintTrigger(
name="check_account_type_trigger",
events=[TriggerEvent.INSERT,
TriggerEvent.UPDATE],
condition=RawSQL("pg_trigger_depth() <
%s", (1,)),
function=models.Func(function="check_account_type"),
),
]
}}}
Generates the following migration:
{{{
operations = [
migrations.AddConstraint(
model_name='account',
constraint=financica.contrib.constraints.ConstraintTrigger(condition=django.db.models.expressions.RawSQL('pg_trigger_depth()
< %s', (1,)), events=('INSERT', 'UPDATE', 'DELETE'),
function=django.db.models.expressions.Func(function='update_full_account_codes'),
name='update_full_account_codes_trigger'),
),
migrations.AddConstraint(
model_name='account',
constraint=financica.contrib.constraints.ConstraintTrigger(condition=django.db.models.expressions.RawSQL('pg_trigger_depth()
< %s', (1,)), events=('INSERT', 'UPDATE'),
function=django.db.models.expressions.Func(function='check_account_type'),
name='check_account_type_trigger'),
),
]
}}}
References:
- CREATE CONSTRAINT TRIGGER syntax: https://www.postgresql.org/docs/9.0
/sql-createconstraint.html
- CREATE CONSTRAINT generic syntax: https://www.postgresql.org/docs/12
/sql-createtrigger.html
--
Ticket URL: <https://code.djangoproject.com/ticket/31622>
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 on the web visit
https://groups.google.com/d/msgid/django-updates/053.0d2fdd9a1a033c74517cab1dab2aaac5%40djangoproject.com.
