Support CREATE CONSTRAINT TRIGGER constraints in postgres.

[Jerome Leclanche]

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

[Mariusz Felisiak]

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.

[Jerome Leclanche]

Replying to felixxm:

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.

Well you don't have to describe them in Django, you can reference them by name (as I'm doing now with my code). The create constraint logic need only be there for migration purposes.

However, it is my goal to create a system which can create functions, and that would eventually support fairly arbitrary languages (support plpython3 etc; i've toyed with this concept in sqlalchemy with redshift modules before); so the idea of describing the function *body* is a non-starter: It's just text, and should be treated as such.

It's less work for me so I don't really care, but … I think this is very valuable FWIW, and falls in line with the work on expanding constraints. Are you dead set on a wontfix?

[Tom Forbes]

This is supported by all our database backends with very similar syntax - I think there's more of a case to be made if this was a generic "trigger" functionality that worked everywhere rather than postgres specific. I could have used this feature once before, and while it's definitely niche it's sometimes an annoying requirement (especially when integrating with other databases/legacy systems). There are also a non-trivial amount of ​relevant hits on Github.

So I think I would be +1 of including this if it wasn't postgres specific. Maybe a discussion on the mailing list is warranted?

[Mariusz Felisiak]

Replying to Jerome Leclanche:

Are you dead set on a wontfix?

​Follow triaging guidelines with regards to wontfix tickets. You can start a discussion on DevelopersMailingList if you don't agree.

[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.