Don't recreate triggers when only function changed

[igavriil]

Assume you have a model with:

triggers = [
    pgtrigger.Trigger(
        name="example_trigger",
        when=pgtrigger.Before,
        operation=(pgtrigger.Insert | pgtrigger.Update),
        func=ORIGINAL_FUNCTION,
    )
]

And then you modify just the function in your model ORIGINAL_FUNCTION -> NEW_FUNCTION

triggers = [
    pgtrigger.Trigger(
        name="example_trigger",
        when=pgtrigger.Before,
        operation=(pgtrigger.Insert | pgtrigger.Update),
        func=NEW_FUNCTION,
    )
]

The current strategy of pgtrigger is to:

• Drop the existing trigger

DROP TRIGGER IF EXISTS pgtrigger_example_trigger_006bc ON "table";

• Create or replace the function

 CREATE OR REPLACE FUNCTION pgtrigger_example_trigger_006bc() ...

• Re-create the trigger

CREATE TRIGGER pgtrigger_example_trigger_006bc ...

The issue is for such operations

• CREATE TRIGGER requires a SHARE ROW EXCLUSIVE lock (postgres documentation)
  and i believe
• DROP TIGGER requires an ACCESS EXCLUSIVE lock (postgres issue)

For such cases I think it would be beneficiary if we could just do:

• Create or replace the function

 CREATE OR REPLACE FUNCTION pgtrigger_example_trigger_006bc() ...

Can you tell me if this a design choice and if not I could probably go and try to implement this approach.

[wesleykendall]

It was a design choice to keep the library simple. pgtrigger maintains a hash to know if the trigger has changed. If I understand what you want to do, you'll have to maintain two hashes. This could make the integration with Django's migration system much more complex among other things. For example, pgtrigger maintains the hash as a comment on the trigger itself. This is used to cross-reference trigger definitions in code to ensure they match what's installed in the DB, and it helps support users that want to install triggers manually without migrations.

You're welcome to try to solve it, but I can't guarantee that I would accept it if it introduces a lot of complexity. Making pgtrigger work with Django's migration system was extremely involved. I'd be worried about it not working correctly and then users not having correct triggers installed.

One solution I could imagine is making a subclass of the migration operation that only drops/re-creates the function, and then leaving it up to users to edit the default migrations if they wish to optimize them in this manner