opus10/django-pgtrigger

Write Postgres triggers for your Django models

django-pgtrigger's Issues

Tuttorial issues

Looks like at a lot of your examples and at tutorial https://wesleykendall.github.io/django-pgtrigger-tutorial/#setup name attribute is not set, its confusing cause its required attribute and when I tried some examples from tutorial I ran into issue

File "/Users/XXX/venv/lib/python3.8/site-packages/pgtrigger/core.py", line 424, in __init__ raise ValueError('Trigger must have "name" attribute') ValueError: Trigger must have "name" attribute

Before Insert trigger still violates not-null constraint

Hello, I'm using pg-trigger to update a column in Table 2 (Workspace) before I insert a row in Table 1 (Task):

@pgtrigger.register(
    pgtrigger.Trigger(
        name="increment_workspace_highest_task_number",
        when=pgtrigger.Before,
        operation=pgtrigger.Insert,
        func="""
        DECLARE 
        highest_number  INTEGER;
        BEGIN
            SELECT workspace_workspace.highest_task_number INTO highest_number
            FROM workspace_workspace
            WHERE workspace_workspace.id = NEW.workspace_id;
            UPDATE "workspace_workspace" AS workspace
            SET "highest_task_number" = highest_number + 1
            FROM workspace_workspace
            WHERE workspace.id = NEW.workspace_id;
            NEW.number = highest_number + 1;
            RETURN NEW;
        END;""",
    )
)

I'm doing this operation on a Task table (model) that has a number field (column). I get the following error:

psycopg2.errors.NotNullViolation: null value in column "number" violates not-null constraint

Is there a way to bypass that error other than making the column nullable?

Feature request: Allow just referencing built-in procedures

I'm trying to use django-pgtrigger to apply tsvector_update_trigger [1], a built-in procedure that takes parameters.

Doing so from another procedure is cumbersome if even possible, as it expects me to pass column names for the current table, which doesn't work:

CALL tsvector_update_trigger(
    this_column_name_exists_but_is_not_found,
    'pg_catalog.english',
    this_column_name_is_also_not_found
);

(Disclaimer: I'm aware that I might be missing something here, I'm new to PL/pgSQL, but it seems to me like these procedures expect some kind of "current table" context that is absent when called this way).

So, as far as I can tell, currently, I have to subclass Trigger to support calling a procedure with arguments, or replicate the logic of tsvector_update_trigger in my custom func. Both mean writing and maintaining PL/pgSQL, which is precisely what I'm trying to avoid by using django-pgtrigger (along with any significant SQL).

It would be nice if triggers could also support a procedure argument that could be specified instead of func and used like

pgtrigger.Trigger(
    (...),
    procedure="tsvector_update_trigger(tsv, 'pg_catalog.english', text);",
)

In this case, no new procedure would be generated. Instead, that procedure call would be used in its place, producing output like

(...)
    FOR EACH ROW EXECUTE FUNCTION
        tsvector_update_trigger(tsv, 'pg_catalog.english', text);
(...)

[1] https://www.postgresql.org/docs/9.5/textsearch-features.html#TEXTSEARCH-UPDATE-TRIGGERS

Error column "tgparentid" does not exist #95

Hello everyone, after upgrading django-pgtrigger 3.4.0 to 4.3.3 I started getting the following error:

django.db.utils.ProgrammingError: column "tgparentid" does not exist
LINE 5:                       tgparentid = 0 AND

This error is being generated from this part of the code: https://django-pgtrigger.readthedocs.io/en/latest/_modules/pgtrigger/installation.html?highlight=tgparentid#

Evaluating the versions, the problem starts from version 4.3.0. Following as described in the tag https://github.com/Opus10/django-pgtrigger/releases/tag/4.3.0 I ran the command python manage.py pgtrigger install but soon got the error.

migration error: cannot alter type of a column used in a trigger definition

More of a question than a bug -- is there a best practice for doing migrations when triggers exist?

Right now I'm just doing a manage pgtrigger uninstall before I manage migrate. I don't like that this leaves a window where triggers are disabled, though, and I don't want to shut down the entire app.

Wondering if anyone else has found a better way for dealing with the issue.

Current trigger running on old DB tables

So, @wesleykendall - I recently came across a pretty nasty problem. Although, fortunately, it's unlikely one will really see it happen in production.

The problem happened when we were running migrations on a somewhat old database. Since that database, many models had been added and updated. Even removed. In this case, there was a new NOT NULL field on a model.

As it happens, one of the migration was a data migration that hadn't been run was on that model, but at that point in time, the field hadn't been added.

So the field was there in the code, but not in the database. But, because we had run partial migrations, on another app, we had installed all the latest and greatest triggers.

One such trigger was via the pghistory library, to track history on that model.

What happened next is this:

The data migration did some change to one row from that table. Immediately, the pghistory based trigger decided to create a historical record of the change in it's table - but alas - that table was based on the latest version of the model, which had a NOT NULL field, but there was no value present, since that field didn't exist yet on the main model.

The solution was to disable triggers before running all the migrations (via pgtriggers.uninstall()).

Of course, this probably only happens for this scenario, where the DB is severely out of date. And no doubt, in those scenarios, it's probably fine to disable triggers before running migrations.

So I don't see this as a severe problem in any way. But perhaps in future versions, there might be some ways to handle this. Or, at any rate, I figured to document the "issue" here, in case someone came across the same problem later on.

It's also likely that with named triggers, it'll be easier to avoid these scenarios. For example, I could have added something like with pgtrigger.disable('sync_model_x'): in the data migration, if I thought it likely to cause this issue.

But most likely, this is not a scenario that will happen often (if at all) in realistic production scenarios.

I thought of posting this in pghistory library, but this might easily also affect other cases. For example, if I had written a trigger that mentioned the new field, but the new field didn't exist when the migration ran. Would end up with a similar error, I'm guessing.

Use for denormalized fields

I planned on using this (awesome) library to cleanup some of my code and avoid some problems caused by denormalizing fields in app code, but I haven't been able to find any clear examples of doing it.
Is there any such example or are you possibly even thinking about a new Trigger class?

Update: I can imagine that #82 might be relevant, but I'm not sure it's exactly the same thing, hence the new issue.

Trigger name is case sensitive

Today all my triggers where getting installed and then inmediately prunned.

This happens if the name of a trigger doesn't have all lowercase letters, as the trigger_id is stored in the database as lowercase, but it is returned with original uppercase letters in the trigger.get_pgid method. As a result, the get_prune_list function in core.py retrieves first all of the installed triggers where the id's keep original uppercase letters and then don't match against the database current trigger id's, as they are stored with a lowercase conversion of the id.

I don't know if this is expected behaviour, but maybe it should be changed, or docummented in the docs (that the name parameter of trigger should be lowercase), as it was a little anoying having to debug source code.

Support for the DECLARE part of the trigger function

When trying to write a trigger function, I have noticed that I have no way to specify the DECLARE part and from what I can see the current code base has some initial support for this functionality, but the def get_declare(self, model) method on the Trigger class , just returns an empty list.

Would it be possible to add full support for the DECLARE section?

register trigger function in separate file

I have a PL/PGSQL trigger function handle_new_row() that I register manually with the CREATE TRIGGER command:

  CREATE TRIGGER mytrigger
    AFTER INSERT
    ON mytable
    FOR EACH ROW
    EXECUTE FUNCTION handle_new_row();

This works fine, but I would like to use pgtrigger to manage it with Django migrations. Is this possible? My trigger function is pretty elaborate so it seems better to leave it in a separate file rather than use the inline approach from the examples. Here is what I've tried:

@pgtrigger.register(
    pgtrigger.Trigger(
        name       = 'handle_new_row',
        operation  = pgtrigger.Insert,
        level      = pgtrigger.Row,
        when       = pgtrigger.After,
        func       = (
                        Path(f'{os.environ["MYAPP"]}/sql/mytrigger.sql')
                          .read_text(encoding  = 'utf-8')
                     )
    )
)
class MyTable(Model):
  ...

but this blows up with:

psycopg2.errors.FunctionExecutedNoReturnStatement:
control reached end of trigger procedure without RETURN
CONTEXT:  PL/pgSQL function pgtrigger_handle_new_row_008cd()

Instructions for upgrading to version 3 and 4

Version 3 integrates django-pgtrigger with the Django migration system, along with several other superficial updates. Version 4 changes the behavior of using triggers in multi-database environments.

TL;DR Most users can do python manage.py makemigrations after upgrading with no problems. If you have triggers registered to third party models or use a mutli-database (or multi-schema) setup, read below for all key updates.

Integration with Django migrations (Version 3)

All triggers now appear in migrations when running python manage.py makemigrations. Triggers from version 2 will appear as new AddTrigger operations and succeed when running migrate even if previously installed. Remember, however, that triggers will be deleted if the migrations are reversed.

Almost all users can simply run python manage.py makemigrations after upgrading. If, however, you have triggers on third-party models or many-to-many default "through" models, use these instructions to migrate them:

1. If you already ran python manage.py makemigrations, delete any new migrations made for these third-party apps.
2. Declare proxy models for the third-party or default many-to-many "through" models, register triggers in the Meta.triggers or those, and call python manage.py makemigrations. See examples in the "Advanced Installation" section of the docs here
3. Warning Declaring proxy models will rename old triggers, leaving them in an orphaned state since they weren't previously managed by migrations. Ensure these old triggers are removed by doing any of the following:
   a. Make a migrations.RunPython operation at the end of your migration or in a new data migration that does call_command("pgtrigger", "prune"). Note that call_command is imported from django.core.management.
   b. OR run python manage.py pgtrigger prune after your deployment is complete
   c. OR set settings.PGTRIGGER_INSTALL_ON_MIGRATE to True for a short period of time in your settings. This will automatically prune those old triggers after deployment, and you can turn this setting back to False later.

If you'd like to keep the legacy installation behavior, set PGTRIGGER_MIGRATIONS to False to turn off trigger migrations and set PGTRIGGER_INSTALL_ON_MIGRATE to True so that triggers are always installed at the end of python manage.py migrate.

Dropping of django-pgconnection dependency (Version 3)

pgtrigger.ignore previously required that django-pgconnection was used to configure the settings.DATABASES setting. django-pgconnection is no longer needed, and settings.DATABASES no longer needs to be wrapped in order
for pgtrigger.ignore to function properly.

New Meta.triggers syntax (Version 2.5)

Version 2.5 introduced the ability to register triggers on your model's Meta.triggers list. User can still use pgtrigger.register to register triggers programmatically.

Adjustments to multi-database support (Version 4)

Previously triggers were installed to databases based on the db_for_write method of the database router. Multiple database support now mimics Django's and follows the allow_migrate method of the database router.

What this means:

1. Triggers are installed on all databases unless allow_migrate says otherwise. This holds true when running migrations and when running any of the management commands such as manage.py pgtrigger install.
2. If you use the legacy settings.PGTRIGGER_INSTALL_ON_MIGRATE to install triggers after migrations, it will install triggers to the same database provided to manage.py migrate or the default database if none is provided.
3. All management commands take a --database argument to target an individual database now.
4. manage.py pgtrigger ls will show UNALLOWED as the installation status of a trigger if it's not allowed to be installed on a database

Multi-schema support (Version 4)

Multi-schema support is now a first-class citizen. See the docs here

Installing trigger fails on custom named tables

Recently I've faced a problem in my project when running ./manage.py pgtrigger install

pgtrigger: Installing set_paid_date trigger for Order table on default database.
Traceback (most recent call last):
  File "$PROJECT_HOME/lib/python3.10/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.SyntaxError: syntax error at or near "Order"
LINE 4:                     BEFORE UPDATE OR INSERT ON Order

Problematic code:

@pgtrigger.register(
    FixedTrigger(
        name="set_paid_date",
        operation=pgtrigger.Update | pgtrigger.Insert,
        when=pgtrigger.Before,
        condition=pgtrigger.Q(new__status="PAID"),
        func="NEW.payment_date = now(); RETURN NEW;",
    ),
)
class Order(models.Model):
    customer = models.ForeignKey(
        "Customer", related_name="orders", on_delete=models.CASCADE
    )
    create_date = models.DateTimeField(auto_now=True)
    payment_date = models.DateTimeField(null=True)
    shipment_date = models.DateTimeField(null=True)
    status = models.CharField(
        max_length=45, choices=OrderStatus.choices, default=OrderStatus.CREATED
    )
    realization_date = models.DateTimeField(null=True)
    products = models.ManyToManyField(
        "Product",
        through="OrderProduct",
        through_fields=("order", "product"),
        related_name="orders",
    )

    class Meta:
        db_table = "Order"

It was kind of curious why excepiton occures, so I dive into library code, and found solution :D
Imo there is an error in custom named tables. In docs I haven't found similar example with custom named table(using custom named tables is not often practice). There is an name conflict in sql order statement and "order" table name;

How to fix:
In function pgtriggers.Trigger.render_trigger SQL query needs double quoted table name. After this fix also faced same error in function pgtriggers.Trigger.render_comment and double-quoting table name fixed problem:

Fixed Trigger class:

from pgtrigger import Trigger


class FixedTrigger(Trigger):
    def render_trigger(self, model):
        """Renders the trigger declaration SQL statement"""
        table = model._meta.db_table
        pgid = self.get_pgid(model)
        return f'''
            DO $$ BEGIN
                CREATE TRIGGER {pgid}
                    {self.when} {self.operation} ON "{table}"
                    {self.referencing or ''}
                    FOR EACH {self.level} {self.render_condition(model)}
                    EXECUTE PROCEDURE {pgid}();
            EXCEPTION
                -- Ignore issues if the trigger already exists
                WHEN duplicate_object THEN null;
            END $$;
        '''

    def render_comment(self, model):
        """Renders the trigger commment SQL statement

        pgtrigger comments the hash of the trigger in order for us to
        determine if the trigger definition has changed
        """
        pgid = self.get_pgid(model)
        hash = self.get_hash(model)
        table = model._meta.db_table
        return f'COMMENT ON TRIGGER {pgid} ON "{table}" IS \'{hash}\''

Using pgtrigger.ignore on partitioned Models with triggers inherited through abstract model

Posting this here in case anyone is interested or happens to bump into the same issue. I'm just describing the problem I've encountered as I have not had time yet to work on a solution (hopefully tomorrow!).

After getting some feedback in issue #70 I was attempting to add a site wide admin action that would allow hard deletes by ignoring the soft _delete trigger. This worked fine on most of my models inheriting triggers through SaasBaseModel (see models below) but failed silently on my Message model.

I am using django-postgres-extra to do table partitioning. My Message model failed to allow deletes within the with pgtrigger.ignore() context.

Models

from django.contrib.gis.db import models

from psqlextra.models import PostgresModel
from psqlextra.models import PostgresPartitionedModel

import pgtrigger

class SaasBaseFields(models.Model):

    class Meta:
        abstract = True

    created = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)
    is_active = models.BooleanField(default=True)
    deactivated = models.DateTimeField(blank=True, null=True)


class SaasBaseModel(
    PostgresModel,
    SaasBaseFields,
    models.Model
):

    class Meta:
        abstract = True
        triggers = [
            pgtrigger.Protect(
                name='read_only',
                operation=pgtrigger.Update,
                condition=pgtrigger.Q(old__created__df=pgtrigger.F('new__created'))
            ),
            pgtrigger.SoftDelete(name='soft_delete', field='is_active'),
            pgtrigger.Trigger(
                name='set_deactivated',
                when=pgtrigger.Before,
                operation=pgtrigger.Update,
                func="NEW.deactivated = NOW(); RETURN NEW;",
                condition=pgtrigger.Q(old__is_active=True, new__is_active=False)
            )
        ]


class SaasBasePartitionedByCreatedModel(
    PostgresPartitionedModel,
    SaasBaseFields,
    models.Model
):

    class Meta:
        abstract = True
        base_manager_name = "objects"
        triggers = [
            pgtrigger.Protect(
                name='read_only',
                operation=pgtrigger.Update,
                condition=pgtrigger.Q(old__created__df=pgtrigger.F('new__created'))
            ),
            pgtrigger.SoftDelete(name='soft_delete', field='is_active'),
            pgtrigger.Trigger(
                name='set_deactivated',
                when=pgtrigger.Before,
                operation=pgtrigger.Update,
                func="NEW.deactivated = NOW(); RETURN NEW;",
                condition=pgtrigger.Q(old__is_active=True, new__is_active=False)
            )
        ]

    # define PartitioningMeta on the child class
    # all partion management is defined in informatical.common.partitioning


class Room(SaasBaseModel):
    name = models.CharField(max_length=128)
    online = models.ManyToManyField(to='users.User', blank=True)


class Message(SaasBasePartitionedByCreatedModel):

    class PartitioningMeta:
        method = PostgresPartitioningMethod.RANGE
        key = ["created"]

    user = models.ForeignKey(to='users.User', on_delete=models.CASCADE)
    room = models.ForeignKey(to=Room, on_delete=models.CASCADE)
    content = models.CharField(max_length=512)

    def __str__(self):
        return f'{self.user.username}: {self.content} [{self.created}]'

Pgtrigger ls output

chat.Room:read_only default INSTALLED ENABLED
chat.Room:soft_delete default INSTALLED ENABLED
chat.Room:set_deactivated default INSTALLED ENABLED
chat.Message:read_only default INSTALLED ENABLED
chat.Message:soft_delete default INSTALLED ENABLED
chat.Message:set_deactivated default INSTALLED ENABLED
users.User:read_only default INSTALLED ENABLED
users.User:soft_delete default INSTALLED ENABLED
users.User:set_deactivated default INSTALLED ENABLED
chat_message_2022_aug_15:pgtrigger_read_only_3c30d default PRUNE ENABLED
chat_message_2022_aug_11:pgtrigger_read_only_3c30d default PRUNE ENABLED
chat_message_2022_aug_13:pgtrigger_set_deactivated_49e4c default PRUNE ENABLED
chat_message_2022_aug_14:pgtrigger_soft_delete_560fe default PRUNE ENABLED
chat_message_2022_aug_16:pgtrigger_set_deactivated_49e4c default PRUNE ENABLED
chat_message_2022_aug_12:pgtrigger_soft_delete_560fe default PRUNE ENABLED
chat_message_default:pgtrigger_set_deactivated_49e4c default PRUNE ENABLED
chat_message_2022_aug_10:pgtrigger_soft_delete_560fe default PRUNE ENABLED
chat_message_2022_aug_14:pgtrigger_read_only_3c30d default PRUNE ENABLED
chat_message_2022_aug_12:pgtrigger_read_only_3c30d default PRUNE ENABLED
chat_message_2022_aug_13:pgtrigger_soft_delete_560fe default PRUNE ENABLED
chat_message_2022_aug_10:pgtrigger_read_only_3c30d default PRUNE ENABLED
chat_message_2022_aug_15:pgtrigger_set_deactivated_49e4c default PRUNE ENABLED
chat_message_2022_aug_16:pgtrigger_soft_delete_560fe default PRUNE ENABLED
chat_message_2022_aug_11:pgtrigger_set_deactivated_49e4c default PRUNE ENABLED
chat_message_2022_aug_16:pgtrigger_read_only_3c30d default PRUNE ENABLED
chat_message_2022_aug_14:pgtrigger_set_deactivated_49e4c default PRUNE ENABLED
chat_message_default:pgtrigger_soft_delete_560fe default PRUNE ENABLED
chat_message_2022_aug_13:pgtrigger_read_only_3c30d default PRUNE ENABLED
chat_message_default:pgtrigger_read_only_3c30d default PRUNE ENABLED
chat_message_2022_aug_15:pgtrigger_soft_delete_560fe default PRUNE ENABLED
chat_message_2022_aug_10:pgtrigger_set_deactivated_49e4c default PRUNE ENABLED
chat_message_2022_aug_12:pgtrigger_set_deactivated_49e4c default PRUNE ENABLED
chat_message_2022_aug_11:pgtrigger_soft_delete_560fe default PRUNE ENABLED

Triggers on a removed model aren't recreated when unapplying the migration that removed the model

This is a pretty niche issue that came up when I was testing #113. That PR also fixes this issue and now adds a test for it too. I've added this issue in case you didn't want to implement #112 but wanted to still track this.

Thanks for all your work on this project!

Triggers in a multi-schema DB setup are pruned incorrectly

Hi, this is going to be a longer description, so please bear with me :)

TL;DR: The query which pgtrigger uses to find all its installed triggers unfortunately responds with fully qualified table names for all other schemas (that are not in the search path).

Let's assume my schemas/tenants within a single database are called foo and bar and that I have a Django app app and a model Customer, with an associated migration and table run for each of these tenants. Lastly, let's have a trigger that runs some logic on row insertion.

Then, as part of pgtrigger's post-migrate action, existing triggers will eventually be queried and checked for possible pruning.
However, for something like:

SET SEARCH_PATH TO foo;
SELECT tgrelid::regclass, tgname, tgenabled FROM pg_trigger WHERE tgname LIKE 'pgtrigger_%';

...the result from Postgres would be along these lines:

tgrelid	tgname	tgenabled
app_customer	pgtrigger_name	O
bar.app_customer	pgtrigger_name	O

A few lines later, _drop_trigger will try to fetch a corresponding model for the DB table bar.api_customer.
However, Django itself is blissfully ignorant of DB schemas, ultimately returning None instead of the proper model.

This results in pgtrigger dropping the triggers in all but the current schema. In a multi-tenant environment like the one I've described, only the last tenant to be migrated would be the only one whose triggers would remain unpruned.

I realise this is quite a handful. I'll gladly provide a more technical example or a PR, if that would be preferable. Thank you!

Changes to trigger conditions require uninstall/reinstall in order to work

Given an existing trigger on a model, updating the condition for that trigger and running a migrate will not update the trigger definition in the database. The steps as far as I can see are:

add trigger to model, with condition A (e.g. Q(old__field="a"))
run ./manage.py install <- trigger now exists in db with condition A
change the trigger condition to B (e.g. Q(old__field="b"))
run ./manage.py pgtrigger ls <- pgtrigger detects that the trigger condition has changed, lists trigger as OUTDATED
run ./manage.py install <- pgtrigger says it has installed the new trigger, but the DB does not get updated

Since the trigger already exists, the install of the new trigger fails, but the generated sql

django-pgtrigger/pgtrigger/core.py

Line 574 in 3ef7e03

CREATE TRIGGER {pgid}

handles the exception, so it fails silently. After the failure though, the trigger hash is still updated to the new value

django-pgtrigger/pgtrigger/core.py

Line 664 in 3ef7e03

cursor.execute(rendered_comment)

, so if you run ls again, it does not think the trigger is outdated.

I wonder if on detecting that the trigger is outdated the first time, the generated trigger sql should first drop the trigger if it exists, and then recreate? Currently the only way to ensure that the trigger is updated is to uninstall it, and then (re)install it.

Note that this showed up through django-pghistory, where adding a new field to a model would not result in changes to that new field being tracked if it was the only field that changed, since the trigger condition only compared the fields that existed before the new one was added (if you remove a field from the model however, that automatically drops the existing trigger, since it referenced the removed field, and so the subsequent install would result in a correctly updated trigger condition).

Detecting migrations for custom get_func implementations

With the changes in version 3 that creates migrations I've encountered an issue with a custom trigger class that implement get_func to generate the trigger (it's for audit and so the trigger uses the fields currently defined on the model). If the fields on the model change then get_func will be different but it won't currently have migrations created (pgtrigger ls will show it as outdated though).

What's the best way to handle this? I've tried the following, which seems to work, but I'm wondering if I should approach it differently or whether it's something pgtrigger should handle internally:

class PatchedEvent(pghistory.trigger.Event):
    def __init__(self, *args, func=None, **kwargs):
        super().__init__(*args, **kwargs)
        self._init_vals = [args, kwargs, func]

    def get_init_vals(self):
        args, kwargs, func = self._init_vals
        if not func:
            func = self.get_func(self.event_model.pgh_tracked_model)
        return [args, {**kwargs, "func": func}]

    def get_func(self, model):
        ...

Documented examples for common denormalizations

I'm principally interested in using this library for denormalizations - thinks like:

class BlogEntry(Model)
    # ...
    num_comments = IntegerField()

class Comment(Model):
    entry = ForeignKey(BlogEntry)
    # ...

Where that num_comments column stores the number of comments, and is updated by a trigger any time a comment is added or deleted.

It would be great if the documentation included some examples of these! Happy to contribute some if I end up figuring this out for my project.

ModuleNotFoundError: No module named 'pgconnection'

The pgconnection module is only used within _inject_pgtrigger_ignore, but is imported top-level. This causes a dependency issue when it's not installed in an existing project.

Thoughts on moving this import into the function itself, so that it is only a required dependency when needed?

Feature request: option to generate migrations for trigger creation

It looks like django-pgtrigger currently works by creating or replacing any missing triggers when the app starts up (correct me if I'm wrong) rather than creating triggers using explicit migration scripts.

This makes me nervous, because of the way it could interact with historical data migrations.

My projects tend to make extensive use of data migrations. I often have a migration that creates a table, then a separate migration that populates that table with a core set of key records - for things like feature flags or other configuration-style data.

When I run ./manage.py migrate in a new environment it may churn through dozens of migrations to create tables, populate them with data, update that data later in the migration sequence and so on.

Here's the problem: if any of those rows are affected by triggers (for denormalized columns for example) I need to execute the historic version of that trigger for that point in the migration history - NOT the most recent version if the trigger which may be incompatible with that historic table layout!

It's not mentioned in the documentation and an initial review of the code didn't spot anything, hence I'm classing this as a feature request: I would really appreciate the option to use this package in a way that can code generate SQL for creating triggers that I can include in a standard Django migration.

Even better: a management command that generates the appropriate migration file for me!

I've only just started looking at this project so it's very possible there is historic context I'm missing about this problem.

Clarification on trigger name length limit

https://github.com/Opus10/django-pgtrigger/blob/master/pgtrigger/core.py#L427 limits trigger names to 43 chars. I am assuming the pgtrigger_ prefix adds another 10 chars , and 5 more for the hash suffix - bringing the total to 58 chars.

Stackoverflow post on max name suggests that 63 char names are allowed.

Can we add comments in the code around the 43 character limit ? (Happy to submit a PR)

'NoneType' object has no attribute '_meta when applying migrations with router

I am using django-database-for-apps for automatically routing apps on schemas.
When migrating I have the following error:

.../python3.9/site-packages/django_database_for_apps/__init__.py", line 15, in db_for_write
    return getattr(settings, 'DATABASE_FOR_APPS', {}).get(model._meta.app_label, getattr(settings, 'DATABASE_FOR_APPS', {}).get('*', 'default'))
AttributeError: 'NoneType' object has no attribute '_meta'

I believe the method pgtrigger.core._get_model should use pgtrigger.core._get_database

Feature request: Use QuerySets to write trigger functions

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

[Question] Is this a usecase for django-pgtrigger?

Hi there,

I need to make a pg trigger and I am using Django 2.2

the use case is like this:

1. i have auto primary key id is integer in the table called teams
2. i have another field called business_identifier that needs to be based on id value like this "TEAM000007"

I wanted to use trigger to autogenerate the values for business_identifier based on the id.

So if I insert a new row, the auto primary key will auto fill in the id and i hope that the business_identifier will also auto update.

I have the sql code for postgres

CREATE OR REPLACE FUNCTION team_insert() RETURNS trigger AS $emp_stamp$
     BEGIN
         NEW.business_identifier:= 'TEAM' || LPAD(NEW.id::text, 6, '0');
         RETURN NEW;
     END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER team_insert BEFORE INSERT OR UPDATE ON teams FOR 
 EACH ROW EXECUTE PROCEDURE team_insert();

How do I do this using django-pgtrigger?

README snippets missing name kwarg

The examples in the README appear to be missing a name kwarg.

Feature request: MySQL support

@tomage leaving a placeholder here for MySQL support since it is actively under development.

So far it looks like MySQL support is going to be close to feature compliance with Postgres, with the following primary notes:

1. Conditions aren't part of the trigger definition. Conditional logic on the old/new rows have to be placed inside the trigger function itself, making it a bit more difficult to make the pgtrigger.Q and pgtrigger.F objects work as well. It should still be possible to make them completely compatible, but the first release of MySQL support may simply not have built-in condition support
2. In order to support pgtrigger.ignore, we may need to make pg-connection compatible with MySQL so that we can hook into SQL generation and set temporary variables. Supporting pgtrigger.ignore will almost certainly not be available in the first release of MySQL support
3. I have not researched much into statement-level triggers for MySQL. It is unclear what type of support will be available for this.
4. MySQL does not support commenting directly on trigger objects, which is how pg-trigger maintains the hashes of installed triggers. We may need to insert a comment into the MySQL trigger definition to maintain its hash to know when the entire definition changes. Or we may need to rework how to detect trigger changes altogether. This is perhaps the most in-the-weeds change we have for MySQL support

It is likely that the support for MySQL and Postgres will be minimal enough that we can keep it in the same library. I am actively planning on renaming this to django-dbtrigger when we have close to full MySQL support.

Please thumbs up this feature request if you want it. It helps me prioritize my time when contributing to open source projects.

Ability to use fields from related models for defining for conditions

Here's an example representation of my models:

class Tag(models.Model):
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)

class Activity(models.Model):
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)
    tags = models.ManyToManyField(Tag, through='TagBinding')

class TagBinding(models.Model):
    tag = models.ForeignKey(Tag)
    activity = models.ForeignKey(Activity)

example picked from this SO Question

And I wanted to make sure tag.user is same as activity.user. So I added the following trigger on TagBinding class

@pgtrigger.register(
    pgtrigger.Protect(
        name='ensure_same_user',
        operation=pgtrigger.Insert | pgtrigger.Update,
        condition=pgtrigger.Q(new__tag__user_id__df=pgtrigger.F('new__activity__user_id'))
        )
    )

Ad when running ./manage.py pgtrigger install, it throws:

django.core.exceptions.FieldDoesNotExist: TagBinding has no field named 'activity__user_id'

Tried dropping the _id from user_id from both F statement and Q statement. No luck.

Is there any way to work around this?

I mainly want to use triggers to ensure that my related data belongs to same user (tried using constraints, but they cannot be configured on related models, so I read that triggers are the solution, but the above errors put a question mark on this approach). Should I do application level validation instead? Please suggest on what can be done here @wesleykendall :)

Trigger function names collide

@tomage I just encountered an issue with django-pgtrigger that opens the possibility of some bad bugs.

Trigger names are unique for every table, but trigger function names are globally unique. Currently django-pgtrigger uses the same function and trigger name. If a user uses the same trigger name across models, they will run into an issue. This is an issue that has been present since the beginning of django-pgtrigger.

I'm going to resolve this by creating a globally unique function name for the trigger based on the model. The django-pgtrigger registry will also help ensure it is globally unique and throw an error if names collide.

Support for multiple DB connections

From what I can see in the codebase, the pgtrigger.core module uses from django.db import connection to deliver the required DB connection, which means that it uses the default connection from django.db.connections and there is no way to change / override / configure that behaviour.

In my specific case, I need to support different groups of models which use different DBs and the default connection is not being used at all, which means that with a hardcoded default DB connection, I am unable to use this package on any of my projects.

In an ideal situation, I would like to be able to do the following:

from django.db import connections
import pgtrigger

@pgtrigger.register(
    pgtrigger.Trigger(
        level=pgtrigger.Statement,
        when=pgtrigger.After,
        operation=pgtrigger.Delete,
        referencing=pgtrigger.Referencing(old='old_values', new='new_values'),
        func=_some_trigger_function,
        db_connection=connections["some_connection"]
    )
)
class SomeModel(models.Model):
    pass

or

from django.db import connections
import pgtrigger

@pgtrigger.register(
    db_connection=connections["some_connection"]
    pgtrigger.Trigger(
        level=pgtrigger.Statement,
        when=pgtrigger.After,
        operation=pgtrigger.Delete,
        referencing=pgtrigger.Referencing(old='old_values', new='new_values'),
        func=_some_trigger_function
    )
)
class SomeModel(models.Model):
    pass

which would allow me to specify the required connection via db_connection parameter.

Are there any plans to support multiple DB connections on a per trigger definition basis?

Triggers on Abstract Models

Should it be possible to use triggers on abstract models in some way or is that unsupported? It doesn't seem possible but I wanted to make sure I'm not just missing something in the options somewhere.

The following example code leads to an error:

import pgtrigger
from psqlextra.models import PostgresModel

@pgtrigger.register(
    pgtrigger.Protect(
        name='read_only',
        operation=pgtrigger.Update,
        condition=pgtrigger.Q(old__created_at__df=pgtrigger.F('new__created_at'))
    )
)
class SaasBaseModel(PostgresModel):

    class Meta:
        abstract = True

    created_at = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)


class ExampleModel(SaasBaseModel):
    color = models.CharField(max_length=64)

Traceback (most recent call last):
  File "C:\X\lib\site-packages\django\db\backends\utils.py", line 87, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.UndefinedTable: relation "common_saasbasemodel" does not exist
CONTEXT:  SQL statement "CREATE TRIGGER pgtrigger_read_only_4072d
                    BEFORE UPDATE ON common_saasbasemodel
                    
                    FOR EACH ROW WHEN (OLD."created_at" IS DISTINCT FROM (NEW."created_at"))
                    EXECUTE PROCEDURE pgtrigger_read_only_4072d()"
PL/pgSQL function inline_code_block line 2 at SQL statement

pgtrigger causes exceptions when reversing migrations

pgtrigger attempts to create triggers using the post migrate signal – this signal is fired for forwards and reverse migrations. This is problematic as it is possible pgtrigger will attempt to create a trigger on a non-existent table, causing exceptions and preventing subsequent post-migrate signals from being fired.

Example:

Simple test model:

@pgtrigger.register(pgtrigger.Protect(name="cant_delete", operation=pgtrigger.Delete))
class TestModel(models.Model):
    ...

Running forwards, then reverse migrations:

$ ./manage.py migrate test_pg_trigger
Operations to perform:
  Apply all migrations: test_pg_trigger
Running migrations:
  Applying test_pg_trigger.0001_initial... OK

$ ./manage.py migrate test_pg_trigger zero
Operations to perform:
  Unapply all migrations: test_pg_trigger
Running migrations:
  Rendering model states... DONE
  Unapplying test_pg_trigger.0001_initial... OK
Traceback (most recent call last):
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.UndefinedTable: relation "test_pg_trigger_testmodel" does not exist
CONTEXT:  SQL statement "CREATE TRIGGER pgtrigger_cant_delete_7dbde
                    BEFORE DELETE ON test_pg_trigger_testmodel

                    FOR EACH ROW
                    EXECUTE PROCEDURE pgtrigger_cant_delete_7dbde()"
PL/pgSQL function inline_code_block line 2 at SQL statement


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/path/to/project/./manage.py", line 22, in <module>
    main()
  File "/path/to/project/./manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 268, in handle
    emit_post_migrate_signal(
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/core/management/sql.py", line 46, in emit_post_migrate_signal
    models.signals.post_migrate.send(
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/dispatch/dispatcher.py", line 170, in send
    return [
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/dispatch/dispatcher.py", line 171, in <listcomp>
    (receiver, receiver(signal=self, sender=sender, **named))
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/pgtrigger/apps.py", line 9, in install
    pgtrigger.install(database=using)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/pgtrigger/core.py", line 903, in install
    trigger.install(model)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/pgtrigger/core.py", line 657, in install
    cursor.execute(rendered_trigger)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/path/to/project/.direnv/python-3.9.0/lib/python3.9/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: relation "test_pg_trigger_testmodel" does not exist
CONTEXT:  SQL statement "CREATE TRIGGER pgtrigger_cant_delete_7dbde
                    BEFORE DELETE ON test_pg_trigger_testmodel

                    FOR EACH ROW
                    EXECUTE PROCEDURE pgtrigger_cant_delete_7dbde()"
PL/pgSQL function inline_code_block line 2 at SQL statement

[critical] `pgtrigger.ignore` persists outside of its context if still in same transaction

tl;dr

pgtrigger uses SET LOCAL to set the SQL variables controlling trigger suppression. Changes made by SET LOCAL are transaction-scoped, meaning that they leak into following queries in the same transaction. Since exiting the last ignore context manager also removes the pre_execute_hook, the new/empty value for pgtrigger.ignore isn't injected into any queries, leaving it where it was inside the ignore context block.

This can easily lead to triggers being ignored entirely. If there is an official interface enforced by write protection, that write protection is gone after you use it until the transaction is over. If you wrap every request in a transaction, the write protection is gone for the rest of the request.

I will edit this issue to provide a more detailed explanation, but want to publish it ASAP as I think it is critical.

EDIT:
Firstly, I hope it's OK that I marked this issue as critical. Given that this library is meant to provide reliable hard guarantees, and given that this bug makes it so that said hard guarantees are quietly bypassed, I felt that this was appropriate.

Context

I'll briefly recap how pgtrigger (and more specifically the mechanism to temporarily ignore triggers) works so that I can explain this issue and discuss potential solutions in context.

The triggers created by pgtrigger are functions that, before performing the action they usually would, check if they should run or not in the first place. They do this by reading pgtrigger.ignore, an array of triggers that should currently be ignored, and check whether they are in that array. If so, they perform no additional action.

So, by setting pgtrigger.ignore, we can temporarily disable triggers, giving us a low-level interface.

The high-level interface is pgtrigger.core.ignore, a context manager that temporarily sets pgtrigger.ignore to its argument list for queries that happen inside its context block.

It achieves this by

• remembering the currently ignored triggers in a thread-local variable
• injecting a pre_execute_hook using pgconnection. This hook will prepend SQL to every query executed in the current thread. This prepended SQL sets pgtrigger.ignore using SET LOCAL.

The issue

When the context block of ignore is exited, the thread-local variable is left in an empty state. However: the pre_execute_hook is also removed.

Furthermore, changes made by SET LOCAL persist for the rest of the current transaction [1].

This means:

• The last query that is executed in the context block has a SET LOCAL pgtrigger.ignore='{a_trigger_name}' prepended
• The first query that is executed after the context block has nothing prepended anymore, since the pre_execute_hook is now gone
• So, assuming that we are still in the same transaction, pgtrigger.ignore will still be {a_trigger_name}, and this trigger will not run at all until either the transaction ends or a new pgtrigger.core.ignore context is entered and a query executed within it.

Proposed solution

Simply emit a SET LOCAL pgtrigger.ignore='{}'; when exiting the ignore context. This is true by definition at this point.

Alternative hotfix

In case this approach causes problems (for example with aborted transactions that mean that we can't make queries):

Don't remove the pre_execute_hook, simply register it forever. It will stay around and prepend mostly useless SQL until the thread exits, but that is way better than the status quo.

[1] https://www.postgresql.org/docs/14/sql-set.html

Feature request: Integration with Django's system check framework

Integrate pgtrigger into django's check system similar to other Django model constructs. Key pieces of behavior desired:

1. Duplicate trigger name checks happen in the check system instead of during code load time.
2. Warn users when child models don't inherit the triggers of the base model

Although Django allows one to turn off checks, number 1 will still fail during installation time since a duplicate trigger name is a serious error.

Number 2 should be able to be ignored if it happens, although it's assumed this should be a rare case. It is not totally clear if/how number 2 can fit into Django's check framework, so more research is needed.

Feature request: Filter triggers by model or by type

Filtering triggers by model/type would allow for more expressiveness in functions like pgtrigger.ignore(), for example, ignoring all soft deletes across a project to allow hard deletes

Trouble loading backends after django-pgtrigger 4.2.0

After 4.2.0 running pgmakemigrations there errors from the steming from the load_backend call in django-pgtrigger apps.py. I'm running this with PostGIS and django-postres-extras (the same setup as in issue #73 ). I'm not going to have a chance tonight to take a look at resolving it but wanted to share in case others run into similar issues.

Base Model

from django.contrib.gis.db import models

from psqlextra.models import PostgresModel
from psqlextra.models import PostgresPartitionedModel

import pgtrigger


class SaasBaseFields(models.Model):

    class Meta:
        abstract = True

    created = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)
    is_active = models.BooleanField(default=True)
    deactivated = models.DateTimeField(blank=True, null=True)


class SaasBaseModel(
    PostgresModel,
    SaasBaseFields,
    models.Model
):

    class Meta(PostgresModel.Meta):
        abstract = True
        triggers = [
            pgtrigger.Protect(
                name='read_only',
                operation=pgtrigger.Update,
                condition=pgtrigger.Q(old__created__df=pgtrigger.F('new__created'))
            ),
            pgtrigger.SoftDelete(name='soft_delete', field='is_active'),
            pgtrigger.Trigger(
                name='set_deactivated',
                when=pgtrigger.Before,
                operation=pgtrigger.Update,
                func="NEW.deactivated = NOW(); RETURN NEW;",
                condition=pgtrigger.Q(old__is_active=True, new__is_active=False)
            )
        ]

Error

Traceback (most recent call last):
  File "/opt/.pycharm_helpers/pycharm/django_manage.py", line 52, in <module>
    run_command()
  File "/opt/.pycharm_helpers/pycharm/django_manage.py", line 46, in run_command
    run_module(manage_file, None, '__main__', True)
  File "/usr/local/lib/python3.10/runpy.py", line 224, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/local/lib/python3.10/runpy.py", line 96, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "/usr/local/lib/python3.10/runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "/opt/project/manage.py", line 31, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.10/site-packages/django/core/management/__init__.py", line 395, in execute
    django.setup()
  File "/usr/local/lib/python3.10/site-packages/django/__init__.py", line 24, in setup
    apps.populate(settings.INSTALLED_APPS)
  File "/usr/local/lib/python3.10/site-packages/django/apps/registry.py", line 91, in populate
    app_config = AppConfig.create(entry)
  File "/usr/local/lib/python3.10/site-packages/django/apps/config.py", line 124, in create
    mod = import_module(mod_path)
  File "/usr/local/lib/python3.10/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1050, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1027, in _find_and_load
  File "<frozen importlib._bootstrap>", line 1006, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 688, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/usr/local/lib/python3.10/site-packages/pgtrigger/apps.py", line 50, in <module>
    backend = load_backend(config["ENGINE"])
  File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 111, in load_backend
    return import_module('%s.base' % backend_name)
  File "/usr/local/lib/python3.10/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1050, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1027, in _find_and_load
  File "<frozen importlib._bootstrap>", line 1006, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 688, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/usr/local/lib/python3.10/site-packages/psqlextra/backend/base.py", line 7, in <module>
    from .introspection import PostgresIntrospection
  File "/usr/local/lib/python3.10/site-packages/psqlextra/backend/introspection.py", line 48, in <module>
    class PostgresIntrospection(base_impl.introspection()):
  File "/usr/local/lib/python3.10/site-packages/psqlextra/backend/base_impl.py", line 94, in introspection
    return base_backend_instance().introspection.__class__
  File "/usr/local/lib/python3.10/site-packages/psqlextra/backend/base_impl.py", line 34, in base_backend_instance
    base_class_module = importlib.import_module(base_class_name + ".base")
  File "/usr/local/lib/python3.10/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "/usr/local/lib/python3.10/site-packages/django/contrib/gis/db/backends/postgis/base.py", line 8, in <module>
    from .operations import PostGISOperations
  File "/usr/local/lib/python3.10/site-packages/django/contrib/gis/db/backends/postgis/operations.py", line 21, in <module>
    from .models import PostGISGeometryColumns, PostGISSpatialRefSys
  File "/usr/local/lib/python3.10/site-packages/django/contrib/gis/db/backends/postgis/models.py", line 8, in <module>
    class PostGISGeometryColumns(models.Model):
  File "/usr/local/lib/python3.10/site-packages/django/db/models/base.py", line 108, in __new__
    app_config = apps.get_containing_app_config(module)
  File "/usr/local/lib/python3.10/site-packages/django/apps/registry.py", line 253, in get_containing_app_config
    self.check_apps_ready()
  File "/usr/local/lib/python3.10/site-packages/django/apps/registry.py", line 136, in check_apps_ready
    raise AppRegistryNotReady("Apps aren't loaded yet.")
django.core.exceptions.AppRegistryNotReady: Apps aren't loaded yet.

pgtrigger changes my django world...

....for the better.

I refuse to insert raw sql migrations into my migrations because I want everything captured in the models. The hoops I have jumped through to get composite keys to work with django2.2's constraint system, or create unique together's, or "clever" multi table inheritance. It left me feeling kinda queasy.

This package opens a completely new dimension to the things I can do to enforce integrity. I hate doing it in python, although most people insert data from my REST API, so I can enforce it there... but ... triggers are really the right answer here!

Thank you, thank you, thank you!

Is it possible to delay trigger at end of transaction? Add deferrable option?

Let's say I have a model... just don't question why my car's only have one wheel :)

class Car:
     wheel = OneToOneField('Wheel', null=True)
     requires_wheel = BooleanField()

I have a constraint that if requires_wheel is True, then wheel can't be None. But this can leave dangling wheels when Cars are deleted. So I could do this instead and get cascading deletes:

class Wheel:
    car = OneToOneField('Car')

But how do I enforce that if requires_wheel is True that the Wheel also exists? I was thinking through a trigger, but the trigger would need to wait until the end of the transaction. Deferrable?

Ignoring triggers breaks savepoint rollbacks in `atomic` blocks, leaving transactions in a failed state

When transaction.atomic blocks are nested, django will create a savepoint. If there is an error in the nested block, it will attempt to rollback to the last savepoint. This behavior is well documented here (see in particular the first admonition re: exception handling) and implemented here.

More concretely, this breaks use cases like the following:

from django.db import transaction, IntegrityError
import pgconnection

with transaction.atomic():
    with pgconnection.ignore("some_app.SomeModel:write_protect"):
        try:
            with transaction.atomic():
                SomeModel.objects.create(unique_name="some-name")
        except IntegrityError:
                SomeModel.objects.create(unique_name="some-fallback-name-that-will-always-be-unique")

Here, instead of the second create succeeding, it will fail because the rollback to the savepoint caused by the inner atomic block failed, meaning that the outer transaction is still in a failed state.

The reason this occurs is that _inject_pgtrigger_ignore is executed for the rollback command that is sent to the database. This causes psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block to be thrown.

A possible fix would be to check cursor.connection.get_transaction_status() before attempting to prepend the SQL, checking if it's equal to psycopg2.extensions.TRANSACTION_STATUS_INERROR, and aborting if this is the case.

Update: I can confirm that this works if monkey patched in!

This library is awesome

Thank you for making (and maintaining) it!

.

.

can't create Constraint Trigger

I can't create CONSTRAINT TRIGGER and also set DEFERRABLE INITIALLY DEFERRED settings.

Exiting `pgtrigger.ignore` while the transaction is in a failed state crashes

Code like this:

with pgtrigger.ignore(<some_trigger>):
    try:
         do_something() # raises database error
    except:
         # handle exception without reraising

Will currently crash, as the RESET statement emitted by pgtrigger will take place in a failed transaction.

This didn't use to be the case, and was most likely caused by the resolution of #49 .

While this isn't too common a use case, or one that should be encouraged - after all, it requires developers to disregard best practices around exception handling - tests are an exception to that rule. For example, it is currently impossible to write a test like so:

with pgtrigger.ignore(<some_trigger>):
    with pytest.raises(<database error>) as excinfo:
         do_something() # raises database error

# assert things about excinfo

Best workaround I could find is something like

with pgtrigger.ignore(<some_trigger>):
    with transaction.atomic():
        with pytest.raises(<database error>) as excinfo:
             do_something() # raises database error
        transaction.set_rollback(True)

# assert things about excinfo

Which is ugly, and nontrivial to arrive at.

Usage of built in SoftDelete

I've been enjoying the 3.x releases!

I am currently using the built in SoftDelete and was curious if there was a best practices or suggested method for overriding the trigger through Django and doing hard deletes?

Also how much are the built in Triggers like SoftDelete intended to be examples vs a set of "batteries included" tools? For example, I am adding the delete time via SoftDelete and was wondering if things like this would be something that should be submitted back to the project with a PR with relevant tests and doc updates?

class SoftDeleteWithDatetime(Trigger):
    """Sets a field to a value when a delete happens.

    Supply the trigger with the "field" that will be set
    upon deletion and the "value" to which it should be set.
    The "value" defaults to ``False``.

    .. note::

        This trigger currently only supports nullable ``BooleanField``,
        ``CharField``, and ``IntField`` fields.
    """

    when = Before
    operation = Delete
    field = None
    value = False

    def __init__(self, *, name=None, condition=None, field=None, value=_unset, field_datetime=None):
        self.field_datetime = field_datetime
        self.field = field or self.field
        self.value = value if value is not _unset else self.value

        if not self.field:  # pragma: no cover
            raise ValueError('Must provide "field" for soft delete')

        super().__init__(name=name, condition=condition)

    def get_func(self, model):
        soft_field = model._meta.get_field(self.field).column
        soft_field_datetime = model._meta.get_field(self.field_datetime).column
        pk_col = model._meta.pk.column

        def _render_value():
            if self.value is None:
                return 'NULL'
            elif isinstance(self.value, str):
                return f"'{self.value}'"
            else:
                return str(self.value)

        if self.field_datetime is not None:
            return f'''
            UPDATE {_quote(model._meta.db_table)}
            SET
            {soft_field} = {_render_value()},
            {soft_field_datetime} = NOW()
            WHERE {_quote(pk_col)} = OLD.{_quote(pk_col)};
            RETURN NULL;
        '''

        return f'''
            UPDATE {_quote(model._meta.db_table)}
            SET {soft_field} = {_render_value()}
            WHERE {_quote(pk_col)} = OLD.{_quote(pk_col)};
            RETURN NULL;
        '''

statement triggers

It looks like this package is doing triggers FOR EACH ROW. I'm wanting to create a FOR EACH STATEMENT trigger that updates a materialized view when a particular table is changed. It's a very low-write table, that's read using an annoyingly complicated RECURSIVE query. Is this something that this package is interested in supporting?

Create triggers for tables in unmigrated apps when run_syncdb is specified

Django can create tables without migrations if an app is left 'unmigrated' (doesn't have a migrations package in the app) and the run_syncdb option is provided to the migrate command. By default tests are run with run_syncdb=True so unmigrated apps are created in the database. Django's tests use this feature extensively as it simplifies the testing workflow (changes to test models are immediately reflected in the test db without having to run makemigrations).

I would like to enhance django-pgtrigger so that triggers are created for unmigrated apps when run_syncdb=True

Here's the part of django's migrate command that creates the tables of unmigrated apps when run_syncdb option is set:

https://github.com/django/django/blob/1a7b6909ac030d2c4dae2664b08ee0bce9c4c915/django/core/management/commands/migrate.py#L321

Bug: pgtrigger.core.Trigger.render_ignore ignores the schema

The trigger can't find the function if the model is in a schema: the _pgtrigger_should_ignore should be prefixed with the schema name

Test against multiple versions of Postgres

In order to prevent issues like #96 happening again, test against all supported versions of Postgres in the test suite and document which Postgres versions are supported

Cannot use `pgtrigger.UpdateSearchVector` across relations

I tried to add a trigger to update a SearchVectorField with a field that has a relation to another table

class MyModel(models.Model):
    search_vector = SearchVectorField()

    class Meta:
        triggers = [
            pgtrigger.UpdateSearchVector(
                name="add_fields_to_entry_vector",
                vector_field="search_vector",
                document_fields=["provider__name", "consumer"],
            )
        ]

But I got...

django.core.exceptions.FieldDoesNotExist: Entry has no field named 'provider__name'

I assume this is similar to #47 and is not currently possible?

More accurate logging during install

I noticed that the library reports it's installing all current triggers every time migrations are run (or, more accurately, every time pgtrigger.install() is called).

Albeit not a big deal, it's not totally accurate, as most of the time, it's not actually installing them.

Digging thru the code, I can readily see why this is the case, but I also did some experimentation on how to change things to have pgtriggers report on these different actions:

• Installing trigger
• Pruning (deleting) trigger
• Updating trigger
• Updating trigger function

And I think it could be very helpful to get the logging to reflect what's happening behind the scenes. For example, if when running migrations against production system, the user would see install of some triggers that were not expected, that could raise some alarms. Or vice-versa, if the user was expecting to see a trigger be installed, or even a trigger function to be updated, it might be very useful to see it in the logs.

I tried to do some small changes to see if I could move logging closer to the actual SQL operations, and modify the code slightly to know when to report on which action.

If I understand the library correctly, we can split this "problem" into two: Named and unnamed triggers.

Unnamed triggers

I believe that triggers, and their functions, if unnamed, are given names based on their definitions. This has some "good" properties w.r.t. the issue I'm reporting: Any change made to the definition of the trigger, or it's function, will result in a changed name. This means that if there's no change made to a trigger, nothing should get reported. If a change is made, we should end up reporting creation of a "new" trigger, and pruning/deletion of the original one.

The "only" change that might be needed here is to look to see if given unnamed trigger found in the code can be found in the DB. If not - we'll create it and log the creation.

Named triggers

Here, things get more interesting, as changes to a trigger definition, or it's function, can be reflected as changes.

I started by looking into function definitions and came up with this approach:

• Remove the OR REPLACE from the trigger function create statement, making postgres raise an exception when the function exists.
• When the exception happens, use pg_get_functiondef() to get the current function definition from the database, and use the output to compare to the function as rendered from the code.
• If the function bodies differ, we then replace the function, and log that action accordingly. If they match, we don't log anything.

And I think a similar approach can be applied for the trigger itself.

I had implemented a (very) rough version of it.. But it's somewhat involved, and I wanted to get some feedback from @wesleykendall before either I commenced with a PR, or just let Wes try and implementation, as he is more familiar with the code.

It's also possible that the extra complexity of adjusting these logging messages may simply not be worth the extra complexity (there's also a (small) chance that this will slow the library down by the slightest, during the "installation" phase, tho since that is a rare event, that is probably fine).

pgtrigger doesn't play nicely (i.e., ignore) other non postgres dbs

I have a mysql db I use readonly as well as a sqlite db I use in testing ... pgtriggers tries to query them to see if they have any triggers that need to be purged.

We end up falling into:

1   948     if isinstance(database, str):
    949         databases = [database]
    950     else:
    951         databases = database or settings.DATABASES
    952 

And using the settings.DATABASES. Then we fail when the SELECT is invalid for those other non postgres dbs.

Looking at the code I don't see a way to ignore other databases as the code is... I'm not sure how you would want to fix. I'd say look at ENGINE="django.db.backends.postgresql_psycopg2" except, that I have a wrapper ("pg_unlogged") around the standard postgres engine to force unlogged transactions during testing.

Ah.. this might help:

https://stackoverflow.com/a/18849255/2077386

>>> from django.db import connection
>>> connection.vendor
'postgresql' or 'sqlite'