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
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
psql
CLI pode emitir commit e rollback, mas não possui algumas das construções básicas encontradas em linguagens de programação, como loops.Fiz um teste para confirmar usando um procedimento e funciona bem.
Obrigado