[译稿]PostgreSQL中匿名事务的使用

2019独角兽企业重金招聘Python工程师标准>>>

原文链接

自主事务是从一个主事务启动的另一个事务&＃xff0c;用于执行独立于调用事务提交或取消的SQL命令。

最经典的用例是将用户在数据库上完成的所有操作插入日志记录表&＃xff0c;无论事务成功还是失败。使用PostgreSQL&＃xff0c;当事务中的某些事务失败时&＃xff0c;事务中完成的所有更改都将被取消。出于同样的原因&＃xff0c;当自治事务失败时&＃xff0c;没有异常会被转发到调用事务&＃xff0c;它可以成功执行结束。

译者注: 类似finally, 但是独立的。

try { }catch { } finally { }

我们可以将自治事务视为一个独立的工作单元&＃xff0c;就像在另一个会话中执行SQL语句一样。在这种情况下&＃xff0c;在主事务中提交的操作在自主事务中不可见&＃xff0c;直到主事务被终止和提交。

在自主事务中执行的SQL操作可以在主事务提交时显示&＃xff0c;并且主事务中的隔离级别不是SERIALIZABLE或REPEATABLE READ。

下面是一个非常简单的Oracle函数示例&＃xff0c;它使用自主事务独立地记录数据库中执行的所有操作&＃xff0c;而不管事务的最终结果是什么。

目前没有办法在PostgreSQL 原生版本中存在(截止2016-08-19, 后面有人提交了补丁)。单个事务中的每个更改都将被取消或随事务一起提交。然而&＃xff0c;很长时间以来&＃xff0c;有一些解决方案来执行这样的事务。它变得更容易&＃xff0c;在PostgreSQL 9.5版本中&＃xff0c;旧的解决方案是使用dblink扩展名与后端的专用连接。最新的解决方案是使用pg_background扩展来实现自主事务&＃xff0c;内核实现。我在下面的章节中描述两种方式。

“旧的方式”&＃xff1a;独立连接&＃xff1a;

要使用PostgreSQL获得相同的自治事务行为&＃xff0c;您只需打开一个到PostgreSQL的新连接&＃xff0c;并在此新会话中执行SQL语句。

为了这个工作&＃xff0c;我们通常使用dblink或PL/Proxy contrib模块很长时间。它们都允许创建到PostgreSQL服务器的新连接&＃xff0c;并在这个新连接内使用自己的事务执行SQL语句。

例如&＃xff0c;Ora2Pg(截止版本17.4&＃xff09;使用带有使用dblink的包装器的pragma AUTONOMOUS_TRANSACTION转换所有Oracle函数或过程。此包装器接受函数的名称并添加一个_atx后缀。

使用上面的Oracle示例&＃xff0c;Ora2Pg将首先转换此函数并使用_atx后缀重命名。

CREATE OR REPLACE FUNCTION log_action_atx (username text, event_date timestamp, msg text ) RETURNS VOID AS $body$ BEGININSERT INTO table_tracking VALUES (nextval(&＃39;log_seq&＃39;), username, event_date, msg); END; $body$ LANGUAGE PLPGSQL

然后&＃xff0c;它使用将由应用程序或另一个存储过程调用的dblink创建的包装函数。

-- -- dblink wrapper to call function log_action as an autonomous transaction -- CREATE OR REPLACE FUNCTION log_action (username text, event_date timestamp, msg text ) RETURNS VOID AS $body$ DECLARE-- Change this to reflect the dblink connection stringv_conn_str text :&＃61; &＃39;port&＃61;5432 dbname&＃61;testdb host&＃61;localhost user&＃61;pguser password&＃61;pgpass&＃39;;v_query text;BEGINv_query :&＃61; &＃39;SELECT true FROM log_action_atx ( &＃39; || quote_nullable(username) ||&＃39;,&＃39; || quote_nullable(event_date) || &＃39;,&＃39; || quote_nullable(msg) || &＃39; )&＃39;;PERFORM * FROM dblink(v_conn_str, v_query) AS p (ret boolean);END; $body$ LANGUAGE plpgsql SECURITY DEFINER;

此方法工作得很好&＃xff0c;但需要一些手动编辑来设置dblink连接参数。还有一些安全问题&＃xff0c;连接密码在功能代码中。性能也不是最优的。

“新的使用方式”&＃xff1a;使用动态后台workder 进程:

希望自从添加动态后台工作人员和Robert Haas的大部分工作以来&＃xff0c;可能是因为PostgreSQL v9.5使用pg_background扩展创建自主事务。此扩展提供了一些其他优势&＃xff0c;但本文只是解释如何以自主方式调用函数或SQL语句。

如果我们回到前面的例子&＃xff0c;这里是Ora2Pg如何导出函数使用pg_background扩展在即将到来的17.5版本。

-- -- pg_background wrapper to call function log_action as an autonomous transaction -- CREATE OR REPLACE FUNCTION log_action (username text, event_date timestamp, msg text ) RETURNS VOID AS $body$ DECLAREv_query text;BEGINv_query :&＃61; &＃39;SELECT true FROM log_action_atx ( &＃39; || quote_nullable(username) ||&＃39;,&＃39; || quote_nullable(event_date) || &＃39;,&＃39; || quote_nullable(msg) || &＃39; )&＃39;;PERFORM * FROM pg_background_result(pg_background_launch(v_query)) AS p (ret boolean); END; $body$ LANGUAGE plpgsql SECURITY DEFINER;CREATE OR REPLACE FUNCTION log_action_atx (username text, event_date timestamp, msg text ) RETURNS VOID AS $body$ BEGININSERT INTO table_tracking VALUES (nextval(&＃39;log_seq&＃39;), username, event_date, msg); END; $body$ LANGUAGE PLPGSQL ;

在这里&＃xff0c;我们谈到了自动PL/SQL的Oracle代码转换&＃xff0c;但是更简单的方法显然是直接调用函数:

CREATE OR REPLACE FUNCTION log_action (username text, event_date timestamp, msg text ) RETURNS text AS $body$ DECLAREs_id integer; BEGININSERT INTO table_tracking VALUES (nextval(&＃39;log_seq&＃39;), username, event_date, msg) RETURNING id INTO s_id;RETURN &＃39;Message inserted into table_tracking with id: &＃39;|| s_id; END; $body$ LANGUAGE plpgsql;

如果我们想等待自治事务的结果并使用结果&＃xff1a;

SELECT * FROM pg_background_result(pg_background_launch(&＃39;SELECT log_action(...)&＃39;)) AS p (ret text);

否则&＃xff0c;对pg_background_launch() 进行简单调用并放在后台执行自主事务。主事务将继续&＃xff0c;而不等待在后台启动的自主事务的结果。

SELECT pg_background_launch(&＃39;SELECT log_action(...)&＃39;);

CREATE OR REPLACE FUNCTION test_autonomous_transaction (username text, msg text ) RETURNS text AS $body$ DECLAREat_pid integer;at_result text; BEGINSELECT INTO at_pid pg_background_launch(&＃39;SELECT log_action(&＃39;||username||&＃39;,&＃39;||now()||&＃39;,&＃39;||msg)&＃39;);... do something ...SELECT INTO at_result * FROM pg_background_result(at_pid) as (result text);RETURN at_result; END;

下面是一个完整的使用Ora2pg 创建的函数

CREATE TABLE table_tracking ( id integer, username text, event_date timestamp, msg text);CREATE SEQUENCE log_seq START 1;CREATE OR REPLACE FUNCTION log_action_atx (username text, event_date timestamp, msg text ) RETURNS VOID AS $body$ BEGININSERT INTO table_tracking VALUES (nextval(&＃39;log_seq&＃39;), username, event_date, msg); END; $body$ LANGUAGE PLPGSQL ;CREATE OR REPLACE FUNCTION log_action (username text, event_date timestamp, msg text ) RETURNS VOID AS $body$ DECLAREv_query text; BEGINv_query :&＃61; &＃39;SELECT true FROM log_action_atx ( &＃39; || quote_nullable(username) || &＃39;,&＃39; || quote_nullable(event_date) || &＃39;,&＃39; || quote_nullable(msg) || &＃39; )&＃39;;PERFORM * FROM pg_background_result(pg_background_launch(v_query)) AS p (ret boolean); END; $body$ LANGUAGE plpgsql SECURITY DEFINER;

gilles&＃61;# TRUNCATE table_tracking ; TRUNCATE TABLE gilles&＃61;# ALTER SEQUENCE log_seq restart 1; ALTER SEQUENCE gilles&＃61;# BEGIN; BEGIN gilles&＃61;# SELECT * from table_tracking;id | username | event_date | msg ----&＃43;----------&＃43;------------&＃43;----- (0 ligne)gilles&＃61;# SELECT log_action(&＃39;gilles&＃39;, &＃39;now&＃39;, &＃39;Add autonomous_transaction article&＃39;);log_action ------------(1 ligne)gilles&＃61;# SELECT * from table_tracking;id | username | event_date | msg ----&＃43;----------&＃43;----------------------------&＃43;------------------------------------1 | gilles | 2016-08-19 11:55:08.859347 | Add autonomous_transaction article (1 ligne)gilles&＃61;# ROLLBACK; ROLLBACK gilles&＃61;# SELECT * from table_tracking;id | username | event_date | msg ----&＃43;----------&＃43;----------------------------&＃43;------------------------------------1 | gilles | 2016-08-19 11:55:08.859347 | Add autonomous_transaction article (1 ligne)

In this last example, we can see that it is perfectly possible to work on something else in the main transaction while the autonomous transaction is running in the background:

在最后一个例子中&＃xff0c;我们可以看到&＃xff0c;当自主匿名事务在后台运行时&＃xff0c;主事务中完全可以在处理其他事情&＃xff1a;

gilles&＃61;# BEGIN; BEGIN gilles&＃61;# SELECT * FROM table_tracking;id | username | event_date | msg ----&＃43;----------&＃43;----------------------------&＃43;------------------------------------1 | gilles | 2016-08-19 14:00:12.573144 | Add autonomous_transaction article2 | gilles | 2016-08-19 14:01:20.83565 | Add autonomous_transaction article (2 lignes)gilles&＃61;# SELECT pg_background_launch($$SELECT pg_sleep(30); SELECT log_action(&＃39;gilles&＃39;,&＃39;now&＃39;,&＃39;Add autonomous_transaction article&＃39;);$$);pg_background_launch ----------------------26170 (1 ligne)gilles&＃61;# SELECT * FROM table_tracking;id | username | event_date | msg ----&＃43;----------&＃43;----------------------------&＃43;------------------------------------1 | gilles | 2016-08-19 14:00:12.573144 | Add autonomous_transaction article2 | gilles | 2016-08-19 14:01:20.83565 | Add autonomous_transaction article (2 lignes)... Attente de 30 secondes ...gilles&＃61;# SELECT * FROM table_tracking;id | username | event_date | msg ----&＃43;----------&＃43;----------------------------&＃43;------------------------------------1 | gilles | 2016-08-19 14:00:12.573144 | Add autonomous_transaction article2 | gilles | 2016-08-19 14:01:20.83565 | Add autonomous_transaction article3 | gilles | 2016-08-19 14:05:42.181332 | Add autonomous_transaction article (3 lignes)gilles&＃61;# SELECT * FROM pg_background_result(26170) as p (result text);result -------------------------------------------------Message inserted into table_tracking with id: 3 (1 ligne)gilles&＃61;# ROLLBACK; ROLLBACK

这里&＃xff0c;我们在自治事务中添加了等待30秒( &＃96;&＃96;&＃96;SELECT pg_sleep(30);)&＃xff0c; 我们可以看到pg_background_launch() 函数立即返回。如果我们在主事务中等待30秒&＃xff0c;那么我们可以看到&＃xff0c; 当自治事务结束时&＃xff0c;我们在table_tracking表中有新的条目。现在我们可以使用pg_background_launch()返回的pid和 pg_background_result()函数来获得自治事务的结果。

这种在后台执行自动事务的特性,可以让你绕过一些PostgreSQL的限制, 比如在事务中运行CREATE INDEX CONCURRENTLY语句。

警告: 需要超级用户来创建此扩展&＃xff0c;一旦创建&＃xff0c; 任何有权访问数据库的用户将被授予执行这些pg_background_...&＃xff08;&＃xff09;函数。即使对象的ACL被保留&＃xff0c;强制要求真正小心数据库访问和定期审计数据库。要防止所有用户执行这些功能&＃xff0c; 最好的办法是将该扩展重定位到专有的模式(schema)下&＃xff0c; 只有授权用户才能被授予使用权限。这可以使用以下命令完成&＃xff1a;

CREATE SCHEMA bgw_schema; ALTER EXTENSION pg_background SET SCHEMA bgw_schema; GRANT USAGE ON SCHEMA bgw_schema TO ;

在由授权用户创建的函数中使用SECURITY DEFINER属性并使用 bgw_schema.pg_background_...()函数将有助于控制安全风险。