postgresql - 在 Postgres 触发器中访问事务上下文

Question

我有一个 Postgres 11.3 数据库，上面有很多触发器。这些触发器是通过 SQLAlchemy 的一些元编程自动生成的，并且基本上将每个 CREATE/UPDATE/DELETE 操作包装在每条记录上，存储它的“版本”。这个过程是完全自动的，不需要最终用户的任何努力。

我希望能够将 user_id 外键添加到我的 versions 表中。这是应用程序用户中的 user_id，不是正在执行事务的连接的 postgres/系统。该字段将需要由应用程序调用方法提供。

我最初的尝试涉及为每个表自动生成一组存储过程/postgres 函数。这行得通，但我想不出从当前事务访问上下文 的方法。下面是一些伪代码来演示:

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;

在该表的 DELETE 触发器中:

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

在这个假设中，您会看到我试图访问本质上是词法范围/闭包值的内容，位于单个事务的上下文中。

这可能吗，或者我有其他方法可以解决这样的问题吗？

干杯!

Answer 1

您可以使用本地交易 custom configuration variable 来做到这一点.

在您的 delete_address() 函数中分配变量:

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

...并使用以下方法检索触发器中的值:

current_setting('my_vars.user_id')