Radu Dumbrăveanu's questions

Por que DISTINCT ONestá classificando as linhas? Por exemplo, a consulta

SELECT DISTINCT ON (a) a
FROM (
  VALUES (2), (3), (1)
) x(a);

terá o resultado

1
2
3

enquanto semDISTINCT ON

SELECT a
FROM (
  VALUES (2), (3), (1)
) x(a);

vou pegar o pedido correto

2
3
1

Como posso preservar a ordem natural dos valores definidos? Por "ordem natural" quero dizer "a ordem em que os valores foram definidos". Eu entendo que o DBMS pode alterar a ordem se eu não especificar explicitamente com um ORDER BY, mas não esperava que DISTINCT ONeu interferisse dessa maneira.

Vamos criar uma tabela temporária (eu escolho uma tabela temporária porque o autovacuum não funciona para esse tipo de tabela):

CREATE TEMP TABLE test (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  value int
);
INSERT INTO test (value) SELECT 0 FROM generate_series(0, 250);
SELECT ctid, * FROM test;

Veremos que a tabela consiste em duas páginas:

(0,1)   1   0
(0,2)   2   0
...
(0,226) 226 0
(1,1)   227 0
...
(1,25)  251 0

Agora, se atualizarmos uma linha

UPDATE test SET value = -1 WHERE id = 1;
SELECT ctid, * FROM test WHERE value <> 0;

veremos que a nova versão de linha foi inserida no final da tabela (na segunda página, que tem espaço livre suficiente para esta operação) e este é um comportamento padrão do PostgreSQL (a versão de linha antiga de (0,1) era marcado como morto)

(1,26)  1   -1

Vamos verificar o espaço livre das páginas:

CREATE EXTENSION pg_freespacemap;
SELECT * FROM pg_freespace('test');

Nós temos

0   0
1   0

ou seja, sem espaço livre; documentações dizem que o Free Space Map (FSM) é atualizado após a execução do vácuo. Agora, se atualizarmos outra linha da primeira página:

UPDATE test SET value = -1 WHERE id = 2;
SELECT ctid, * FROM test WHERE value <> 0;

vamos ver

(0,227) 2   -1
(1,26)  1   -1

A nova versão de linha não foi adicionada à 2ª página, mas foi adicionada à página atual, porque criamos espaço livre quando atualizamos o registro com id 1. Mas essa abordagem não corresponde ao meu entendimento de FSM. Aqui estão minhas perguntas:

1. Como o PostgreSQL soube no segundo UPDATE que havia espaço livre na primeira página se pg_freespace('test')retornasse zero? Eu pensei que esta função lhe dá o FSM que por sua vez é usado no caso de UPDATEs para decidir qual página tem espaço livre suficiente para armazenar a nova versão da linha.
2. Geralmente eu estava pensando que, mesmo que a primeira linha estivesse marcada como morta, ela ocuparia algum espaço na 1ª página e esse espaço será liberado somente após o vácuo. Assim, eu esperava que o segundo UPDATE também adicionasse a nova versão de linha à 2ª página.

Vamos supor que uma tabela foose tornou elegível para análise de autovacuum, por exemplo, eu inseri um número de linhas fooque excede autovacuum_analyze_scale_factor * number of rows + autovacuum_analyze_thresholdo limite efetivo. Não consigo descobrir quais são as condições que devem ser satisfeitas para que o autovacuum execute a análise no foo.

A análise de autovacuum será executada para a tabela foosomente se não houver transações? Ou ele pode ser executado mesmo durante transações ativas com as condições que: (1) nenhuma transação está lendo uma versão anterior da tabela fooe (2) a análise de autovacuum pode ganhar SHARE UPDATE EXCLUSIVEbloqueio na tabela foo?

Estas questões estão relacionadas com o caso particular. Eu executo um número significativo de INSERTs fooem uma transação e, em seguida, alguns UPDATEs fooem uma segunda transação. Eu preciso que a análise de autovacuum seja executada antes da 2ª transação para ter estatísticas atualizadas foopara melhores estimativas do meu planejador de consultas. Existe uma maneira de garantir que a análise de autovacuum seja executada antes da 2ª transação? Talvez para dormir (estou executando as duas transações do aplicativo Java) alguns milissegundos entre as transações?

Vamos criar duas tabelas de teste em um banco de dados PostgreSQL 13:

CREATE TABLE foo (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  value int NOT NULL
);
CREATE TABLE bar (
  id bigint PRIMARY KEY,
  category_id bigint NOT NULL,
  foo_id bigint REFERENCES foo (id),
  value int
);
CREATE INDEX bar_category_id_ix ON bar (category_id);

e desative autovacuumpara estas tabelas:

ALTER TABLE foo SET (autovacuum_enabled = false);
ALTER TABLE bar SET (autovacuum_enabled = false);

Insira 500.000 (meio milhão) registros em foo, transfira-os bare analise as tabelas:

INSERT INTO foo (value) SELECT * FROM generate_series(1, 500000);
ANALYZE foo;
INSERT INTO bar (id, category_id, foo_id, value) SELECT id, 1, id, value FROM foo WHERE value <= 500000;
ANALYZE bar;

Opcionalmente, assegure-se de que apenas ANALYZE(não autovacuum) foi executado nestas tabelas:

SELECT relname, last_autovacuum, last_vacuum, last_autoanalyze, last_analyze FROM pg_stat_user_tables WHERE relname IN ('foo', 'bar');

Insira outro pedaço de 500.000 registros (mas não execute ANALYZE):

INSERT INTO foo (value) SELECT * FROM generate_series(500001, 1000000);
INSERT INTO bar (id, category_id, foo_id, value) SELECT id, 2, id, value FROM foo WHERE value > 500000;

Como não executamos ANALYZEas estatísticas da tabela está desatualizada, está relacionada ao estágio em que fooe barcontinha meio milhão de registros. Agora vamos verificar os planos de consulta:

EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id
WHERE category_id = 2;
----
Nested Loop  (cost=0.85..12.89 rows=1 width=40)
  ->  Index Scan using bar_category_id_ix on bar  (cost=0.42..4.44 rows=1 width=28)
        Index Cond: (category_id = 2)
  ->  Index Scan using foo_pkey on foo  (cost=0.42..8.44 rows=1 width=12)
        Index Cond: (id = bar.foo_id)

e

EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id;
---
Hash Join  (cost=32789.00..71320.29 rows=999864 width=40)
  Hash Cond: (bar.foo_id = foo.id)
  ->  Seq Scan on bar  (cost=0.00..17351.64 rows=999864 width=28)
  ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12)
        ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=12)

Entendo que o 1º plano de consulta estimou erroneamente apenas 1 linha ( rows=1) para condição category_id = 2porque a estatística está desatualizada (o ANALYZEfoi realizado antes de inserir registros com category_id = 2). (1) Mas então, como o 2º plano de consulta chegou a uma boa estimativa ( rows=999864) para condição bar.foo_id = foo.id?

Também se executarmos:

EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id
WHERE category_id = 1;
----
Hash Join  (cost=32789.00..73819.95 rows=999864 width=40)
  Hash Cond: (bar.foo_id = foo.id)
  ->  Seq Scan on bar  (cost=0.00..19851.30 rows=999864 width=28)
        Filter: (category_id = 1)
  ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12)
        ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=12)

(2) Por que o planejador estima 999864 linhas para condição category_id = 1? As estatísticas devem mostrar cerca de 500.000 linhas satisfazendo isso?

NOTA: Cheguei a essas questões porque empiricamente observei que condições contendo apenas colunas de chave primária produzirão um melhor plano de consulta mesmo que a tabela não tenha sido analisada, mas não encontrei nada sobre esse comportamento na documentação oficial do PostgreSQL.

Em relação a esse tipo de erro no log do PgBouncer:

2020-03-10 03:46:01.730 EDT [25305] LOG C-0xa5cf00: testdb/[email protected]:49214 closing because: query_wait_timeout (age=120s)
2020-03-10 03:46:01.730 EDT [25305] WARNING C-0xa5cf00: testdb/[email protected]:49214 pooler error: query_wait_timeout

Existe alguma possibilidade de ver (ou registrar) a consulta que acionou o erro? Quais são as causas mais frequentes para esse erro? um servidor parado, um servidor ocupado?

ATUALIZAÇÃO: o arquivo de log do PostgreSQL não mostra nenhum erro em 2020-03-10 03:46:01

Existe alguma diferença significativa (no desempenho, por exemplo) entre os tipos character varyingvs. textas para os parâmetros da função db? Tenho 2 exemplos de uso:

Exemplo 1:

CREATE OR REPLACE FUNCTION func1(table_name <character varying|text>)
  RETURNS integer AS $$
DECLARE
  RESULT integer;
BEGIN
  EXECUTE 'SELECT COUNT(*) FROM ' || table_name INTO result;
  RETURN result;
END; 
$$ LANGUAGE plpgsql;

Exemplo 2:

CREATE OR REPLACE FUNCTION func2(filter_value <character varying|text>)
  RETURNS integer AS $$
DECLARE
  RESULT integer;
BEGIN
  SELECT COUNT(*) INTO RESULT FROM <some_table> WHERE <some_column> = filter_value;
  RETURN result;
END; 
$$ LANGUAGE plpgsql;

NOTA no 2º exemplo o some_columntipo é textse filter_valuefor do tipo character varyinge vice-versa

É possível no PostgreSQL conceder funções/privilégios temporários a um usuário? Estou pensando nisso como uma solução para a situação em que há a necessidade de solucionar um problema e um usuário pode precisar de algumas funções/privilégios específicos (mesmo superusuário) apenas por um curto período de tempo.

Na documentação doCREATE FUNCTION é declarado que ''Você deve possuir a função para substituí-la (isso inclui ser um membro da função de proprietário).''

Duas questões:

1. Eu não entendo o que exatamente a parte "isso inclui ser um membro do papel de proprietário" significa.

2. É possível ter vários usuários (não superusuários) que podem modificar o corpo do mesmo conjunto de funções? Tenho um usuário que cria as funções, tabelas etc (usuário Liquibase) e quero adicionar um usuário debug que deve ter permissão para modificar funções criadas pelo primeiro usuário para testar algumas suposições ou hotfixes?