主页 / dba / 问题 / 273213
Accepted
Gandolf989
Asked: 2020-08-05 11:23:33 +0800 CST

我需要在我的清除功能中定期提交

  • 772

我编写了一个在 Postgres 9.6 中运行的清除函数。该函数遍历我们应用程序中的所有表，从最底层的子表开始，到最顶层的父表结束，并根据日期或客户进行清除。当我们在生产和测试版中运行它时，会同时发生其他进程，并且数据库正在根据我未提交的删除获得阻塞锁。一种可能的解决方案是每隔一千左右行进行一次提交。但是我是 Postgres 的新手，我无法让 Postgres 9.6 在循环中进行提交。Postgres 9.6 可能不会这样做，而 Postgres 12 可能会。我们正在迁移到 Postgres 12。

有没有办法让提交在 Postgres 9.6 中工作？我包含了一些测试代码，以便您可以自己测试提交。我的实际代码要复杂得多。

谢谢

\timing

DROP TABLE IF EXISTS _tmp_test_transactions_table;
CREATE TABLE _tmp_test_transactions_table ( pkey INTEGER );

create or replace function _tmp_test_transactions ( p_number_of_rows INTEGER )
returns int language plpgsql
as
$fun$
DECLARE
   v_counter     INTEGER := 0;
   v_final_count INTEGER := 0;
BEGIN
   START TRANSACTION ISOLATION LEVEL READ COMMITTED;

   LOOP
      v_counter := v_counter + 1;
      EXIT WHEN v_counter > p_number_of_rows;
      
      IF MOD( v_counter, 10 ) = 0
      THEN
         COMMIT;
         START TRANSACTION ISOLATION LEVEL READ COMMITTED;
      END IF;
   END LOOP;

   SELECT COUNT(*) 
     INTO v_final_count
     FROM _tmp_test_transactions_table;

   RAISE NOTICE 'Inserted % rows in the _tmp_test_transactions_table table', v_final_count;
   COMMIT;
END
$fun$;

SELECT _tmp_test_transactions( 100 );
psql:bbyrd_test_transactions.sql:36: ERROR:  unsupported transaction command in PL/pgSQL
CONTEXT:  PL/pgSQL function _tmp_test_transactions(integer) line 6 at SQL statement
Time: 0.473 ms
postgresql transaction

2 个回答

  1. Best Answer

    PostgreSQL 函数中不允许事务控制（提交、回滚）。

    从 PostgreSQL 11 开始，可以在过程中使用（请参阅文档中的CREATE PROCEDURE）。事实上，这是函数和过程之间的主要区别。

    在 PostgreSQL 11 之前，在循环内提​​交的能力来自客户端超越 SQL 的编程。psqlCLI 当然可以发出提交和回滚，但它没有编程语言中的一些基本结构，例如循环。

    • 1

  2. 我运行了一个测试以使用一个程序提交，它运行良好。

    谢谢

    \timing
Timing is on.
DROP TABLE IF EXISTS _tmp_test_transactions_table;
DROP TABLE
Time: 38.583 ms
CREATE TABLE _tmp_test_transactions_table ( pkey INTEGER );
CREATE TABLE
Time: 17.007 ms
create or replace procedure _tmp_test_transactions ( p_number_of_rows INTEGER )
language plpgsql
as
$fun$
DECLARE
   v_counter     INTEGER := 0;
   v_final_count INTEGER := 0;
   v_dummy       INTEGER;
BEGIN
   TRUNCATE TABLE _tmp_test_transactions_table;
   LOOP
      v_counter := v_counter + 1;
      EXIT WHEN v_counter > p_number_of_rows;

      INSERT INTO _tmp_test_transactions_table VALUES ( v_counter );
      COMMIT;
   END LOOP;

   SELECT COUNT(*)
     INTO v_final_count
     FROM _tmp_test_transactions_table;

   RAISE NOTICE 'Inserted % rows in the _tmp_test_transactions_table table', v_final_count;

   RAISE EXCEPTION 'This is an error, roll back';
END
$fun$;
CREATE PROCEDURE
Time: 5.104 ms
CALL _tmp_test_transactions( 100 );
psql:run_commit_test.sql:34: NOTICE:  Inserted 100 rows in the _tmp_test_transactions_table table
psql:run_commit_test.sql:34: ERROR:  This is an error, roll back
CONTEXT:  PL/pgSQL function _tmp_test_transactions(integer) line 22 at RAISE
Time: 116.180 ms
SELECT COUNT(*) FROM _tmp_test_transactions_table;
 count
-------
   100
(1 row)
    • 0

相关问题