Início / dba / Perguntas / 249446
Accepted
Damien Sawyer
Asked: 2019-09-24 16:35:57 +0800 CST

Não é possível criar COMMIT dentro do procedimento do PostgreSQL 11.5

  • 772

Estou tentando aprender procedimentos armazenados do PostgreSQL. Especificamente criando o procedimento abaixo em PSQL.

CREATE OR REPLACE PROCEDURE BUILD_AND_POPULATE(INOUT cresults refcursor) 
    LANGUAGE PLPGSQL 
    AS $$
    BEGIN
        BEGIN; -- I've tried removing this but the behaviour is the same
        cresults:= 'cur';
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;  
        COMMIT;
        OPEN cresults FOR SELECT * FROM procsampledata;  
    END;
$$;

Então eu executo assim, mas recebo um erro:

postgres=# call build_and_populate(null);
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function build_and_populate(refcursor) line 6 at COMMIT

Eu tentei com AUTOCOMMIT definido para ligado e desligado.

Esta é a minha versão do Postgres

 PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

Alguém pode ver o que estou fazendo de errado? Obrigada!

postgresql plpgsql

2 respostas

  1. Best Answer

    o

    BEGIN;

    está errado e causará um erro. Você não pode iniciar uma transação dentro de um procedimento, pois já existe uma transação ativa.

    Você pode encerrar uma transação, o que implica que uma nova transação seja iniciada imediatamente.

    Sem o BEGIN;, seu procedimento funciona bem.

    O problema deve estar na forma como você está chamando. Como diz a documentação :

    O controle de transações só é possível em invocações do nível superior ou invocações CALL ou DO aninhadas sem qualquer outro comando interveniente CALL. DOPor exemplo, se a pilha de chamadas for CALL proc1()→ CALL proc2()→ CALL proc3(), então o segundo e o terceiro procedimentos podem realizar ações de controle de transação. Mas se a pilha de chamadas for CALL proc1()→ SELECT func2()→ CALL proc3(), então o último procedimento não pode fazer o controle da transação, por causa do meio SELECT-termo.

    Provavelmente há um SELECTna sua pilha de chamadas.

    Outra possibilidade é que você tenha iniciado explicitamente uma transação BEGIN antes de chamar o procedimento armazenado.

    Isso também não funciona, que é uma restrição de implementação não documentada que pode ser corrigida no futuro. Veja este tópico para referência.

    • 5

  2. Depois de estabelecer que BEGIN;não é permitido em um procedimento e que o lado do cliente CALLnão deve ocorrer a partir de uma transação já iniciada, a questão se resume a (como você escreveu como comentário):

    "isso significa que não é possível retornar um cursor de um procedimento, pois (no meu entendimento) eles não sobrevivem após a transação"

    Ainda é possível. Existem dois tipos de CURSORs: WITH HOLDtransações de sobrevivência eWITHOUT HOLD (o padrão) não. Você pode apontar uma variável refcursor para um WITH HOLDcursor em plpgsql com um pouco de SQL dinâmico. Aqui está uma versão modificada do seu procedimento fazendo isso:

    CREATE OR REPLACE PROCEDURE public.build_and_populate(INOUT cresults refcursor)
 LANGUAGE plpgsql
AS $procedure$
    BEGIN
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;
        COMMIT;
        EXECUTE 'DECLARE ' || quote_ident(cresults::text) || ' CURSOR WITH HOLD FOR SELECT * FROM procsampledata';                                               
    END;
$procedure$

    postgres=# call BUILD_AND_POPULATE('res');
 cresults 
----------
 res
(1 row)

postgres=# fetch all from res;
 x  | c2 | c3 |               md5                
----+----+----+----------------------------------
  1 |  1 |  2 | 11a3d2e637332a25118c2f4d5dac49c0
  2 |  1 |  2 | 4c5b9cb5ec79479a9daa9ae7a131a078
  3 |  1 |  2 | 320b49912e94de90c3370836706c5494
  4 |  1 |  2 | a9ccf45dbbbbec19d18b69cf4a0fc26b
  5 |  1 |  2 | 1a52801f1761cb0e357a4468803473e1
  6 |  1 |  2 | 96c395dad11ee19526bc08b5ad114905
  7 |  1 |  2 | 7d0da9a0cf2520ee871185677d7062cf
  8 |  1 |  2 | 65ecf1e68806a0b9133fa0e47056574e
  9 |  1 |  2 | bae5c688e1ef6b9c389b37ecc7a18342
 10 |  1 |  2 | ed07142f3b51a282c05370890a8af468
(10 rows)

postgres=# close res;
CLOSE CURSOR
    • 2

relate perguntas