Accessing transaction context in a Postgres trigger

Question

I have a Postgres 11.3 database that has lots of triggers on it. These triggers are automatically generated through some metaprogramming via SQLAlchemy, and essentially wrap every CREATE/UPDATE/DELETE action on every record, storing a "version" of it. This process is entirely automatic and requires no effort from the end user.

I'd like to be able to add a user_id foreign key to my versions table. This is user_id as in application user, not the connected postgres/system within which the transaction is executing. This field will need to be provided by the application calling method.

My initial attempt has involved automating out a set of stored procedures/postgres functions for each table. This works, but I can't figure out a way to access the context from the current transaction. Here is some psuedo code to demonstrate:

CREATE OR REPLACE FUNCTION delete_address(resource_id INTEGER, user_id INTEGER)
    RETURNS INTEGER as
    $BODY$
        DECLARE
            context USER_ID INTEGER; // something like this magical context keyword
        BEGIN
            USER_ID = user_id;

            DELETE FROM address WHERE id = resource_id;

            RETURN 1;
        END;
    $BODY$
    LANGUAGE plpgsql;

In the DELETE trigger for this table:

CREATE OR REPLACE FUNCTION update_address_history_on_delete()
    RETURNS trigger AS
    $BODY$
    BEGIN
        UPDATE "versions"
        SET 
          date_archived = now()::timestamp,
          user_id = context USER_ID  // theres that magical context keyword again
        WHERE
            resource_id = OLD.id;
        RETURN OLD;
    END
    $BODY$
    LANGUAGE plpgsql

You will see in this hypothetical I'm trying to access what would essentially be a lexically scoped/closure value, sitting in the context of a single transaction.

Is this possible, or is there another way I can go about something like this?

Cheers!

score 4 · Accepted Answer · answered Sep 12 '19 at 06:33

4

You can do this with a transaction-local custom configuration variable.

Assign the variable in your delete_address() function with:

PERFORM set_config('my_vars.user_id', user_id, true);

...and retrieve the value in your trigger using:

current_setting('my_vars.user_id')

answered Sep 12 '19 at 06:33

Nick Barnes

19,816
3
51
63

Awesome, thanks for this. For anyone looking at how to specify it to transaction-specific, here we go: https://www.postgresql.org/docs/11/functions-admin.html . Note that it needs to period "." delimited, so "exciting.variable" will work, but "excitingvariable" wont – Jamie S Sep 13 '19 at 05:52

Accessing transaction context in a Postgres trigger

1 Answers1