Highgo瀚高数据库PL/SQL 中增加commit注意事项

by

在我之前的blog中有记录<PostgreSQL系(GaussDB、KingBase…) 在 SELECT 和 TRUNCATE之间lock冲突>,从oracle迁移到基于postgresql的数据时的需要注意的地方，在那篇有记录select 会堵塞truncate, truncate又会堵塞所有的现象，有时truncate是在存储过程中从oracle迁移而来，在oracle中truncate是DDL，会触发隐式提交，但PostgreSQL的事务更符合我们对事务的理解，DDL并不会隐士提交，那如何实现本篇演示一下。

create procedure  demo
as
begin
  truncate table xxx;

-- select a long query;
end;
/

如上场景，因为是一个事务，那执行存储过程时，因为truncate持有锁并未隐式提交，那后面一个长事务或长查询会导致，前面TRUNCATE持有锁时间加长，从而影响其他事务，这是我们迁移到postgresql存储过程遇到最常见的性能问题。

下面我实际演示一下，数据库环境highgoDB v9

-- session 1

$ sh conn_orcl.sh
psql (14.20)
hgdb-client-V9.0.5
Type "help" for help.

highgo=# \c
hgdb-client-V9.0.5
You are now connected to database "highgo" as user "highgo".
highgo=# create table test(id int);
CREATE TABLE
highgo=# insert into test values(1);
INSERT 0 1
highgo=# begin;
BEGIN
highgo=*# select * from test;
 id
----
  1
(1 row)

-- session 2
highgo=# select * from test;
 id
----
  1
(1 row)

highgo=# truncate test;
-- waiting ....

-- session 3
highgo=# select * from test;
-- waiting ...

-- session 4
highgo=# SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
   pid   | usename |        query        | blocking_id |   blocking_query
---------+---------+---------------------+-------------+---------------------
 3411812 | highgo  | truncate test;      |     3411924 | select * from test;
 3414729 | highgo  | select * from test; |     3411812 | truncate test;

Note: 可以看到堵塞链条事务中的select 堵塞truncate, truncate 堵塞后续的select（或相关对象其他事务）。

同理，如果前面没有select 堵truncate, truncate后一个大查询，同样会堵塞其他事务，主要出现在存储过程中，下面创建个demo。

CREATE OR REPLACE PROCEDURE p_test
AS
BEGIN
 truncate table test;

 select pg_sleep(60);
 insert into test values(2);
 commit;
END;

-- session 1
highgo=# exec p_test;


-- session 2
highgo=# select * from test;
-- waiting...

-- session 3
highgo=# SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
   pid   | usename |        query        | blocking_id |   blocking_query
---------+---------+---------------------+-------------+--------------------
 3411812 | highgo  | select * from test; |     3510944 | BEGIN p_test; END;
(1 row)

Note： 可见在sleep过程中，同样堵塞了select 的会话。

这是因为oracle truncate DDL 会隐式提交，结束事务，但PG不会，而PG又是我们更好理解的，一个存储过程中不希望隐式提交。但truncate提交也有好处，就是释放了锁资源， 那我们在存储过程truncate 下面手动增加commit;试试

highgo=# CREATE OR REPLACE PROCEDURE p_test
highgo-# AS
highgo-# BEGIN
highgo-#  truncate table test;
highgo-#  commit;
highgo-#  select pg_sleep(60);
highgo-#  insert into test values(2);
highgo-#  commit;
highgo-# END;
highgo-# /
CREATE PROCEDURE

highgo=# exec p_test;
ERROR:  invalid transaction termination
CONTEXT:  PL/iSQL function p_test() line 3 at COMMIT
SQL statement "CALL p_test()"
PL/iSQL function inline_code_block line 1 at CALL

highgo=# CREATE OR REPLACE PROCEDURE p_test
highgo-# AS
highgo-# BEGIN
highgo-#  truncate table test;
highgo-#  commit;
highgo-#  select pg_sleep(60);
highgo-#
highgo-#  commit;
highgo-#  EXCEPTION
highgo-#       WHEN OTHERS THEN
highgo-#         RAISE NOTICE '错误描述:%', SQLERRM;
highgo-# END;
highgo-# /
CREATE PROCEDURE
highgo=# exec p_test;
NOTICE:  错误描述:invalid transaction termination

PL/iSQL procedure successfully completed.

highgo=# \df+ p_test
                                                                                           List of functions
 Schema |  Name  | Result data type | Argument data types | Type | Volatility | Parallel | Owner  | Security | Access privileges | Language |
     Source code                 | Description
--------+--------+------------------+---------------------+------+------------+----------+--------+----------+-------------------+----------+------------
---------------------------------+-------------
 public | p_test |                  |                     | proc | volatile   | unsafe   | highgo | definer  |                   | plisql   | BEGIN
                                +|
        |        |                  |                     |      |            |          |        |          |                   |          |  truncate t
able test;                      +|
        |        |                  |                     |      |            |          |        |          |                   |          |  commit;
                                +|
        |        |                  |                     |      |            |          |        |          |                   |          |  select pg_
sleep(60);                      +|
        |        |                  |                     |      |            |          |        |          |                   |          |
                                +|
        |        |                  |                     |      |            |          |        |          |                   |          |  commit;
                                +|
        |        |                  |                     |      |            |          |        |          |                   |          |  EXCEPTION
                                +|
        |        |                  |                     |      |            |          |        |          |                   |          |       WHEN
OTHERS THEN                     +|
        |        |                  |                     |      |            |          |        |          |                   |          |         RAI
SE NOTICE '错误描述:%', SQLERRM;+|
        |        |                  |                     |      |            |          |        |          |                   |          | END
                                 |
(1 row)

解决方法从定义者到 调用者，

  • AUTHID DEFINER (默认)：
    • 在 HighGo/PostgreSQL 中，定义者权限的过程通常被视为“安全定义者”（Security Definer）。
    • 出于安全考虑，数据库严禁在安全定义者函数/过程中执行 COMMIT 或 ROLLBACK，除非是在非常特定的顶层调用场景下。这就是为什么你之前一直报事务错误。
  • AUTHID CURRENT_USER：
    • 这相当于“安全调用者”（Security Invoker）。
    • 因为它使用的是当前用户的权限，数据库认为它更像是在执行当前用户的直接命令，因此对事务控制（COMMIT）的限制通常会放宽（或者行为更符合直觉）。

highgo=# CREATE OR REPLACE PROCEDURE p_test
highgo-# AUTHID CURRENT_USER
highgo-# AS
highgo-# BEGIN
highgo-#  truncate table test;
highgo-#  commit;
highgo-#  select pg_sleep(60);
highgo-#
highgo-#  commit;
highgo-#  EXCEPTION
highgo-#       WHEN OTHERS THEN
highgo-#         RAISE NOTICE '错误描述:%', SQLERRM;
highgo-# END;
highgo-# /
CREATE PROCEDURE
highgo=# exec p_test;
NOTICE:  错误描述:cannot commit while a subtransaction is active

PL/iSQL procedure successfully completed.

PostgreSQL（以及 HighGo 等兼容数据库）中 EXCEPTION 语句块的机制与 COMMIT 事务控制语句是互斥的。一旦你的存储过程代码中包含 EXCEPTION 块，整个代码块就被视为一个“子事务”（类似保存点），而在子事务内部是绝对禁止执行 COMMIT 的。当 PL/pgSQL 解析器看到 EXCEPTION 子句时，它会自动在后台开启一个 子事务 (Subtransaction)（相当于 SAVEPOINT）。

在 PostgreSQL/HighGo 中，COMMIT 和 EXCEPTION 不能在同一个存储过程的执行块中共存。这是数据库的内核限制。

推荐的代码修改（移除 EXCEPTION）

highgo=# CREATE OR REPLACE PROCEDURE p_test
highgo-# AUTHID CURRENT_USER
highgo-# AS
highgo-# BEGIN
highgo-#  truncate table test;
highgo-#  commit;
highgo-#  select pg_sleep(60);
highgo-#
highgo-#  commit;
highgo-# END;
highgo-# /
CREATE PROCEDURE

highgo=# exec p_test;
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/iSQL function p_test() line 4 at SQL statement
SQL statement "CALL p_test()"
PL/iSQL function inline_code_block line 1 at CALL

函数或存储过程中写了一个 SELECT 查询，但既没有把结果存入变量，也没有返回给调用者，数据库不知道该拿这些结果怎么办，所以建议你使用 PERFORM 来明确“丢弃结果”的意图。

如果你执行查询的目的是为了副作用（例如调用一个函数记录日志、更新状态），而不关心查询返回的具体数据，你应该使用 PERFORM 代替 SELECT。PERFORM 会执行查询，但会主动丢弃返回的结果集，从而避免报错。

highgo=# CREATE OR REPLACE PROCEDURE p_test
highgo-# AUTHID CURRENT_USER
highgo-# AS
highgo-# BEGIN
highgo-#  truncate table test;
highgo-#  commit;
highgo-#  PERFORM pg_sleep(60);
highgo-#  insert into test values(2);
highgo-#  commit;
highgo-# END;
highgo-# /
CREATE PROCEDURE
highgo=#

-- session 1
highgo=# exec p_test;
-- sleeping ...


-- session 2
highgo=# select * from test;
 id
----
(0 rows)

Note： 可见并没有堵塞，表为空，说明truncate生效，等session 1执行成功，session 可以查到insert的 2记录。

highgo=# exec p_test;

PL/iSQL procedure successfully completed.

highgo=# select * from test;
 id
----
  2

highgo=# \df+ p_test
                                                                                    List of functions
 Schema |  Name  | Result data type | Argument data types | Type | Volatility | Parallel | Owner  | Security | Access privileges | Language |         Source code          | Description
--------+--------+------------------+---------------------+------+------------+----------+--------+----------+-------------------+----------+------------------------------+-------------
 public | p_test |                  |                     | proc | volatile   | unsafe   | highgo | invoker  |                   | plisql   | BEGIN                       +|
        |        |                  |                     |      |            |          |        |          |                   |          |  truncate table test;       +|
        |        |                  |                     |      |            |          |        |          |                   |          |  commit;                    +|
        |        |                  |                     |      |            |          |        |          |                   |          |  PERFORM pg_sleep(60);      +|
        |        |                  |                     |      |            |          |        |          |                   |          |  insert into test values(2);+|
        |        |                  |                     |      |            |          |        |          |                   |          |  commit;                    +|
        |        |                  |                     |      |            |          |        |          |                   |          | END                          |
(1 row)

— over

Leave a Comment