在 Postgres 查询期间设置临时全局变量

Question

是否可以在查询期间设置一个可由 TRIGGER 过程捕获的变量(仅对相关查询有效)？

例如，我想记录查询执行者的 ID(current_user始终相同)。 所以我会做这样的事情：

tbl_executor (
id   PRIMARY KEY,
name VARCHAR
);
tbl_log (
executor REFERENCE tbl_executor(id),
op VARCHAR
);
tbl_other ...
CREATE TRIGGER t AFTER INSERT OR UPDATE OR DELETE ON tbl_executor 
FOR EACH ROW 
EXECUTE PROCEDURE (INSERT INTO tbl_log VALUES( ID_VAR_OF_THIS_QUERY ,TG_OP))

现在，如果我运行如下查询：

INSERT INTO tbl_other 
VALUES(.......) - and set ID_VAR_OF_THIS_QUERY='id of executor' -

我得到以下结果：

tbl_log
-----------------------------
id                | op      |
-----------------------------
'id of executor'  | 'INSERT'|

我希望我已经提出了这个想法...我认为这几乎不可行...但是有人可以帮助我吗？

Answer 1

回答问题

您可以像这样SET(自定义选项)：

SET myvar.role_id = '123';

但这需要一个文字值。还有功能set_config()。引用手册：

set_config(setting_name, new_value, is_local)......设置参数并返回新值

set_config将参数setting_name设置为new_value。如果is_localtrue，则新值将只应用于当前事务。

相应地，读取带有SHOW或current_setting()的选项值。相关：

• 如何在触发函数中使用变量设置？

但是您的触发器位于语法错误的表(tbl_executor)上。看起来像 Oracle 代码，您可以在其中直接提供代码以CREATE TRIGGER。在Postgres中，你首先需要一个触发器函数：

• 如何使用PostgreSQL触发器？

所以：

CREATE OR REPLACE FUNCTION trg_log_who()
RETURNS trigger AS
$func$
BEGIN
INSERT INTO tbl_log(executor, op)
VALUES(current_setting('myvar.role_id')::int, TG_OP);  -- !
RETURN NULL;  -- irrelevant for AFTER trigger     
END
$func$  LANGUAGE plpgsql;

您的示例设置需要 a 类型转换::int.
Then：

CREATE TRIGGER trg_log_who
AFTER INSERT OR UPDATE OR DELETE ON tbl_other  -- !
FOR EACH ROW EXECUTE PROCEDURE trg_log_who();  -- !

最后，从表中获取idtbl_executor来设置变量：

BEGIN;
SELECT set_config('myvar.role_id', id::text, true)   -- !
FROM   tbl_executor
WHERE  name = current_user;
INSERT INTO tbl_other VALUES( ... );
INSERT INTO tbl_other VALUES( ... );
--  more?
COMMIT;

将set_config()的第三个参数 (is_local) 设置为true，以使其根据请求成为本地会话。(相当于SET LOCAL。

但为什么是每行呢？每个陈述似乎更合理吗？

...
FOR EACHSTATEMENTEXECUTE PROCEDURE trg_foo();

不同的方法

除此之外，我会考虑一种不同的方法：一个简单的函数返回id列默认值：

CREATE OR REPLACE FUNCTION f_current_role_id()
RETURNS int LANGUAGE sql STABLE AS
'SELECT id FROM tbl_executor WHERE name = current_user';
CREATE TABLE tbl_log (
executor int DEFAULT f_current_role_id() REFERENCES tbl_executor(id)
, op VARCHAR
);

然后，在触发函数中，忽略executor列;将自动填充：

...
INSERT INTO tbl_log(op) VALUES(TG_OP);
...

请注意current_user和session_user之间的区别。看：

• 如何从Qt应用程序检查当前PostgreSQL用户的角色？

Answer 2

一种选择是创建一个共享表来保存此信息。由于它是每个连接的，因此主键应为pg_backend_pid()。

create table connection_global_vars(
backend_pid bigint primary key,
id_of_executor varchar(50)
);
insert into connection_global_vars(backend_pid) select pg_backend_pid() on conflict do nothing;
update connection_global_vars set id_of_executor ='id goes here' where backend_pid = pg_backend_pid();

-- in the trigger: 
CREATE TRIGGER t AFTER INSERT OR UPDATE OR DELETE ON tbl_executor 
FOR EACH ROW 
EXECUTE PROCEDURE (INSERT INTO tbl_log VALUES( (select id_of_executor from connection_global_vars where backend_pid = pg_backend_pid()) ,TG_OP))

另一种选择是创建一个临时表(每个连接都存在)。

create temporary table if not exists connection_global_vars(
id_of_executor varchar(50)
) on commit delete rows;
insert into connection_global_vars(id_of_executor) select null where not exists (select 1 from connection_global_vars);
update connection_global_vars set id_of_executor ='id goes here';

-- in the trigger: 
CREATE TRIGGER t AFTER INSERT OR UPDATE OR DELETE ON tbl_executor 
FOR EACH ROW 
EXECUTE PROCEDURE (INSERT INTO tbl_log VALUES( (select id_of_executor from connection_global_vars where backend_pid = pg_backend_pid()) ,TG_OP))

特别是对于PostgreSQL，它可能不会对性能产生太大影响，除了未记录的临时表可能会稍微快一点。

如果您在无法识别它是单个行表方面遇到性能问题，则可以运行 analysis。