Feature request: Use QuerySets to write trigger functions

[wesleykendall]

I have a proof of concept that uses querysets to construct functions for triggers. This helps alleviate the need to write raw SQL, especially for denormalizations.

Here's what it looks like in practice:

class Post(models.Model):
    num_comments = models.IntegerField(default=0)

class Comment(models.Model):
    post = ForeignKey(Post)

    class Meta:
        triggers = [
            pgtrigger.Trigger(
                name="add_num_comments",
                when=pgtrigger.After,
                operation=pgtrigger.Insert,
                func=pgtrigger.Query(
                    Post.objects.filter(id=pgtrigger.F("new__post")).update,
                    args=[models.F("num_comments") + 1)]
                )
            )
        ]

Above is a very basic example of adding to the number of comments for a post each time a comment is created. This assumes that the comment model is append only.

In the above is a special Query object that takes a function and the args/kwargs to the function. It's constructed this way to avoid querysets being evaluated.

Say that we want to allow comments to be deleted or updated. This example can be extended to allow conditions. Here we are only showing the func attribute, which now generates two possible queries:

func=[
   pgtrigger.Query(
       Post.objects.filter(id=pgtrigger.F("new__post")).update,
       args=[models.F("num_comments") + 1)],
       condition=pgtrigger.Q(tg_op__in=[pgtrigger.Insert, pgtrigger.Update])
    ),
    pgtrigger.Query(
        Post.objects.filter(id=pgtrigger.F("old__post")).update,
        args=[models.F("num_comments") - 1)],
        condition=pgtrigger.Q(tg_op__in=[pgtrigger.Delete, pgtrigger.Update])
    )
]

In the above, we increment and decrement on an update for a post to handle the scenario of the foreign key changing. We conditionally execute the queries based on the TG_OP operation variable that is available in the trigger. The rendered SQL looks like the example in #26

How Does It Work?

It captures the SQL of the query and uses this SQL for the function of the trigger. When capturing the SQL, it ignores executing it.

This is a somewhat hacky approach, but it works for update, delete, and a few other queries. It becomes problematic for querysets that fetch data (get_or_create). In my proof of concept, I just catch ProgrammingErrors when this happens and stop capturing SQL.

Limitations

As mentioned, this approach only works for queries that can be expressed as one line of SQL. A user cannot use a function that queries data, processes it in Python, and then updates it. If this happens, the SQL generation only captures the first SQL statement. It would be up to the user to ensure they've tested the generated SQL for their trigger, and useful error messages aren't thrown in this current proof of concept.

Although I could catch errors if anyone tries to perform get or any function that fetches data, this is problematic because functions like create fetch the result too. create is a primary method users would want to use here.

So I'm stuck between a rock and a hard place on trying to make something useful that generates SQL while removing the ability to shoot oneself in the foot.

How you can help

Any feedback would be appreciated! I wanted to put this in an issue first and get a response from the community before making a PR. I can always put it in as an experimental feature that also might be broken without a proper major version bump

[pgcd]

Disclaimer: I just started using the library (which is great, BTW) with zero previous experience with triggers, so I assume I'm missing something.

I've tried to use aggregates (using a trigger to do something conceptually similar to "update a basket total when an item price changes", extended to multiple fields etc) and as far as I can tell, aggregates only work on the rows that already exist in the table - so the computed values in NEW wouldn't be included.
If I'm correct, would there be a way of integrating more complex logic than a value increase/decrease in the definition of the trigger, or would we still end up having to write raw SQL?