django-pgtrigger

The pgtrigger module provides a declarative API for defining PostgreSQL triggers within Django. It is organized into several functional clauses that map to SQL trigger syntax:

Level

Defines the granularity of the trigger:

• Row: Executes for each row affected.
• Statement: Executes once per SQL statement.

When (Timing)

Defines when the trigger fires relative to the operation:

• After: Fires after the operation.
• Before: Fires before the operation.
• InsteadOf: Fires instead of the operation (typically for views).

Operation

Defines which SQL operations trigger the execution:

• Insert, Update, Delete, Truncate, UpdateOf.

Referencing

• Referencing: Used to access the NEW and OLD row data.

Timing (Execution Mode)

• Immediate: Executes immediately.
• Deferred: Executes at the end of the transaction.

Func

• Func: Allows calling PostgreSQL functions.

Conditions

Logic used to determine if a trigger should fire, including:

• Condition, AnyChange, AnyDontChange, AllChange, AllDontChange.
• Django-like lookups: Q, F, IsDistinctFrom, IsNotDistinctFrom.

Triggers

High-level trigger abstractions:

• Trigger: The base class for custom triggers.
• Protect, ReadOnly, SoftDelete, FSM, UpdateSearchVector, Composer.

Runtime & Registry

• constraints, ignore, is_ignored, schema: Manage runtime behavior and constraints.
• register, registered: Manage the internal registry of triggers.

Installation & Management

• install, uninstall: Handle database setup/teardown.
• enable, disable: Toggle trigger state.
• prunable, prune: Manage cleanup of unused triggers.

Statement-level triggers run once per SQL statement rather than once per row, offering significant performance advantages for bulk operations.

When using pgtrigger.Composer with level=pgtrigger.Statement, the library automatically handles REFERENCING declarations to make transition tables (old_values and new_values) available.

Key differences from row-level triggers:

• They cannot be conditionally executed natively in Postgres (handled via template variables in Composer).
• They can only fire after an operation; they cannot alter rows in memory or prevent an operation from occurring (except via exceptions).
• To detect differences between old and new rows, you must join them on the primary key. Warning: If a primary key is updated, the join may fail to match the row, causing it to be missed.

Operation-to-Reference Mapping:

• pgtrigger.Update: Provides old_values and new_values.
• pgtrigger.Delete: Provides old_values.
• pgtrigger.Insert: Provides new_values.
• pgtrigger.UpdateOf and pgtrigger.Truncate: Provide null references.

pgtrigger.Composer(
    name="track_history",
    level=pgtrigger.Statement,
    when=pgtrigger.After,
    operation=pgtrigger.Update,
    func=f"""
        INSERT INTO {HistoryModel._meta.db_table}(old_field, new_field)
        SELECT
            old_values.field AS old_field,
            new_values.field AS new_field
        FROM old_values
            JOIN new_values ON old_values.id = new_values.id;
        RETURN NULL;
    ""
)

Triggers are 'deferrable' if their execution can be postponed until the end of a transaction. This is useful for enforcing constraints that require multiple related objects to be created within the same transaction.

To create a deferrable trigger, set the timing attribute to pgtrigger.Deferred in the pgtrigger.Trigger definition.

Important: Because deferrable triggers run at the end of the transaction, the operations they validate must all occur within a single transaction.atomic() block. If the operations are not wrapped in a transaction, the trigger will not run or will fail.

class UserProxy(User):
    class Meta:
        proxy = True
        triggers = [
            pgtrigger.Trigger(
                name="profile_for_every_user",
                when=pgtrigger.After,
                operation=pgtrigger.Insert,
                timing=pgtrigger.Deferred,
                func="""
                    IF NOT EXISTS (SELECT FROM profile_table WHERE user_id = NEW.id) THEN
                        RAISE EXCEPTION 'Profile does not exist for user %', NEW.id;
                    END IF;
                    RETURN NULL;
                """
            )
        ]

Statement-level triggers fire once per SQL statement rather than once per row. This is highly efficient for bulk operations (like bulk_create or update) because it allows you to process all changed rows in a single query using "transition tables".

To use transition tables, configure the trigger with pgtrigger.Referencing(old="table_name", new="table_name"). This provides access to temporary tables containing the old and new states of the affected rows.

Performance Tip: While statement-level triggers reduce the number of queries, they add complexity. A simpler row-level trigger might be preferable if the logic is not performance-critical.

class HistoryModel(models.Model):
    old_field = models.CharField(max_length=32)
    new_field = models.CharField(max_length=32)

class TrackedModel(models.Model):
    field = models.CharField(max_length=32)

    class Meta:
        triggers = [
            pgtrigger.Trigger(
                name="track_history",
                level=pgtrigger.Statement,
                when=pgtrigger.After,
                operation=pgtrigger.Update,
                referencing=pgtrigger.Referencing(old="old_values", new="new_values"),
                func=f"""
                    INSERT INTO {HistoryModel._meta.db_table}(old_field, new_field)
                    SELECT
                        old_values.field AS old_field,
                        new_values.field AS new_field
                    FROM old_values
                        JOIN new_values ON old_values.id = new_values.id;
                    RETURN NULL;
                "",
            )
        ]

The pgtrigger.Trigger object is the base class for all triggers. Its attributes map directly to Postgres trigger syntax.

Core Attributes

• name: A unique identifier for the trigger (max 48 characters).
• operation: The table operation that fires the trigger. Use pgtrigger.Update, pgtrigger.Insert, pgtrigger.Delete, pgtrigger.Truncate, or pgtrigger.UpdateOf. You can combine operations using the bitwise OR operator | (e.g., pgtrigger.Insert | pgtrigger.Update).
• when: Determines execution timing relative to the operation. Use pgtrigger.Before, pgtrigger.After, or pgtrigger.InsteadOf (for SQL views).
• condition (optional): A WHERE clause to conditionally execute the trigger based on OLD or NEW rows. Use pgtrigger.Q and pgtrigger.F objects to construct these conditions.

Advanced Attributes

• func: The raw PL/pgSQL snippet executed within the DECLARE ... BEGIN ... END block.
• declare (optional): A list of (variable_name, variable_type) tuples for additional variable declarations (e.g., [('my_var', 'BOOLEAN')]).
• level (optional, default=pgtrigger.Row): Set to pgtrigger.Row to fire once per row, or pgtrigger.Statement to fire once per statement.
• referencing (optional): Used in statement-level triggers to reference OLD and NEW rows as transition tables via pgtrigger.Referencing(old='old_table_name', new='new_table_name').
• timing (optional): Creates a deferrable CONSTRAINT trigger. Use pgtrigger.Immediate (end of statement) or pgtrigger.Deferred (end of transaction). Note: Deferrable triggers require level=pgtrigger.Row and when=pgtrigger.After.

Instead of deleting rows, pgtrigger.SoftDelete sets a field to a specific value (defaulting to False) when a .delete() is called.

Supported fields: Nullable CharField, IntField, and BooleanField.

Integration with Django Managers: To ensure Model.objects.all() automatically filters out soft-deleted items, use a custom Manager and set default_manager_name in the model's Meta class.

Warning: Django's on_delete=models.CASCADE will still perform actual database deletions on related models even if the parent is soft-deleted.

class NotDeletedManager(models.Manager):
    """Automatically filters out soft deleted objects from QuerySets"""
    def get_queryset(self):
        return super().get_queryset().exclude(is_active=False)

class SoftDeleteModel(models.Model):
    is_active = models.BooleanField(default=True)
    
    all_objects = models.ModelManager()  # access deleted objects too
    objects = NotDeletedManager()  # filter out soft deleted objects

    class Meta:
        triggers = [
            pgtrigger.SoftDelete(name="soft_delete", field="is_active")
        ]
        # Return both active/deleted data via Django Admin, dumpdata, etc.
        default_manager_name = "all_objects"