Damien Sawyer's questions

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!

Estamos construindo um sistema CQRS apoiado pelo Postgres. Como parte disso, geramos 'readmodels' que são projeções pré-calculadas de dados originados de eventos. Precisamos regularmente criar novas tabelas, consultar dados originados de eventos, calcular uma projeção e inserir milhares de linhas (principalmente contendo dados jsonb) em novas tabelas.

Essas inserções precisam ser feitas dentro de uma transação que também inclui leituras e gravações em outras tabelas.

Nosso código inicial usava INSERT ... ON CONFLICT UPDATEcomandos padrão e alcançava cerca de 100 linhas por segundo(). Quando, no entanto, alterei para usar COPY ... FROM STDIN (FORMAT BINARY)a taxa de transferência melhorou por um fator de cerca de 100. Estou ciente de que não posso fazer 'upserts' da mesma maneira com o segundo método, mas estava planejando excluir registros antes da inserção COPY ( dentro de uma transação).

Estou pensando em usar o fluxo COPY da nossa aplicação. No entanto, recebi críticas do tipo "Duvido que funcione. COPY foi projetado para importação em massa. Você terá problemas de simultaneidade".

Minha intuição é que, com o MVCC e o DDL transacional do Postgres, provavelmente é robusto o suficiente para lidar com algo assim sem suar a camisa. Eu tive medo, incerteza e dúvida semeados em mim embora!

Minhas perguntas:

1/ Posso usar COPY em transações normais como faria em outras declarações?

2/ Os carregamentos de COPY em massa podem acontecer enquanto o banco de dados ainda está ativo com inserções de produção normais e seleções ainda em execução em outras tabelas?

3/ Várias conexões podem executar simultaneamente operações de CÓPIA? (em mesas diferentes)

4/ Estou louco por pensar em tratar isso como qualquer outro comando do PG? Ele foi projetado para ser usado em bancos de dados de produção ao vivo ou é mais uma ferramenta de manutenção.

Estamos usando NPGSQL como a biblioteca cliente do Postgres.

Obrigado!