PostgreSQL Oracle 兼容性之 - plpgsql 自治事务(autonomous_transaction)补丁

4 minute read

背景

PostgreSQL的plpgsql服务端编程语言与Oracle数据库的pl/sql编程语言非常类似，但是对于自治事务一直没有语法层面的支持。

以往如果要支持自治事务，可以使用exception或者使用dblink来实现。

写法有点复杂，如果你想要语法层面的支持，可以试试社区放出的这个补丁，补丁还没有合并到主分支，不建议生产使用。

用法

以9.6为例，介绍一下这个补丁的用法。

安装补丁

$ wget https://www.postgresql.org/message-id/attachment/45863/autonomous.patch

$ cd postgresql-9.6.1

$ patch -p1 < ../autonomous.patch 

$ make && make install

重启数据库

$ pg_ctl restart -m fast

语法讲解

查看这两个回归测试的文件，可以了解它的用法

1. src/pl/plpgsql/src/expected/plpgsql_autonomous.out

CREATE TABLE test1 (a int);
CREATE FUNCTION autonomous_test() RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 0..9 LOOP
    START TRANSACTION;
    EXECUTE 'INSERT INTO test1 VALUES (' || i::text || ')';
    IF i % 2 = 0 THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
  END LOOP;

  RETURN 42;
END;
$$;
SELECT autonomous_test();
 autonomous_test 
-----------------
              42
(1 row)

SELECT * FROM test1;
 a 
---
 0
 2
 4
 6
 8
(5 rows)

TRUNCATE test1;
CREATE FUNCTION autonomous_test2() RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 0..9 LOOP
    START TRANSACTION;
    INSERT INTO test1 VALUES (i);
    IF i % 2 = 0 THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
  END LOOP;

  RETURN 42;
END;
$$;
SELECT autonomous_test2();
 autonomous_test2 
------------------
               42
(1 row)

SELECT * FROM test1;
 a
---
  
  
  
  
  
(5 rows)

2. src/pl/plpython/expected/plpython_autonomous.out

CREATE TABLE test1 (a int, b text);
CREATE FUNCTION autonomous_test() RETURNS integer
LANGUAGE plpythonu
AS $$
with plpy.autonomous() as a:
    for i in range(0, 10):
        a.execute("BEGIN")
        a.execute("INSERT INTO test1 (a) VALUES (%d)" % i)
        if i % 2 == 0:
            a.execute("COMMIT")
        else:
            a.execute("ROLLBACK")

return 42
$$;
SELECT autonomous_test();
 autonomous_test 
-----------------
              42
(1 row)

SELECT * FROM test1;
 a | b 
---+---
 0 | 
 2 | 
 4 | 
 6 | 
 8 | 
(5 rows)

CREATE FUNCTION autonomous_test2() RETURNS integer
LANGUAGE plpythonu
AS $$
with plpy.autonomous() as a:
        a.execute("BEGIN")
        a.execute("INSERT INTO test1 (a) VALUES (11)")
        rv = a.execute("SELECT * FROM test1")
        plpy.info(rv)
        a.execute("ROLLBACK")

return 42
$$;
SELECT autonomous_test2();
INFO:  <PLyResult status=5 nrows=6 rows=[{'a': 0, 'b': None}, {'a': 2, 'b': None}, {'a': 4, 'b': None}, {'a': 6, 'b': None}, {'a': 8, 'b': None}, {'a': 11, 'b': None}]>
 autonomous_test2 
------------------
               42
(1 row)

SELECT * FROM test1;
 a | b 
---+---
 0 | 
 2 | 
 4 | 
 6 | 
 8 | 
(5 rows)

CREATE FUNCTION autonomous_test3() RETURNS integer
LANGUAGE plpythonu
AS $$
with plpy.autonomous() as a:
    a.execute("DO $_$ BEGIN RAISE NOTICE 'notice'; END $_$")
    a.execute("DO $_$ BEGIN RAISE EXCEPTION 'error'; END $_$")

return 42
$$;
SELECT autonomous_test3();
NOTICE:  notice
ERROR:  error
CONTEXT:  PL/pgSQL function inline_code_block line 1 at RAISE
PL/Python function "autonomous_test3"
CREATE FUNCTION autonomous_test4() RETURNS integer
LANGUAGE plpythonu
AS $$
with plpy.autonomous() as a:
    a.execute("SET client_encoding TO SJIS")

return 42
$$;
SELECT autonomous_test4();
ERROR:  cannot set client encoding in autonomous session
CONTEXT:  PL/Python function "autonomous_test4"
TRUNCATE test1;
CREATE FUNCTION autonomous_test5() RETURNS integer
LANGUAGE plpythonu
AS $$
with plpy.autonomous() as a:
    plan = a.prepare("INSERT INTO test1 (a, b) VALUES ($1, $2)", ["int4", "text"])
    a.execute_prepared(plan, [1, "one"])
    a.execute_prepared(plan, [2, "two"])

return 42
$$;
SELECT autonomous_test5();
 autonomous_test5 
------------------
               42
(1 row)

SELECT * FROM test1;
 a |  b  
---+-----
 1 | one
 2 | two
(2 rows)

TRUNCATE test1;
CREATE FUNCTION autonomous_test6() RETURNS integer
LANGUAGE plpythonu
AS $$
with plpy.autonomous() as a:
    plan = a.prepare("INSERT INTO test1 (a) VALUES (i)", {"i": "int4"})
    a.execute_prepared(plan, [1])
    a.execute_prepared(plan, [2])

return 42
$$;
SELECT autonomous_test6();
 autonomous_test6 
------------------
               42
(1 row)

SELECT * FROM test1;
 a | b 
---+---
 1 | 
 2 | 
(2 rows)

TRUNCATE test1;
CREATE FUNCTION autonomous_test7() RETURNS integer
LANGUAGE plpythonu
AS $$
with plpy.autonomous() as a:
        a.execute("BEGIN")
        plan = a.prepare("INSERT INTO test1 (a) VALUES ($1)", ["int4"])
        a.execute_prepared(plan, [11])
        plan = a.prepare("SELECT * FROM test1")
        rv = a.execute_prepared(plan, [])
        plpy.info(rv)
        a.execute("ROLLBACK")

return 42
$$;
SELECT autonomous_test7();
INFO:  <PLyResult status=5 nrows=1 rows=[{'a': 11, 'b': None}]>
 autonomous_test7 
------------------
               42
(1 row)

SELECT * FROM test1;
 a | b 
---+---
(0 rows)

CREATE FUNCTION autonomous_test8() RETURNS integer
LANGUAGE plpythonu
AS $$
with plpy.autonomous() as a:
        a.execute("BEGIN")

return 42
$$;
SELECT autonomous_test8();
ERROR:  autonomous session ended with transaction block open
CONTEXT:  PL/Python function "autonomous_test8"
DROP TABLE test1;

测试

测试plpgsql的自治事务用法

-- 创建测试表
CREATE TABLE test1 (a int);

创建测试函数
CREATE FUNCTION autonomous_test() RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
  -- 定义使用自治事务
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 0..9 LOOP
    -- 启动自治事务
    START TRANSACTION;
    -- 目前仅支持动态SQL
    EXECUTE 'INSERT INTO test1 VALUES (' || i::text || ')';
    IF i % 2 = 0 THEN
        -- 整除2的提交
	COMMIT;
    ELSE
        -- 不能整除2的回归
	ROLLBACK;
    END IF;
  END LOOP;

  RETURN 42;
END;
$$;

-- 调用测试函数
SELECT autonomous_test();
 autonomous_test 
-----------------
              42
(1 row)

-- 查看结果
SELECT * FROM test1;
 a 
---
 0
 2
 4
 6
 8
(5 rows)

TRUNCATE test1;


CREATE FUNCTION autonomous_test2() RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 0..9 LOOP
    START TRANSACTION;
    -- 不支持spi prepared statement
    -- 与当前自治事务的设计有关
    INSERT INTO test1 VALUES (i);
    IF i % 2 = 0 THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
  END LOOP;

  RETURN 42;
END;
$$;

SELECT autonomous_test2();
 autonomous_test2 
------------------
               42
(1 row)

SELECT * FROM test1;
 a
---
  
  
  
  
  
(5 rows)

目前这个PATCH还在fix，请注意跟踪，也许后面的用法就变了。

When running in-process (SPI), we install parser hooks that allow the
parser to check back into PL/pgSQL about whether x, y are variables and
what they mean.  When we run in an autonomous session, we don't have
that available.  So my idea was to extend the protocol Parse message to
allow sending a symbol table instead of parameter types.  So instead of
saying, there are two parameters and here are their types, I would send
a list of symbols and types, and the server would respond to the Parse
message with some kind of information about which symbols it found.  I
think that would work, but I got lost in the weeds and didn't get very
far.  But you can see some of that in the code.  If anyone has other
ideas, I'd be very interested.

参考

http://blog.dalibo.com/2016/09/08/Pragma_Autonoumous_transactions.html

http://blog.dalibo.com/2016/08/19/Autonoumous_transactions_support_in_PostgreSQL.html

https://www.postgresql.org/message-id/659a2fce-b6ee-06de-05c0-c8ed6a01979e@2ndquadrant.com

https://www.postgresql.org/message-id/flat/659a2fce-b6ee-06de-05c0-c8ed6a01979e%402ndquadrant.com#659a2fce-b6ee-06de-05c0-c8ed6a01979e@2ndquadrant.com

PostgreSQL 11已支持函数内COMMIT，支持自治事务。

https://www.postgresql.org/docs/devel/static/plpgsql-porting.html

digoal’s 大量PostgreSQL文章入口

Twitter Facebook Google+ LinkedIn

Digoal.zhou

PostgreSQL Oracle 兼容性之 - plpgsql 自治事务(autonomous_transaction)补丁

背景

用法

安装补丁

重启数据库

语法讲解

测试

参考

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

PostgreSQL plpgsql 存储过程、函数 - 状态、异常变量打印、异常捕获… - GET [STACKED] DIAGNOSTICS

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法