How would you ignore a trigger within a trigger?

[ikokollari]

I am updating tbla."field_2" from a trigger in tblb. Except tbla."field_2" is protected with a pgtrigger.ReadOnly trigger.

This works without the pgtrigger.ReadOnly trigger in TblA. How would I do it and keep the pgtrigger.ReadOnly trigger on TblA.field_2?

Model TblA(models.Model):
    field_1 = models.CharField(max_length=20)
    field_2 = models.PositiveIntegerField(default=0)

    class Meta:
        triggers = [
            pgtrigger.ReadOnly(
                name='read_only_field_2',
                fields=['field_2']
            )
        ]

class TblB(models.Model):
    field_a = models.ForeignKey(TblA, on_delete=models.SET_NULL)

    class Meta:
        pgtrigger.Trigger(
                func=pgtrigger.Func('''
                    IF (NEW."{columns[field_a]}" IS NOT NULL) THEN
                        UPDATE "tbla"
                            SET "tbla"."field_2" = "tbla"."field_2" + 1
                        WHERE
                            "tbla"."id" = NEW.{columns[field_a]}
                    END IF;
                    RETURN NEW;
                    ''')
                ),
                when=pgtrigger.Before,
                operation=pgtrigger.Insert,
                name='increment_tbla_field_2'
            ),

I was thinking to mimic pgtrigger.ignore() functionality within the trigger by using set_config however I am not very familiar how persistent set_config is. Do I need to clean up at the end of the trigger? Is this doable?

set_config('pgtrigger.ignore', '{myapp:pgtrigger_read_only_field_2_c8215,pgtrigger_read_only_field_2_c8215}', true)

[wesleykendall]

The approach you outlined is likely the best approach for now.

The main potential side effect is overriding any triggers your app has previously ignored during the duration of this transaction. For example, if you did pgtrigger.ignore() in your app during this transaction, this trigger would override it, and any downstream triggers wouldn't be ignored. It's an edge case that would involve multiple triggers firing, so I assume it would be rare, but something to consider.

I'd be open for a PR that adds ignore as an argument to pgtrigger.Trigger to automatically wrap the trigger properly. I've hit this situation once before too myself

[ikokollari]

Thank you for a fast reply!
Indeed setting pgtrigger.ignore config would make pgtrigger.ignore() work improperly.

I glanced at the (excellent) source code to see how would you add a ignore argument to pgtrigger.Trigger.
I assume that the only way to achieve this functionality is to extend the pgtrigger.ignore value with what is provided in the ignore parameter in another wrapper function:

SELECT CURRENT_SETTING('pgtrigger.ignore') into _prev_ignore;
SELECT _prev_ignore::text[] || '{uri,pgid}'::text[] into _local_ignore;

SELECT set_config('pgtrigger.ignore', _local_ignore::text, true);

Afterwards

SELECT set_config('pgtrigger.ignore, _prev_ignore, true)
RETURN RESULT

But this would require to use a second wrapping function to reset the pgtrigger.ignore setting before returning.
Do you imagine there's a better way?

[ikokollari]

I made it work like so:

Model TblA(models.Model):
    field_1 = models.CharField(max_length=20)
    field_2 = models.PositiveIntegerField(default=0)

    class Meta:
        triggers = [
            pgtrigger.ReadOnly(
                name='read_only_field_2',
                fields=['field_2']
            )
        ]

class TblB(models.Model):
    field_a = models.ForeignKey(TblA, on_delete=models.SET_NULL)

    class Meta:
        triggers = [
            pgtrigger.Trigger(
                func=pgtrigger.Func('''
                        BEGIN
                            SELECT CURRENT_SETTING('pgtrigger.ignore', true) INTO _prev_ignore;
                            EXCEPTION WHEN OTHERS THEN
                        END;

                        IF _prev_ignore IS NOT NULL AND (_prev_ignore = '') IS NOT TRUE THEN
                            SELECT _local_ignore || _prev_ignore::text[] INTO _local_ignore;
                        END IF;

                        PERFORM set_config('pgtrigger.ignore', _local_ignore::text, true);

                        IF (NEW."{columns[field_a]}" IS NOT NULL) THEN
                            UPDATE "tbla"
                                SET "tbla"."field_2" = "tbla"."field_2" + 1
                            WHERE
                                "tbla"."id" = NEW.{columns[field_a]}
                        END IF;

                        IF _prev_ignore IS NOT NULL AND (_prev_ignore = '') IS NOT TRUE THEN
                            PERFORM set_config('pgtrigger.ignore', _prev_ignore, true);
                        ELSE
                            PERFORM set_config('pgtrigger.ignore', '', true);
                        END IF;
                    RETURN NEW;
                    ''')
                ),
                when=pgtrigger.Before,
                operation=pgtrigger.Insert,
                name='increment_tbla_field_2',
                declare=[
                    ('_prev_ignore', 'text'),
                    ('_local_ignore', f"text[] = '{{{TblA._meta.db_table}:{TblA._meta.triggers[0].get_pgid(TblA)},{TblA._meta.triggers[0].get_pgid(TblA)}}}'"),
                ]
            ),
        ]

[wesleykendall]

Nice! So you verified that this works for you locally?

I will mark this as the answer. I'd love to get this into pgtrigger as a first-class citizen

[ikokollari]

Yes, this works in my application. I am also in the process of making a PR. It required little contribution to add this functionality.
The keyword ignore was already reserved by subclasses of pgtrigger.Trigger, so I settled for ignore_others argument which accepts a list of trigger URIs.

There's one caveat, the user must provide an instance of pgtrigger.Func to the func argument and mark to reset triggers at every branch of the trigger code.

I was struggling with the tests setup, so as soon as I have written comprehensive tests I will submit it.