Início / dba / Perguntas / 273213
Accepted
Gandolf989
Asked: 2020-08-05 11:23:33 +0800 CST

Eu preciso fazer commits periódicos na minha função de purga

  • 772

Eu escrevi uma função de limpeza que roda no Postgres 9.6. A função passa por todas as tabelas em nosso aplicativo, começando da tabela filho inferior e terminando com a tabela pai superior e limpa com base na data ou no cliente. Quando executamos isso em produção e beta, há outros processos acontecendo ao mesmo tempo e o banco de dados está obtendo bloqueios de bloqueio com base em minhas exclusões não confirmadas. Uma solução possível seria fazer um commit a cada mil ou mais linhas. Mas sou novo no Postgres e não consigo fazer com que o Postgres 9.6 faça commits dentro de um loop. É possível que o Postgres 9.6 não faça isso e talvez o Postgres 12 o faça. Estamos migrando para o Postgres 12.

Existe alguma maneira de fazer os commits funcionarem no Postgres 9.6? Incluí um código de teste para que você possa testar os commits por conta própria. Meu código real é muito mais complicado.

Obrigado

\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 respostas

  1. Best Answer

    O controle de transações (commit, rollback) não é permitido dentro das funções do PostgreSQL.

    A partir do PostgreSQL 11, é possível em procedimentos (veja CREATE PROCEDURE na documentação). Na verdade, essa é a principal diferença entre funções e procedimentos.

    Antes do PostgreSQL 11, a capacidade de confirmar dentro de um loop vinha da programação além do SQL do lado do cliente. É claro que a psqlCLI pode emitir commit e rollback, mas não possui algumas das construções básicas encontradas em linguagens de programação, como loops.

    • 1

  2. Fiz um teste para confirmar usando um procedimento e funciona bem.

    Obrigado

    \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

relate perguntas