PostgreSQL之procedure的各种坑

开源软件联盟PostgreSQL分会 2020-09-25

12991

文章转载自公众号：PostgreSQL学徒

前言

今天有个朋友问我，PostgreSQL中是否支持像Oracle Function一样BEGIN END，正好之前也在社区分享过一篇《PostgreSQL函数实战》，自认为对函数算是很熟悉了，于是乎脱口而出就回答可以，结果很尴尬，PostgreSQL的函数中无法实现事务部分提交，换句话说，函数中的SQL要么都执行成功，要不全部返回失败，整体做为一个事务。好在PostgreSQL11之后实现了Procedure，同时支持存储过程嵌入事务，从兼容层面考虑和Oracle的兼容性进一步增强。

实战

2.1.Function

postgres=# create table test(id int);
CREATE TABLE
复制

直接参照官网的例子，依葫芦画瓢改成函数，创建成功没有问题

postgres=# create or replace function test1() returns void
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$#     for i in 0..9 loop
postgres$#         insert into test1 (id) values (i);
postgres$#         if i % 2 = 0 then
postgres$#             commit;
postgres$#         else
postgres$#             rollback;
postgres$#         end if;
postgres$#     end loop;
postgres$# end
postgres$# $$;
CREATE FUNCTION
postgres=# \df test1
                       List of functions
 Schema | Name  | Result data type | Argument data types | Type
--------+-------+------------------+---------------------+------
 public | test1 | void             |                     | func
(1 row)
复制

但是执行就会报错了，提示不能有事务相关的语句。

postgres=# select * from transaction_test1();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function transaction_test1() line 6 at COMMIT
复制

关于PostgreSQL函数的这一特性，即在最初的解析过程中只能检测到一些显而易见的临时语法错误，只有真正执行之后，才会检测到更深的错误，各有所见吧，好处有就是减少不必要的解析，坏处自然就是可能函数里面的bug和一些流程上的错误只有真正触发了才会发现。

官网上详细注明了这一块The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree the first time the function is called (within each session). The instruction tree fully translates the PL/pgSQL statement structure, but individual SQL expressions and SQL commands used in the function are not translated immediately.直接google翻译一下，PL pgSQL解释器解析函数的源文本，并在第一次调用函数时(在每个会话中)生成内部二进制指令树。指令树完全转换了PL pgSQL语句结构，但是函数中使用的各个SQL表达式和SQL命令不会立即转换。

比如创一个含有id和info两个字段的表，但创建的函数里面却故意写成select info_mytest from xxx

postgres=# create table test1(id int,info text);
CREATE TABLE
postgres=# insert into test1 select n,n||'test' from generate_series(1,10)n;
INSERT 0 10
然后创个函数：
create or replace function mytestf22(v_id int ) returns text
as $$
declare
       tmp text;
begin
       if v_id > 2 then
              select info_mytest from test1 where id = v_id into tmp;
              return tmp;
       else
              select info from test1 where id = v_id into tmp;
              return tmp;
       end if;
end;
$$ language plpgsql;
正常调用：
postgres=# select mytestf22(1);
 mytestf22
-----------
 1test
(1 row)
这样就会报错，因为走到了第一个流程分支内：
postgres=# select mytestf22(3);
ERROR:  column "info_mytest" does not exist
LINE 1: select info_mytest from test1 where id = v_id
               ^
QUERY:  select info_mytest from test1 where id = v_id
CONTEXT:  PL/pgSQL function mytestf22(integer) line 6 at SQL statement
复制

官网上还有一句介绍，However, procedures and functions of different argument types can share a name (this is called overloading).类似于Java中的重载，假如直接改个类型为funtion而不变动其他会报错

postgres=# create or replace function test1() returns void
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$#     for i in 0..9 loop
postgres$#         insert into test1 (id) values (i);
postgres$#         if i % 2 = 0 then
postgres$#             commit;
postgres$#         else
postgres$#             rollback;
postgres$#         end if;
postgres$#     end loop;
postgres$# end
postgres$# $$;
CREATE FUNCTION
postgres=# \df test1
                       List of functions
 Schema | Name  | Result data type | Argument data types | Type
--------+-------+------------------+---------------------+------
 public | test1 | void             |                     | func
(1 row)
postgres=# create or replace procedure test1()
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$#     for i in 0..9 loop
postgres$#         insert into test1 (id) values (i);
postgres$#         if i % 2 = 0 then
postgres$#             commit;
postgres$#         else
postgres$#             rollback;
postgres$#         end if;
postgres$#     end loop;
postgres$# end
postgres$# $$;
ERROR:  cannot change routine kind
DETAIL:  "test1" is a function.
复制

2.2.Procedure

2.2.1.普通使用

再看看procedure会是怎样，直接引用官网例子

CREATE OR REPLACE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test2(id) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END
$$;
复制

需要用call调用

postgres=# select transaction_test2();
ERROR:  transaction_test2() is a procedure
LINE 1: select transaction_test2();
               ^
HINT:  To call a procedure, use CALL.
postgres=# call transaction_test2();
CALL
postgres=# select * from test2;
 id
----
  0
  2
  4
  6
  8
(5 rows)
复制

当然稍加改造一下，function也是能实现相同效果的

CREATE function transaction_test3() returns void
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        IF i % 2 = 0 THEN
            INSERT INTO test2(id) VALUES (i);
        ELSE
            continue;
        END IF;
    END LOOP;
END $$;
postgres=# select * from transaction_test3();
 transaction_test3
-------------------

(1 row)
postgres=# select * from test2;
 id
----
  0
  2
  4
  6
  8
  0
  2
  4
  6
  8
(10 rows)
复制

2.2.2.诸多限制

引入了嵌套事务后，procedure有诸多局限性。

2.2.2.1.不能放在事务里

postgres=# CREATE OR REPLACE PROCEDURE transaction_test2()
postgres-# LANGUAGE plpgsql
postgres-# AS $$
postgres$# BEGIN
postgres$#     FOR i IN 0..9 LOOP
postgres$#         INSERT INTO test2(id) VALUES (i);
postgres$#         IF i % 2 = 0 THEN
postgres$#             COMMIT;
postgres$#         ELSE
postgres$#             ROLLBACK;
postgres$#         END IF;
postgres$#     END LOOP;
postgres$# END
postgres$# $$;
CREATE PROCEDURE
postgres=# begin;
BEGIN
postgres=# call transaction_test2();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function transaction_test2() line 6 at COMMIT
postgres=# rollback ;
ROLLBACK
postgres=# call transaction_test2();
CALL
复制

而函数是可以的

postgres=# begin;
BEGIN
postgres=# select * from transaction_test3();
 transaction_test3
-------------------

(1 row)
postgres=# rollback;
ROLLBACK
复制

2.2.2.2.在函数体内调用procedure

postgres=# \df test1
                       List of functions
 Schema | Name  | Result data type | Argument data types | Type
--------+-------+------------------+---------------------+------
 public | test1 |                  |                     | proc
(1 row)
postgres=# create or replace function test2() returns void
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$# call test1();
postgres$# end
postgres$# $$;
CREATE FUNCTION
postgres=# select test2();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function test1() line 6 at COMMIT
SQL statement "CALL test1()"
PL/pgSQL function test2() line 3 at CALL
复制

改成函数也是可以的，不过需要注意调用方式

postgres=# drop procedure test1;
DROP PROCEDURE
postgres=# truncate table test1;
TRUNCATE TABLE
postgres=# create or replace function test1() returns void
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$#     for i in 0..9 loop
postgres$#         if i % 2 = 0 then
postgres$#             insert into test2(id) values (i);
postgres$#         else
postgres$#             continue;
postgres$#         end if;
postgres$#     end loop;
postgres$# end $$;
CREATE FUNCTION
postgres=# \df test1
                       List of functions
 Schema | Name  | Result data type | Argument data types | Type
--------+-------+------------------+---------------------+------
 public | test1 | void             |                     | func
(1 row)
postgres=# create or replace function test2() returns void
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$# call test1();
postgres$# end
postgres$# $$;
CREATE FUNCTION
Function不能使用call
postgres=# select test2();
ERROR:  test1() is not a procedure
LINE 1: CALL test1()
             ^
HINT:  To call a function, use SELECT.
QUERY:  CALL test1()
CONTEXT:  PL/pgSQL function test2() line 3 at CALL
改成select也不行，得用perform
postgres=# create or replace function test2() returns void
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$# select test1();
postgres$# end
postgres$# $$;
CREATE FUNCTION
postgres=# select test2();
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function test2() line 3 at SQL statement
若不关心函数的返回值，得换成PERFORM
postgres=# create or replace function test2() returns void
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$# perform test1();
postgres$# end
postgres$# $$;
CREATE FUNCTION
postgres=# select test2();
 test2
-------

(1 row)
postgres=# select * from test;
 id
----
  0
  2
  4
  6
  8
(5 rows)
复制

对于perform需要特别注意，在PLPGSQL里面不允许单独的select，必须得放在select into或者单独的return query里面，这一点我之前也没有注意到，由于日常的编写习惯都是select into或者for in ... return next等，导致都忽略了这一点，在写这篇博文的时候才发现

postgres=# create or replace function func3() returns setof test
postgres-# as $$
postgres$# begin
postgres$# select * from test;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# select func3();
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function func3() line 3 at SQL statement
定义为return void也不行
postgres=# create or replace function func4() returns void
postgres-# as $$
postgres$# begin
postgres$# select * from test;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# select func4();
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function func4() line 3 at SQL statement
复制

在SQL里面，对应的是select来调用，在PLPGSQL里面，就对应的perform了

PERFORM is plpgsql command used for calls of void functions. PLpgSQL is careful about useless SELECT statements - the SELECT without INTO clause is not allowed. But sometimes you need to call a function and you don't need to store result (or functions has not any result). The function in SQL is called with SELECT statement. But it is not possible in PLpgSQL - so the command PERFORM was introduced.

SQL语言
postgres=# create or replace function func5() returns void
postgres-# as $$
postgres$# select now();
postgres$# $$ language sql;
CREATE FUNCTION
postgres=# select func5();
 func5
-------

(1 row)
PLPGSQL语言
postgres=# create or replace function func5() returns void
postgres-# as $$
postgres$# begin
postgres$# select now();
postgres$# end
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# select func5();
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function func5() line 3 at SQL statement

换成perform即可
postgres=# create or replace function func5() returns void
postgres-# as $$
postgres$# begin
postgres$# perform now();
postgres$# end
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# select func5();
 func5
-------

(1 row)
复制

同理，对应嵌套函数也是一样

postgres=# \d test_func
             Table "public.test_func"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
postgres=# create or replace function test_func1(v_id int) returns void
postgres-# as $$
postgres$# begin
postgres$# insert into test_func values(v_id);
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=#
postgres=# create or replace function test_func2() returns void
postgres-# as $$
postgres$# begin
postgres$# select test_func1(5);
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# select test_func2();
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function test_func2() line 3 at SQL statement
postgres=# select * from test_func ;
 id
----
(0 rows)
得换成perform
postgres=# create or replace function test_func3() returns void
postgres-# as $$
postgres$# begin
postgres$# perform test_func1(5);
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# select test_func3();
 test_func3
------------

(1 row)

postgres=# select * from test_func ;
 id
----
  5
(1 row)
复制

2.2.2.3.create procedure的时候同时设定参数

postgres=# CREATE PROCEDURE transaction_test6()
LANGUAGE plpgsql
SET work_mem = 8
AS $$
BEGIN
    COMMIT;
END;
$$;
CREATE PROCEDURE
postgres=# call transaction_test6();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function transaction_test6() line 3 at COMMIT
复制

2.2.2.4.嵌套调用的时候，使用execute

postgres=# create table test_pro(id int);
CREATE TABLE
postgres=# create or replace procedure test_pro1()
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$#     for i in 0..9 loop
postgres$#         insert into test_pro(id) values (i);
postgres$#         if i % 2 = 0 then
postgres$#             commit;
postgres$#         else
postgres$#             rollback;
postgres$#         end if;
postgres$#     end loop;
postgres$# end
postgres$# $$;
CREATE PROCEDURE
嵌套调用
postgres=# create or replace procedure test_pro2()
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$#     call test_pro1();
postgres$# end;
postgres$# $$;
CREATE PROCEDURE
postgres=# call test_pro2();
CALL
postgres=# select * from test_pro ;
 id
----
  0
  2
  4
  6
  8
(5 rows)

postgres=# call test_pro2();
CALL
postgres=# select * from test_pro ;
 id
----
  0
  2
  4
  6
  8
  0
  2
  4
  6
  8
(10 rows)
换成动态执行会报错
postgres=# create or replace procedure test_pro2()
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$#     execute 'call test_pro1()';
postgres$# end;
postgres$# $$;
CREATE PROCEDURE
postgres=# call test_pro2();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function test_pro1() line 6 at COMMIT
SQL statement "call test_pro1()"
PL/pgSQL function test_pro2() line 3 at EXECUTE
复制

2.2.2.5.捕获异常和rollback的冲突

postgres=# do language plpgsql $$
postgres$# begin
postgres$#     begin
postgres$#         insert into test_pro(id) values (1);
postgres$#         rollback;
postgres$#         insert into test_pro(id) values (1/0);
postgres$#         rollback;
postgres$#     exception
postgres$#         when division_by_zero then
postgres$#             raise notice 'caught division_by_zero';
postgres$#     end;
postgres$# end;
postgres$# $$;
ERROR:  cannot roll back while a subtransaction is active
CONTEXT:  PL/pgSQL function inline_code_block line 5 at ROLLBACK
postgres=# do language plpgsql $$
postgres$# begin
postgres$#     begin
postgres$#         insert into test_pro(id) values (1);
postgres$#         commit;
postgres$#         insert into test_pro(id) values (1/0);
postgres$#         rollback;
postgres$#     exception
postgres$#         when division_by_zero then
postgres$#             raise notice 'caught division_by_zero';
postgres$#     end;
postgres$# end;
postgres$# $$;
ERROR:  cannot commit while a subtransaction is active
CONTEXT:  PL/pgSQL function inline_code_block line 5 at COMMIT
复制

2.2.2.6.不支持savepoint

postgres=# create or replace procedure test_pro5()
postgres-# language plpgsql
postgres-# as $$
postgres$# begin
postgres$#     for i in 0..9 loop
postgres$#         insert into test1(id) values (i);
postgres$#         if i % 2 = 0 then
postgres$#             savepoint t1;
postgres$#         else
postgres$#             rollback;
postgres$#         end if;
postgres$#     end loop;
postgres$# end
postgres$# $$;
CREATE PROCEDURE
postgres=# call test_pro5();
ERROR:  unsupported transaction command in PL/pgSQL
CONTEXT:  PL/pgSQL function test_pro5() line 6 at SQL statement
复制

2.2.3.匿名块的处理

写着的过程中，我又想到了一个微妙的问题，对于匿名块，PostgreSQL是怎么处理的？因为定义匿名块的时候并不像我们create procedure或者create function那样显式指定到底是procedure还是function。

在12的版本中，可以在匿名块里使用事务相关语句

postgres=# select version();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
postgres=# do $$
postgres$# <>
postgres$# declare
postgres$#   counter integer := 0;
postgres$# begin
postgres$#    counter := counter + 1;
postgres$#    raise notice 'the current value of counter is %', counter;
postgres$# end first_block $$;
NOTICE:  the current value of counter is 1
DO
postgres=# do $$
postgres$# <>
postgres$# declare
postgres$#   counter integer := 0;
postgres$# begin
postgres$#    counter := counter + 1;
postgres$#    raise notice 'the current value of counter is %', counter;
postgres$#    commit;
postgres$# end first_block $$;
NOTICE:  the current value of counter is 1
DO
postgres=# truncate table test1;
TRUNCATE TABLE
postgres=# do $$
postgres$# begin
postgres$#     for i in 0..9 loop
postgres$#         insert into test1(id) values (i);
postgres$#         if i % 2 = 0 then
postgres$#             commit;
postgres$#         else
postgres$#             rollback;
postgres$#         end if;
postgres$#     end loop;
postgres$# end
postgres$# $$;
DO
postgres=# select * from test1;
 id | info
----+------
  0 |
  2 |
  4 |
  6 |
  8 |
(5 rows)
复制

在9版本里，就会报错了

postgres=# select version();
                                                  version                                                 
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
postgres=# do $$
postgres$# <>
postgres$# declare
postgres$#   counter integer := 0;
postgres$# begin
postgres$#    counter := counter + 1;
postgres$#    raise notice 'the current value of counter is %', counter;
postgres$#    commit;
postgres$# end first_block $$;
NOTICE:  the current value of counter is 1
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function inline_code_block line 8 at SQL statement
postgres=# create table test1(id int);
CREATE TABLE
postgres=# do $$
postgres$# begin
postgres$#     for i in 0..9 loop
postgres$#         insert into test1(id) values (i);
postgres$#         if i % 2 = 0 then
postgres$#             commit;
postgres$#         else
postgres$#             rollback;
postgres$#         end if;
postgres$#     end loop;
postgres$# end
postgres$# $$;
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function inline_code_block line 6 at SQL statement
复制

看样子多虑了，甚好。PostgreSQL的匿名块里也允许有事务相关语句。

这样的话，也方便测试了

postgres=# do language plpgsql $$
postgres$# begin
postgres$#     create table test3 (y int unique deferrable initially deferred);
postgres$#     commit;
postgres$#     insert into test3 (y) values (1);
postgres$#     commit;
postgres$#     insert into test3 (y) values (1);
postgres$#     insert into test3 (y) values (2);
postgres$#     commit;
postgres$#     insert into test3 (y) values (3);  -- won't get here
postgres$# end;
postgres$# $$;
ERROR:  relation "test3" already exists
CONTEXT:  SQL statement "create table test3 (y int unique deferrable initially deferred)"
PL/pgSQL function inline_code_block line 3 at SQL statement
postgres=# select * from test3;
 y
---
 1
(1 row)
复制

2.2.4.隔离级别的处理

写着的时候我又想到一个问题，隔离级别在procedure内部是怎么处理的？

开两个session，session 1
postgres=# create table test_mvcc(id int);
CREATE TABLE
postgres=# insert into test_mvcc values(1);
INSERT 0 1
session2里面每个1ms插入一条数据
postgres=# insert into test_mvcc values(3);\watch 0.001
INSERT 0 1
Sun 20 Sep 2020 12:15:26 PM CST (every 1s)

INSERT 0 1

Sun 20 Sep 2020 12:15:27 PM CST (every 1s)

INSERT 0 1

Sun 20 Sep 2020 12:15:28 PM CST (every 1s)

INSERT 0 1

Sun 20 Sep 2020 12:15:29 PM CST (every 1s)

INSERT 0 1
session1执行这个匿名块
DO LANGUAGE plpgsql $$
declare
       v_count int;
BEGIN
       commit;
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
       RAISE INFO '当前隔离级别是%', current_setting('transaction_isolation');
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
    COMMIT;
    SET TRANSACTION ISOLATION LEVEL read committed;
       RAISE INFO '当前隔离级别是%', current_setting('transaction_isolation');
    select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       select count(*) from test_mvcc into v_count;
       RAISE INFO '行数是：%', v_count;
       COMMIT;
END;
$$;

INFO:  当前隔离级别是repeatable read
INFO:  行数是：85717
INFO:  行数是：85717
INFO:  行数是：85717
INFO:  行数是：85717
INFO:  行数是：85717
INFO:  行数是：85717
INFO:  行数是：85717
INFO:  行数是：85717
INFO:  行数是：85717
INFO:  当前隔离级别是read committed
INFO:  行数是：85734
INFO:  行数是：85736
INFO:  行数是：85738
INFO:  行数是：85740
INFO:  行数是：85742
INFO:  行数是：85744
INFO:  行数是：85746
INFO:  行数是：85748
DO
复制

甚好甚好，隔离级别PostgreSQL也处理得当。

结语

算是弥补了一点之前函数的遗漏知识，procedure里面的嵌套事务进一步兼容Oracle，尤其是对于Oracle转到PostgreSQL的朋友们。但是还是有很多的限制存在，使用过程中要特别注意，相信后面的版本procedure会越来越强。


I Love PG
复制

关于我们
中国开源软件推进联盟PostgreSQL分会（简称：中国PG分会）于2017年成立，由国内多家PostgreSQL生态企业所共同发起，业务上接受工信部中国电子信息产业发展研究院指导。中国PG分会是一个非盈利行业协会组织。我们致力于在中国构建PostgreSQL产业生态，推动PostgreSQL产学研用发展。
复制

欢迎投稿

做你的舞台，show出自己的才华。

投稿邮箱：partner@postgresqlchina.com

——愿能安放你不羁的灵魂

技术文章精彩回顾




PostgreSQL学习的九层宝塔
PostgreSQL职业发展与学习攻略
2019，年度数据库舍 PostgreSQL 其谁?
Postgres是最好的开源软件
PostgreSQL是世界上最好的数据库
从Oracle迁移到PostgreSQL的十大理由
从“非主流”到“潮流”，开源早已值得拥有

PG活动精彩回顾




创建PG全球生态！PostgresConf.CN2019大会盛大召开
首站起航！2019“让PG‘象’前行”上海站成功举行
走进蓉城丨2019“让PG‘象’前行”成都站成功举行
中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行
群英论道聚北京，共话PostgreSQL
相聚巴厘岛| PG Conf.Asia 2019  DAY0、DAY1简报
相知巴厘岛| PG Conf.Asia 2019 DAY2简报
独家|硅谷Postgres大会简报
直播回顾 | Bruce Momjian：原生分布式将在PG 14版本发布

PG培训认证精彩回顾




中国首批PGCA认证考试圆满结束，203位考生成功获得认证！
中国第二批PGCA认证考试圆满结束，115位考生喜获认证！
重要通知：三方共建，中国PostgreSQL认证权威升级！
近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！
2020年首批 | 中国PostgreSQL初级认证考试圆满结束
一分耕耘一分收获，第五批次PostgreSQL认证考试成绩公布

PG专辑预览阅读
复制