在PostgreSQL中通过dblink兼容Oracle数据库中的自治事务

1、数据库事务原子性的局限与自治事务

1.1、事务原子性概述

如下所示，如果一个事务中有A和B两个数据的插入操作，当A的SQL语句执行成功后，因为某种原因（如服务器断电等），导致B的SQL语句执行失败，待数据库服务恢复正常后，数据库将进行回滚操作，此时即使A的SQL执行成功，也会连带一并撤销，回到事务执行之前的状态：

--创建一张表并提交事务
create table t(msg varchar(27));
commit;

--像该表中插入数据
insert into t values('string1');
insert into t values('string2');

--执行回滚操作，该回滚语句执行成功后，t表为空，无数据
rollback;

/*
上述SQL语句中，在数据库关闭自动提交的前提下，执行rollback语句后，两个insert操作都会
被撤销，而并不会只撤销“insert into t values('string2');”，因为两个insert操作在同一个事务中，
rollback是以事务为单位进行撤销，而非以SQL语句为单位，所以在这里，rollback执行成功后，
再查看该表时，表中为空，没有数据。

自动提交概念：现在部分数据库默认打开了自动提交的功能，具体表现为，执行一个SQL后，无需
手动输入“commit;”进行事务提交，因此在开启自动提交的绝大部分情况下，一个事务中就只包含
一个SQL语句
*/

上述SQL在Oracle Database 19c中的执行效果如下图：

1.2、Oracle数据库下自治事务使用场景

如1.1的SQL所示，若此时有个需求，是在关闭自动提交或不在每个SQL后手动commit的前提下，通过rollback语句，仅回滚最近执行的一个insert语句，而保留其他insert语句的执行结果，这个时候就需要用到自治事务。
自治事务并不是所有的数据库都支持，当下用得比较多的场景是在Oracle数据库下使用自治事务，针对本文1.1章节的SQL语句，我们可以改写为如下形式，使用Oracle的自治事务：

--创建一张表并提交事务
create table t ( msg varchar(27) );
commit

--创建一个存储过程Autonomous_Insert，将第二个insert和回滚语句写入这个存储过程中
create or replace procedure Autonomous_Insert
as
   pragma autonomous_transaction;  --使用Oracle自治事务语句
begin
    insert into t values ( 'string2' );  --将第二个insert语句写入
    rollback;   --将回滚语句写入
end;

--执行两个插入的操作
/
begin
    insert into t values ( 'string1' );
    Autonomous_Insert;  --调用包含第二个insert的存储过程
    commit;
end;
/

--查询表格，可看到此时表格中存在第1个insert的数据string1
select * from t;

上述SQL在Oracle Database 19c中的执行效果如下图：

2、通过dblink实现PostgreSQL下的自治事务

PostgreSQL原本并不像Oracle数据库那样支持自治事务，为了实现自治事务的功能，PostgreSQL需要额外安装扩展插件dblink，通过dblink的跨库查询功能间接实现自治事务。

2.1、PostgreSQL下的dblink扩展概述

PostgreSQL本身并不支持像MariaDB和SQL Server等数据库那样，直接在SQL上进行跨库查询，因此如下图所示的，在MariaDB的跨库查询的SQL语法在PostgreSQL是执行不成功的：

图中的语法在PostgreSQL中是不支持的。
为弥补PostgreSQL在跨库查询的局限性，PostgreSQL需要额外安装dblink扩展，通过dblink实现跨库查询可见2.2章节，跨库查询的语法和自治事务语法相同，只是dblink_connect函数中的，代表数据库名称的dbname参数不同而已，若是跨服务器的话，还涉及到代表IP地址的host参数的不同。

2.2、PostgreSQL下通过dblink实现自治事务

在本文1.1章节的两个insert语句的场景下，在PostgreSQL实现自治事务的SQL代码如下：

--创建数据库yanmuhuan_test
create database yanmuhuan_test;

--进入数据库yanmuhuan_test
\c yanmuhuan_test;

--安装dblink扩展
create extension dblink;

--关闭自动提交功能
\set AUTOCOMMIT off

--创建数据表t
create table t(msg varchar(27));
commit;

--创建一个存储过程Autonomous_Insert，将第1个insert写入这个存储过程中
create or replace function Autonomous_Insert(v_description varchar)
returns void
as 
$BODY$
declare
    v_sql text;
begin
    PERFORM dblink_connect('dbname=yanmuhuan_test user=postgres password=yanmuhuan1997114 host=127.0.0.1 port=5432');    --dblink_connect为跨库连接操作
    v_sql := format('INSERT INTO t VALUES(%L)',v_description);
    PERFORM dblink_exec(v_sql);   --dblink_exec中存放跨库执行的SQL（此处为第1个insert语句）
    PERFORM dblink_disconnect();   --dblink_disconnect为跨库连接断开操作
end;
$BODY$
LANGUAGE  plpgsql;
commit;

--执行两个插入的操作
select Autonomous_Insert('string1');
insert into t values('string2');
rollback;

--查询表格，可看到此时表格中只存在第1个insert的数据string1
select * from t;
commit;

上述SQL代码在PostgreSQL 13中执行情况如下图：

3、Oracle数据库与PostgreSQL自治事务思路和SQL逻辑的区别

Oracle数据库和PostgreSQL在自治事务的语法逻辑的实现上，是相反的，如下图所示：

造成这样的结果在于Oracle和PostgreSQL的特性不同，相关特性如下：

1. Oracle数据库的会话在建立时，自动提交（隐式提交）默认关闭，因此Oracle数据库中，自治事务必须在存储过程内进行显式提交或回滚（即必须手动写入commit或rollback），所以若存储过程内没有手动写入commit或rollback进行显式提交，将会报“ORA-06519:检测到活动的独立的事务处理,
   已经回退”错误；

2. PostgreSQL的会话在建立时，自动提交（隐式提交）默认打开，而PostgreSQL自治事务是由存储过程中的dblink实现的，相当于在一个SQL交互会话中再新建立一个临时会话，因此在存储过程中手动写入commit或rollback是没有意义且无效的，所以commit和rollback只能在关闭自动提交（隐式提交）的前提下于存储过程外进行。