cis's questions

No Postgres 13, tenho uma tabela que é atualizada com frequência. No entanto, a consulta de atualização é bastante complicada e usa os mesmos valores várias vezes. Então, usar um CTE parece uma coisa bastante lógica a se fazer.

Um exemplo simplificado se parece com isso:

WITH my_cte AS (
    SELECT
          my_id,
          CASE WHEN my_value1 > 100 THEN 50 ELSE 10 END AS my_addition     
    FROM my_table      
    WHERE my_id = $1
)
UPDATE my_table
        SET my_value1 = my_table.my_value1 + my_cte.my_addition,
            my_value2 = my_table.my_value2 + my_cte.my_addition
FROM my_cte
WHERE my_table.my_id = my_cte.my_id

Agora estou me perguntando: O que aconteceria se entre o SELECTno CTE e o UPDATE, a tabela fosse atualizada por outra consulta, alterando my_value1assim, o cálculo de my_additionficasse desatualizado e errado quando UPDATEisso acontecesse. Tal situação pode ocorrer? Ou o Postgres define um bloqueio implícito automaticamente?

Se o Postgres não faz mágica aqui e eu mesmo preciso cuidar disso: seria suficiente fazer FOR UPDATEno SELECTCTE?

Desculpe se não fui claro aqui: Não é que eu queira "ver" essas modificações simultâneas, eu quero evitá-las, ou seja, uma vez que o cálculo SELECTé feito, nenhuma outra consulta pode modificar essa mesma linha até que UPDATEseja feito.

Na vida real, o que eu zombei aqui CASE WHEN my_value1 > 100 THEN 50 ELSE 10 ENDtem cerca de 20 linhas e eu preciso disso em cerca de 5 lugares no arquivo UPDATE. Como sou um grande fã de "Não se repita", acho que um CTE é o caminho a percorrer. Ou existe uma maneira melhor de evitar copiar e colar em um UPDATEsem CTE?

Eu tenho uma consulta como

UPDATE my_table 
  SET my_value = 
    CASE WHEN random() > 0.5 THEN my_value * 2 ELSE my_value END
RETURNING *;

Agora, dentro da RETURNINGdeclaração eu gostaria de ter um booleano indicando se my_valuefoi alterado pela consulta atual ou não.

Vamos supor que não posso passar o valor anterior de my_valuecomo parâmetro para a consulta.

Então, existe uma maneira de obter algo como uma lista de colunas que possuem valores diferentes após o UPDATE? Ou obter os valores no estado antes UPDATEem RETURNING?

No meu exemplo, eu poderia, claro, colocar o resultado de random()em um CTE como

WITH random_cte AS (
    SELECT random() AS my_random
)
UPDATE my_table 
  SET my_value = 
    CASE WHEN my_random > 0.5 THEN my_value * 2 ELSE my_value END
FROM random_cte
RETURNING *, my_random > 0.5 AS value_changed;

Mas isso aumentaria um pouco a consulta. Então eu estou querendo saber se eu poderia fazer isso de uma forma mais elegante?

Usando o PostgreSQL 11. Considere uma tabela como

CREATE TABLE "logs" 
    (
      "id" INTEGER NOT NULL,
      "userId" INTEGER NOT NULL, 
      "timestamp" TIMESTAMP NOT NULL,
      CONSTRAINT "PK_8d33b9f1a33b412e4865d1e5465" PRIMARY KEY ("id")
     )

Agora, o requisito é que apenas 100 linhas sejam armazenadas por userId. Se mais dados entrarem, os logs mais antigos devem ser excluídos. Se, por um curto período de tempo, 101 linhas forem armazenadas, não será o fim do mundo. Tudo bem se a linha supérflua for excluída com alguns segundos de atraso.

Não consigo criar um banco de dados TRIGGER. Então, eu preciso escrever uma consulta que é acionada em um evento de criação de log na camada do aplicativo.

O SQL puro é preferível ao plpgsql.

Esta é a solução que encontrei:

WITH "userLogs" AS (SELECT id, timestamp FROM "logs"
                    WHERE "userId" = $1
                ),
"countLogs" AS (SELECT count(id) FROM "userLogs")
        
DELETE FROM "logs" WHERE id = ANY
                (
                    SELECT id FROM "userLogs" 
                    ORDER BY "timestamp" ASC 
                    LIMIT GREATEST( (SELECT count FROM "countLogs") - 100, 0)
                );

A ideia é: Sempre execute a DELETEe baseie a decisão se realmente algo tiver que ser excluído em LIMITuma subconsulta. Se houver mais de 100 logs, a subconsulta retornará os ids dos mais antigos a serem descartados. Caso contrário, LIMITserá 0, a subconsulta não retornará nada e nada será excluído.

Minhas perguntas agora são:

1. É sensível executar uma DELETEconsulta em cada um INSERT- mesmo que não exclua nada?
2. Existem implicações de desempenho aqui? (Ou outras armadilhas que talvez eu não conheça?)
3. Não tenho certeza se preciso de um LOCK. Em meus testes, não consegui produzir nenhum comportamento inesperado ao executar INSERTs em paralelo, no entanto, pode haver casos de borda em que eu precisaria de um LOCK?

Edit : é difícil prever quantas vezes um INSERTserá executado nessa tabela. Se tudo correr bem (em termos de negócios), pode ser alguns milhares de vezes por dia em soma - e algumas dezenas de vezes por usuário a cada dia.

Edit 2 : timestampos valores não são necessariamente únicos por usuário: pode haver várias entradas de log com o mesmo timestampe o mesmo userId. Espera-se que a tabela obtenha mais colunas contendo o que realmente aconteceu.

Usando o PostgreSQL 12, tenho o requisito de que alguns dados precisem ser "atualizados" a cada noite. Isso significa: O banco de dados precisa fazer alguns cálculos com base na data atual e gravar o resultado na tabela. Isso funciona em geral. No entanto, a maneira como funciona não é perfeita, ao que parece.

Basicamente, existe uma consulta PLV8 como esta:

FAÇA $$

var myData = plv8.execute('SELECT id FROM my_table');
for(let i = 0; < myData.length; i++) {
    plv8.execute('SELECT my_function_which_refreshes_lots_of_data($1)',[myData[i].id);
}
$$ LANGUAGE plv8;

my_function_which_refreshes_lots_of_datapega o id de um item e faz todo o trabalho. Isso precisa ser feito dessa maneira, porque o acionamento da atualização também precisa ser possível para linhas únicas às vezes.

O problema é que de alguma forma isso saiu do controle. Atualmente, a consulta é executada por mais de uma hora, ocupa 99% da memória, preenche o espaço de troca e fica a maior parte do tempo no status "D" - "suspensão do disco (ininterrupta)". A duração da consulta não é um problema, pois geralmente é executada durante a noite, acionada por um cronjob. No entanto, o consumo de memória e o status do processo (basicamente como um zumbi) é ou pode ser um problema.

Então, minha ideia foi: Até onde eu sei, o PostgreSQL mantém todas as alterações nas tabelas na memória enquanto a consulta é executada e as grava apenas no final. Seria possível acionar manualmente "gravar no disco o que você tem até agora e liberar a memória"?

Eu faria isso no meu código depois plv8.execute('SELECT my_function_which_refreshes_lots_of_data($1)',[myData[i].id);. É claro que, se o processo travar ou qualquer outra coisa, isso resultaria em um estado inconsistente: parte dos itens seria processada por my_function_which_refreshes_lots_of_data, enquanto o restante permaneceria como antes. No entanto, isso é melhor do que nenhum item sendo escrito. E definitivamente melhor do que uma consulta rodando por tanto tempo e consumindo toda a memória.

Eu sei que poderia de alguma forma tentar dividir a consulta no lado do cliente. No entanto, isso seria menos conveniente para mim por alguns motivos.

Recentemente descobri um comportamento do PostgreSQL que é estranho e problemático na minha opinião.

Considere esta consulta simples

SELECT 
   'something confidential'
WHERE FALSE;

A cláusula sem sentido WHERE FALSEse assemelha aqui a uma verificação de permissão muito estrita. Isso funciona como esperado: nada é retornado.

Agora, imagine que você adiciona uma coluna adicional, por exemplo, uma countfunção. Isso dá é esta consulta:

SELECT 
   'something confidential'
   ,count(CURRENT_DATE)
WHERE FALSE;

(O parâmetro de countpoderia ser qualquer coisa. CURRENT_DATEé apenas um exemplo aleatório.)

Agora, obtemos

algo confidencial | 0

Eu acho que isso é de alguma forma de propósito, no sentido de que SELECT count(CURRENT_DATE);retorna 1 se a condição for verdadeira e 0 se a condição for falsa.

No entanto, considero isso problemático em situações em que você não está ciente disso. Então, minhas perguntas são

1. Por quê? Qual é o pano de fundo desse comportamento?
2. Como posso fazer essa consulta retornar zero linhas em vez de uma linha com valor 0 para count?
3. Existe uma maneira direta na consulta para evitar o retorno acidental de linhas que você não deseja retornar adicionando funções de agregação? (Ou seja, além de testes externos, etc.)

Estou usando o PostgreSQL 12.1, mas o comportamento é o mesmo para versões mais antigas.

Dando sequência à minha pergunta sobre algumas consultas no Postgres 12 serem mais lentas do que no 11 , acho que consegui diminuir o problema. Parece que um CTE recursivo baseado em valores de função é o ponto problemático.

Consegui isolar uma consulta SQL bastante pequena que é executada significativamente mais no Postgres 12.1 do que no Postgres 11.6, como ca 150ms no Postgres 12.1 vs ca 4ms no Postgres 11.6. Consegui reproduzir o fenômeno em vários sistemas: em várias VMs no VirtualBox; via Docker em duas máquinas físicas diferentes. (Consulte o apêndice para os comandos do docker). No entanto, estranho o suficiente, não consigo reproduzi-lo em https://www.db-fiddle.com/ (não há diferença a ser vista lá, ambos são rápidos).

Agora para a consulta. Primeiro, criamos esta função simples

CREATE OR REPLACE FUNCTION public.my_test_function()
 RETURNS SETOF record
 LANGUAGE sql
 IMMUTABLE SECURITY DEFINER
AS $function$ 

SELECT 
        1::integer AS id,
        '2019-11-20'::date AS "startDate",
        '2020-01-01'::date AS "endDate"

$function$;

Então, para a consulta real

WITH  "somePeriods" AS  (
      SELECT * FROM my_test_function() AS 
      f(id integer, "startDate" date, "endDate" date)
),

"maxRecursiveEndDate" AS (

SELECT "startDate", "endDate", id, 
( 
  WITH RECURSIVE prep("startDateParam", "endDateParam") AS (

  SELECT "startDate","endDate" FROM "somePeriods" WHERE id = od.id
  UNION
  SELECT "startDate","endDate" FROM "somePeriods", prep
  WHERE
    "startDate" <= ("endDateParam" + '1 day'::interval ) AND ("endDateParam" + '1 day'::interval ) <= "endDate"
  )
  SELECT max("endDateParam") FROM prep
) AS "endDateNew"

FROM "somePeriods" AS od

)

SELECT * FROM "maxRecursiveEndDate";

O que isso realmente faz não é tão importante aqui, eu acho. O ponto importante é provavelmente que existem vários CTEs envolvidos, incluindo RECURSIVEum.

O que eu tentei:

• Eu tentei sem my_test_function, ou seja, colocando os valores diretamente no primeiro CTE. Dessa forma, não houve problema algum. Corre igualmente rápido em 12 e em 11.
• No Postgres 12, eu brinquei com MATERIALIZED, mas não consegui ver nenhum efeito. A consulta ainda é executada tão lenta quanto antes.

Não sei se isso pode realmente ser um bug do Postgres 12 (ou regressão de desempenho) ou se estou perdendo algo aqui.

Apêndice: Comandos do Docker que usei para reproduzir

Primeiro, extraia imagens de ambas as versões

docker pull postgres:12.1
docker pull postgres:11.6

Agora, execute o Postgres 12

docker run -d --name my_postgres_12_container postgres:12.1

Agora, execute a consulta

docker exec my_postgres_12_container psql -U postgres -c "

CREATE OR REPLACE FUNCTION public.my_test_function()
 RETURNS SETOF record
 LANGUAGE sql
 IMMUTABLE SECURITY DEFINER
AS \$function\$ 

SELECT 
        1::integer AS id,
        '2019-11-20'::date AS \"startDate\",
        '2020-01-01'::date AS \"endDate\"

\$function\$;

EXPLAIN ANALYZE WITH  \"somePeriods\" AS  (
      SELECT * FROM my_test_function() AS 
      f(id integer, \"startDate\" date, \"endDate\" date)
),

\"maxRecursiveEndDate\" AS (

SELECT \"startDate\", \"endDate\", id, 
( 
  WITH RECURSIVE prep(\"startDateParam\", \"endDateParam\") AS (

  SELECT \"startDate\",\"endDate\" FROM \"somePeriods\" WHERE id = od.id
  UNION
  SELECT \"startDate\",\"endDate\" FROM \"somePeriods\", prep
  WHERE
    \"startDate\" <= (\"endDateParam\" + '1 day'::interval ) AND (\"endDateParam\" + '1 day'::interval ) <= \"endDate\"
  )
  SELECT max(\"endDateParam\") FROM prep
) AS \"endDateNew\"

FROM \"somePeriods\" AS od

)

SELECT * FROM \"maxRecursiveEndDate\";
"

Pare o contêiner Postgres 12

docker stop my_postgres_12_container

Inicie o Postgres 11 para comparação

docker run -d --name my_postgres_11_container postgres:11.6

Execute a consulta no Postgres 11

docker exec my_postgres_11_container psql -U postgres -c "

CREATE OR REPLACE FUNCTION public.my_test_function()
 RETURNS SETOF record
 LANGUAGE sql
 IMMUTABLE SECURITY DEFINER
AS \$function\$ 

SELECT 
        1::integer AS id,
        '2019-11-20'::date AS \"startDate\",
        '2020-01-01'::date AS \"endDate\"

\$function\$;

EXPLAIN ANALYZE WITH  \"somePeriods\" AS  (
      SELECT * FROM my_test_function() AS 
      f(id integer, \"startDate\" date, \"endDate\" date)
),

\"maxRecursiveEndDate\" AS (

SELECT \"startDate\", \"endDate\", id, 
( 
  WITH RECURSIVE prep(\"startDateParam\", \"endDateParam\") AS (

  SELECT \"startDate\",\"endDate\" FROM \"somePeriods\" WHERE id = od.id
  UNION
  SELECT \"startDate\",\"endDate\" FROM \"somePeriods\", prep
  WHERE
    \"startDate\" <= (\"endDateParam\" + '1 day'::interval ) AND (\"endDateParam\" + '1 day'::interval ) <= \"endDate\"
  )
  SELECT max(\"endDateParam\") FROM prep
) AS \"endDateNew\"

FROM \"somePeriods\" AS od

)

SELECT * FROM \"maxRecursiveEndDate\";
"