首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

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

作者：hupi12丨 | 来源：互联网 | 2023-10-10 10:42
2019独角兽企业重金招聘Python工程师标准原文链接自主事务是从一个主事务启动的另一个事务，用于执行独立于调用事务提交或取消的SQL命令。最经典的用例是将用

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

原文链接

自主事务是从一个主事务启动的另一个事务&＃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_...()函数将有助于控制安全风险。


转:https://my.oschina.net/innovation/blog/782635



推荐阅读
  • int

    iOS数据库Sqlite的SQL语句分类和常见约束关键字

    本文介绍了iOS数据库Sqlite的SQL语句分类和常见约束关键字。SQL语句分为DDL、DML和DQL三种类型，其中DDL语句用于定义、删除和修改数据表，关键字包括create、drop和alter。常见约束关键字包括if not exists、if exists、primary key、autoincrement、not null和default。此外，还介绍了常见的数据库数据类型，包括integer、text和real。 ... [详细]
  • int

    Java String与StringBuffer的区别及其应用场景

    Java String与StringBuffer的区别及其应用场景
    本文主要介绍了Java中String和StringBuffer的区别，String是不可变的，而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象，内存使用上要优于String类。因此，在需要频繁对字符串进行修改的情况下，使用StringBuffer更加适合。同时，文章还介绍了String和StringBuffer的应用场景。 ... [详细]
  • int

    XML介绍与使用的概述及标签规则

    XML介绍与使用的概述及标签规则
    本文介绍了XML的基本概念和用途，包括XML的可扩展性和标签的自定义特性。同时还详细解释了XML标签的规则，包括标签的尖括号和合法标识符的组成，标签必须成对出现的原则以及特殊标签的使用方法。通过本文的阅读，读者可以对XML的基本知识有一个全面的了解。 ... [详细]
  • get

    SpringJdbcTemplate的使用详解

    本文详细介绍了Spring的JdbcTemplate的使用方法，包括执行存储过程、存储函数的call()方法，执行任何SQL语句的execute()方法，单个更新和批量更新的update()和batchUpdate()方法，以及单查和列表查询的query()和queryForXXX()方法。提供了经过测试的API供使用。 ... [详细]
  • main

    Java中vector的使用详解

    本文详细介绍了Java中vector的使用方法和相关知识，包括vector类的功能、构造方法和使用注意事项。通过使用vector类，可以方便地实现动态数组的功能，并且可以随意插入不同类型的对象，进行查找、插入和删除操作。这篇文章对于需要频繁进行查找、插入和删除操作的情况下，使用vector类是一个很好的选择。 ... [详细]
  • express

    关于Linq to sql 实现 模糊查询 string数组

    前景：当UI一个查询条件为多项选择，或录入多个条件的时候，比如查询所有名称里面包含以下动态条件，需要模糊查询里面每一项时比如是这样一个数组条件：newstring[]{兴业银行, ... [详细]
  • main

    Java学习笔记之面向对象编程（OOP）

    Java学习笔记之面向对象编程（OOP）
    本文介绍了Java学习笔记中的面向对象编程（OOP）内容，包括OOP的三大特性（封装、继承、多态）和五大原则（单一职责原则、开放封闭原则、里式替换原则、依赖倒置原则）。通过学习OOP，可以提高代码复用性、拓展性和安全性。 ... [详细]
  • get

    iOS实现UITextField+Limit的字符限制方法

    本文介绍了在iOS开发中使用UITextField实现字符限制的方法，包括利用代理方法和使用BNTextField-Limit库的实现策略。通过这些方法，开发者可以方便地限制UITextField的字符个数和输入规则。 ... [详细]
  • main

    Java中的Hashtable clear()方法详解

    本文介绍了Java中Hashtable的clear()方法，该方法用于清除和移除指定Hashtable中的所有键。通过示例程序演示了clear()方法的使用。 ... [详细]
  • get

    纠正网上的错误：自定义一个类叫java.lang.System/String的方法

    纠正网上的错误：自定义一个类叫java.lang.System/String的方法
    本文纠正了网上关于自定义一个类叫java.lang.System/String的错误答案，并详细解释了为什么这种方法是错误的。作者指出，虽然双亲委托机制确实可以阻止自定义的System类被加载，但通过自定义一个特殊的类加载器，可以绕过双亲委托机制，达到自定义System类的目的。作者呼吁读者对网上的内容持怀疑态度，并带着问题来阅读文章。 ... [详细]
  • main

    解决.net项目中未注册“microsoft.ACE.oledb.12.0”提供程序的方法

    解决.net项目中未注册“microsoft.ACE.oledb.12.0”提供程序的方法
    在开发.net项目中，通过microsoft.ACE.oledb读取excel文件信息时，报错“未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序”。本文提供了解决这个问题的方法，包括错误描述和代码示例。通过注册提供程序和修改连接字符串，可以成功读取excel文件信息。 ... [详细]
  • get

    七牛上传图片成功之后，图片裂了

    图像因存在错误而无法显示 ... [详细]
  • hash

    Java面经整理及相关概念解析

    本文整理了Java面试中常见的问题及相关概念的解析，包括HashMap中为什么重写equals还要重写hashcode、map的分类和常见情况、final关键字的用法、Synchronized和lock的区别、volatile的介绍、Syncronized锁的作用、构造函数和构造函数重载的概念、方法覆盖和方法重载的区别、反射获取和设置对象私有字段的值的方法、通过反射创建对象的方式以及内部类的详解。 ... [详细]
  • int

    C# Word模版打印方案详解

    本文详细介绍了使用C#实现Word模版打印的方案。包括添加COM引用、新建Word操作类、开启Word进程、加载模版文件等步骤。通过该方案可以实现C#对Word文档的打印功能。 ... [详细]
  • get

    C++标准库string类型的操作总结

    总结一下C中string的操作，来自〈CPrimer〉第四版。1.string对象的定义和初始化:strings1;空串strings2(s1);将s2初始 ... [详细]
author-avatar
hupi12丨
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有