[Django] #31622: Support CREATE CONSTRAINT TRIGGER constraints in postgres
Django
-------------------------------------+-------------------------------------
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.
Django
-------------------------------------+-------------------------------------
Reporter: Jerome Leclanche | Owner: (none)
Type: New feature | Status: closed
Component: contrib.postgres | Version: 3.1
Severity: Normal | Resolution: wontfix
Keywords: constraints, | Triage Stage:
postgres, triggers | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
Comment:
Thanks for this ticket, however IMO it's quite niche and will be useful
only for functions with really complicated logic (How would you describe
them in Django?).
It sounds like a third-party package is the best way to proceed.
--
Ticket URL: <https://code.djangoproject.com/ticket/31622#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Jerome Leclanche | Owner: (none)
Type: New feature | Status: closed
Component: contrib.postgres | Version: 3.1
Severity: Normal | Resolution: wontfix
Keywords: constraints, | Triage Stage:
postgres, triggers | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
Replying to [comment:2 Jerome Leclanche]:
> Are you dead set on a wontfix?
[https://docs.djangoproject.com/en/stable/internals/contributing/triaging-
tickets/#closing-tickets Follow triaging guidelines with regards to
wontfix tickets.] You can start a discussion on DevelopersMailingList if
you don't agree.
--
Ticket URL: <https://code.djangoproject.com/ticket/31622#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Jerome Leclanche | Owner: (none)
Type: New feature | Status: closed
Component: contrib.postgres | Version: 3.1
Severity: Normal | Resolution: wontfix
Keywords: constraints, | Triage Stage:
postgres, triggers | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jerome Leclanche):
Here's a butchered version of the code which pulls in actual postgres
functions used as constraints. The functions are versioned in migrations.
https://gist.github.com/jleclanche/07961a08b7741cb804b1f14177d19a1f
It's primitive, doesn't support updating (has to drop+recreate), and it
hacks into `constraints = []` so that it's picked up by the migration
auto-detector. But hey, it works. And incidentally it made me realize
there is no way to add new operations to be auto-detected by the migration
engine.
--
Ticket URL: <https://code.djangoproject.com/ticket/31622#comment:5>