Aqui estão apenas algumas perguntas para começar:
- Os intervalos podem conter lacunas nos valores?
- Por que precisamos de notação de índice para o tipo json?
- Um índice pode crescer menos do que uma tabela com atualizações frequentes? E não crescer nada?
- Há quanto tempo as sessões estão inativas em idle_in_transaction?
- Como construir um diagrama ER para tabelas de catálogo do sistema?
Aplicativos clientes
psql: \ dX - ver estatísticas estendidas
commit: ad600bba
Novo comando \ dX exibe objetos de estatísticas estendidas como uma lista.
CREATE STATISTICS flights_from_to (dependencies, MCV)
ON departure_airport, arrival_airport
FROM flights;
\x
\dX
List of extended statistics -[ RECORD 1 ]+------------------------------------------------ Schema | bookings Name | flights_from_to Definition | departure_airport, arrival_airport FROM flights Ndistinct | Dependencies | defined MCV | defined
Para cada tipo de estatística (Dependências, Ndistinto, MCV), apenas o fato da coleta é exibido. Os próprios valores precisam ser examinados em pg_statistic_ext_data, que, por padrão, só pode ser acessado por superusuários.
psql: \ dtS mostra tabelas TOAST
commit: 7d80441d
Uma tabela TOAST separada pode ser visualizada com o comando \ d antes. No entanto, não foi possível obter uma lista dessas tabelas com o comando \ dt ou \ dtS. A omissão foi corrigida, \ dtS agora mostra as tabelas TOAST, pois são tabelas de serviço.
Mas há uma ressalva. Todas as tabelas TOAST estão localizadas no esquema pg_toast, que provavelmente não será incluído em search_path . Portanto, para obter a lista, você precisa especificar o modelo apropriado:
\dtS pg_toast.*165*
List of relations Schema | Name | Type | Owner ----------+----------------+-------------+---------- pg_toast | pg_toast_16529 | TOAST table | postgres pg_toast | pg_toast_16539 | TOAST table | postgres pg_toast | pg_toast_16580 | TOAST table | postgres
Conclusão da guia Psql aprimorada para os comandos CLOSE, FETCH, MOVE e DECLARE
commit: 3f238b88 Nenhuma
descrição adicional necessária.
Documentação
Verificação e edição da documentação
commit: 2a5862f0
Muitas pessoas notam que o PostgreSQL tem uma documentação excelente. Mas é escrito pelos próprios desenvolvedores, que geralmente não são considerados mestres da caneta. Como você consegue manter a alta qualidade? É simples. Como acontece com qualquer redação, você precisa de editores e revisores. E assim Justin Prizzby tem feito um trabalho enorme e importante nos últimos dois anos: revisar a documentação. O resultado é uma grande lista de 18 patches. E Mikael Paquier, como committer, o ajudou.
E esse é apenas um grande compromisso. E o número de pequenos patches que melhoram a documentação simplesmente não pode ser contado.
miscelânea
Parâmetro Idle_session_timeout - força o encerramento de sessões inativas
commit: 9877374b O
novo parâmetro idle_session_timeout especifica o tempo limite da sessão inativa. Se o limite de tempo definido for excedido, a sessão será encerrada. O parâmetro é muito semelhante ao idle_in_transaction_session_timeout ,que apareceu no 9.6 , mas afeta apenas as sessões nas quais não há transação iniciada. Portanto, se você deseja interromper sessões inativas, independentemente de uma transação ser iniciada nelas ou não, os dois parâmetros devem ser definidos.
Recomenda-se que este parâmetro seja usado com extremo cuidado em sistemas que usam pullers de conexão ou conexões postgres_fdw.
O parâmetro pode ser definido por qualquer usuário para sua sessão. No exemplo a seguir, após definir o parâmetro e aguardar um segundo, vemos que uma entrada de encerramento de sessão aparece no log do servidor. Depois disso, a tentativa de executar a solicitação falha, mas o psql estabelece automaticamente uma nova conexão:
SET idle_session_timeout = '1000ms';
--
\! tail -n 1 logfile
2021-02-01 12:25:06.716 MSK [5262] FATAL: terminating connection due to idle-session timeout
SHOW idle_session_timeout;
FATAL: terminating connection due to idle-session timeout server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.
SHOW idle_session_timeout;
idle_session_timeout ---------------------- 0
Descrição de depesz.
Informação GSS no envio da mensagem de log do servidor
: dc11f31a
Nova mensagem de conexão de sessão no log do servidor foi atualizada com informações GSS se este método de autenticação foi usado.
pageinspect: funções para índices GiST
commit: 756ab291
Para qualquer pessoa interessada em explorar a organização e armazenamento de índices GiST, a extensão pageinspect oferece novos recursos .
Comportamento correto do EXPLAIN em comandos com
confirmação IF NOT EXISTS : e665769e
Uma tentativa de criar uma tabela existente com a opção IF NOT EXISTS resulta em um aviso:
CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping
No entanto, obter um plano para tal comando leva a resultados inesperados. É que EXPLAIN gera um plano para o SELECT, que o comando consegue construir antes de verificar a existência da tabela de tickets. E sem avisos!
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
QUERY PLAN ----------------------------------------------------------------- Seq Scan on bookings (cost=0.00..4301.88 rows=262788 width=21)
EXPLAIN ANALYZE falha em vez de aviso:
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
ERROR: relation "tickets" already exists
Na versão 14, o comportamento tornou-se previsível:
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
Mesmas alterações para o comando EXPLAIN [ANALYZE] CREATE MATERIALIZED VIEW IF NOT EXISTS.
Adicionadas chaves primárias e exclusivas à
confirmação das tabelas do catálogo do sistema : dfb75e47 , 62f34097
Adicionadas restrições de integridade às tabelas do catálogo do sistema: chaves primárias e exclusivas. Antes havia índices únicos, agora as restrições são feitas com base neles.
Isto é o que parece:
\d pg_class
Table "pg_catalog.pg_class" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- oid | oid | | not null | relname | name | | not null | relnamespace | oid | | not null | ... ... Indexes: "pg_class_oid_index" PRIMARY KEY, btree (oid) "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace) "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
Mas há exceções: pg_depend, pg_shdepend. Essas duas tabelas têm dois índices e ambos não são exclusivos. Não foi possível chegar a uma combinação única de colunas. Devo admitir que entender como funciona a tabela de dependência não é uma tarefa trivial. E a incapacidade de criar uma chave primária é uma confirmação clara disso.
Mas as chaves estrangeiras não foram adicionadas. Há boas razões para isto:
- Para várias tabelas, as colunas oid podem ser 0 quando não há um OID real para se referir. Para criar uma chave estrangeira, você precisará substituir 0 por NULL em todos os lugares, e esse é um grande trabalho de reescrita que você não está pronto para assumir.
- Várias tabelas possuem uma coluna com uma chave estrangeira potencial não apenas do tipo oid, mas oid []. É impossível criar uma chave estrangeira a partir de um array.
A ideia original do patch era construir um diagrama das relações entre as tabelas no catálogo do sistema com base nas informações do banco de dados. Isso pode ser feito automaticamente por ferramentas externas. Afinal, construir um diagrama sem informações sobre chaves estrangeiras só pode ser feito manualmente e com alterações regulares após cada lançamento, o que é extremamente inconveniente.
Percebendo que a tarefa do patch não estava completa, imediatamente após o commit, Tom Lane iniciou uma nova discussão na qual ele propôs uma solução de compromisso na forma de uma função que retorna uma lista de chaves estrangeiras para todas as tabelas no catálogo do sistema. O patch foi adotado após o encerramento do commitfest de janeiro, mas é mais lógico descrevê-lo agora.
Portanto, as tabelas do catálogo do sistema não têm chaves estrangeiras. Mas podemos obter informações sobre eles chamando a função pg_get_catalog_foreign_keys. A consulta a seguir mostra quem está se referindo a pg_attribute:
SELECT fktable, fkcols, is_array, is_opt
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_attribute'::regclass
AND pkcols = ARRAY['attrelid','attnum'];
fktable | fkcols | is_array | is_opt ----------------------+-----------------------+----------+-------- pg_attrdef | {adrelid,adnum} | f | f pg_constraint | {conrelid,conkey} | t | t pg_constraint | {confrelid,confkey} | t | f pg_index | {indrelid,indkey} | t | t pg_statistic_ext | {stxrelid,stxkeys} | t | f pg_statistic | {starelid,staattnum} | f | f pg_trigger | {tgrelid,tgattr} | t | f pg_partitioned_table | {partrelid,partattrs} | t | t (8 rows)
Monitoramento
Parâmetro Log_recovery_conflict_waits - registrando longas esperas para resolver conflitos de recuperação
commit: 0650ff23
Quando o novo parâmetro log_recovery_conflict_waits é habilitado , a espera pela resolução de conflito de recuperação pelo processo de inicialização será registrada no log do servidor se o tempo limite exceder deadlock_timeout.
Vamos simular a situação. Na réplica, ative o parâmetro, inicie a transação e aguarde:
ALTER SYSTEM SET log_recovery_conflict_waits = on;
SELECT pg_reload_conf();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM t;
E agora no mestre:
DELETE FROM t;
VACUUM t;
Após um segundo ( deadlock_timeout ), uma mensagem detalhada descrevendo o conflito aparecerá no log da réplica. Aqui está o número do processo conflitante e a posição LSN onde eles travaram, e o nome do arquivo (tabelas lidas) com o número do bloco:
LOG: recovery still waiting after 1023.267 ms: recovery conflict on snapshot DETAIL: Conflicting process: 29119. CONTEXT: WAL redo at 0/1001BEB0 for Heap2/CLEAN: latestRemovedXid 717; blkref #0: rel 1663/16384/17198, blk 0
Após mais 30 segundos de espera ( max_standby_streaming_delay ), a sessão na réplica será encerrada, como deveria ser em tais casos.
Esta é uma continuação do trabalho iniciado e descrito no commitfest anterior.
Pg_stat_database view - estatísticas adicionadas nas sessões de usuário
commit: 960869da Os
desenvolvedores do sistema de monitoramento têm mais trabalho a fazer. Mudanças úteis e interessantes aguardam os usuários dos sistemas de monitoramento!
Muitas colunas apareceram em pg_stat_database com informações adicionais sobre as sessões de usuário em cada banco de dados no cluster:
- session_time - tempo total de todas as sessões gastas neste banco de dados;
- active_time ― , ;
- idle_in_transaction_time ― ;
- sessions ― ;
- sessions_abandoned ― - ;
- sessions_fatal ― - FATAL;
- sessions_killed ― .
Descrição de depesz.
ps: atualizando o status dos processos quando um ponto de verificação é executado
commit: df9274ad Você pode
monitorar os processos de inicialização e checkpointer consultando pg_stat_activity. Mas existem três situações em que a visualização pg_stat_activity não está disponível e o checkpointer funciona. Estes são o ponto de verificação no final do processo de recuperação de falha, o ponto de verificação durante a parada do servidor e o ponto de reinicialização durante a parada da réplica.
Nessas três situações, você pode monitorar o status dos processos de inicialização e checkpointer no sistema operacional, por exemplo, usando o utilitário ps.
Um exemplo típico é a recuperação de desastres. No final, após rolar as alterações do WAL, o processo de inicialização executa um checkpoint e isso pode levar algum tempo. No entanto, o status do processo de inicialização não muda e mostra "recuperando NNN". Embora seja útil saber que o rollforward de alterações foi concluído e ainda falta aguardar a conclusão do ponto de verificação. O status agora é atualizado para reduzir o nível de alerta do DBA em uma emergência.
pg_stat_statements: Quando as estatísticas de
commit foram liberadas : 2e0fedf0
Não há dúvidas de que as estatísticas de pg_stat_statements devem ser liberadas regularmente. Senão, de que adianta acumular informações sobre os pedidos executados ontem, uma semana atrás, um mês, um ano ...
Mas como você sabe quando as estatísticas foram redefinidas pela última vez? Muito simples. Analisamos pg_stat_statements_info:
SELECT now(), pg_stat_statements_reset();
now | pg_stat_statements_reset -------------------------------+-------------------------- 2021-02-03 13:25:44.738188+03 |
SELECT * FROM pg_stat_statements_info;
dealloc | stats_reset ---------+------------------------------- 0 | 2021-02-03 13:25:44.738468+03
A visualização pg_stat_statements_info foi introduzida na versão 14. Você pode ler sobre a coluna desalocar no artigo anterior.
Descrição de depesz.
Ainda mais útil é a ideia de não apenas despejar estatísticas regularmente, mas também salvar o conteúdo de pg_stat_statements antes de cada despejo. Então, na presença de muitos cortes realizados regularmente, é possível obter informações para intervalos de tempo no passado. Esta abordagem é usada pela extensão de monitoramento pgpro_pwr . Progresso do commit do
COPY
: 8a4f618e
A família de visualizações pg_stat_progress_ * foi atualizada! Agora você pode monitorar o progresso do comando COPY.
Vamos fazer uma cópia lógica do banco de dados de demonstração:
\! pg_dump -d demo -Fc -f demo.dump
Agora vamos expandir a cópia no banco de dados postgres em dois threads e, enquanto o processo está acontecendo, dê uma olhada na visão pg_stat_progress_copy:
\! pg_restore tickets.dump -d postgres -j 2 &
SELECT pid, datname, relid::regclass, bytes_processed, bytes_total, lines_processed
FROM pg_stat_progress_copy\gx
-[ RECORD 1 ]---+------------------------- pid | 18771 datname | postgres relid | bookings.tickets bytes_processed | 19088527 bytes_total | 0 lines_processed | 189820 -[ RECORD 2 ]---+------------------------- pid | 18772 datname | postgres relid | bookings.boarding_passes bytes_processed | 14833287 bytes_total | 0 lines_processed | 567652
A coluna bytes_total teria sido preenchida com o tamanho do arquivo quando o comando COPY… FROM 'arquivo' foi executado. Mas no exemplo acima, o download é de uma cópia do pg_dump, portanto, o tamanho não é conhecido.
O status pode ser monitorado não apenas para download (COPY ... FROM), mas também para download (COPY ... TO) de dados.
Descrição de depesz.
atuação
Otimização do esvaziamento da
confirmação do cache do buffer : d6ad34f3 , bea449c6
Várias operações exigem que todos os buffers associados a uma determinada tabela sejam removidos do cache do buffer. Essas operações incluem os comandos TRUNCATE e DROP table, um comando interrompido CREATE TABLE AS SELECT e VACUUM quando blocos vazios precisam ser removidos do final de uma tabela.
Para remover, todo o cache do buffer é verificado, o que pode ser caro com tamanhos de cache grandes. Agora, para tabelas pequenas, uma estrutura especial será mantida na memória com informações sobre os buffers ocupados, o que evitará a varredura de todo o cache do buffer.
Testes mostraram que com shared_buffers de 100 GB ou mais, truncar milhares de tabelas é mais de 100 vezes mais rápido.
Esta é uma continuação do trabalho iniciado na versão 13.
postgres_fdw: modo batch para inserir registros de
commit: b663a413 Buscando
dados de tabelas externas postgres_fdw usa modo batch. Os registros são transferidos do servidor externo em lotes de 100 (o valor padrão do parâmetro fetch_size ). Isso é significativamente mais rápido do que enviar um de cada vez. Mas insira, altere, exclua o trabalho linha por linha. E, portanto, muito lentamente.
A API FDW foi aprimorada para otimização. É verdade que foi melhorado apenas na parte do modo em lote para operações de inserção. Atualizações e exclusões são para referência futura. Obviamente, postgres_fdw foi o primeiro wrapper a aproveitar as vantagens da nova API.
Vamos ver o que aconteceu. Configure postgres_fdw para trabalhar com tabelas externas no banco de dados de demonstração:
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'postgres');
CREATE USER MAPPING FOR postgres
SERVER remote_server
OPTIONS (user 'postgres');
A tabela externa estará localizada no banco de dados postgres vizinho:
postgres=# CREATE TABLE bookings(
book_ref char(6),book_date timestamptz, total_amount numeric(10,2)
);
Vamos considerar a velocidade de inserção na tabela local como a velocidade de referência. E habilite o tempo para as medições:
CREATE TABLE bookings_local (LIKE bookings);
\timing
Insira em uma tabela local:
INSERT INTO bookings_local SELECT * FROM bookings;
INSERT 0 262788 Time: 165,653 ms
E agora insira em uma tabela externa, o modo em lote está desativado. (Na verdade está habilitado, mas por padrão o tamanho do lote é 1 linha).
CREATE FOREIGN TABLE bookings_remote_no_batch (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings'
);
INSERT INTO bookings_remote_no_batch SELECT * FROM bookings;
INSERT 0 262788 Time: 6729,867 ms (00:06,730)
Quase 40 vezes mais lento! E isso é entre as bases de um mesmo cluster, onde não há atrasos na rede.
Vamos repetir o experimento, mas definir o tamanho do lote (batch_size) para 100.
CREATE FOREIGN TABLE bookings_remote_batch_100 (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings',
batch_size '100'
);
INSERT INTO bookings_remote_batch_100 SELECT * FROM bookings;
INSERT 0 262788 Time: 679,632 ms
Outra questão. Claro, a perda para a inserção local ainda é perceptível, ~ 4 vezes, mas ainda não 40!
E finalmente. O tamanho do lote para inserção (batch_size) poderia ser definido nos parâmetros do servidor externo, então seria válido para todas as tabelas nas quais não esteja explicitamente definido.
Excluindo linhas de índice de
confirmação de baixo para cima : 9dc718bd , d168b666
Esta otimização tenta evitar a divisão da página de índice por duas nas últimas operações UPDATE, em situações em que as colunas do índice não mudaram. Antes de adicionar uma nova versão de uma linha ao índice, você precisa ver se pode remover linhas desnecessárias nesta página. Por exemplo, se você encontrar uma cadeia de linhas de índice duplicadas desnecessárias que fazem referência à mesma linha da tabela, poderá excluí-las. Peter Geigan, o autor do patch, chamou isso de "exclusão de baixo para cima".
Um problema semelhante (para evitar o crescimento do índice) é resolvido pela otimização de atualização do HOT. Se o UPDATE não alterar nenhuma das colunas indexadas, não será possível criar novas versões das linhas dos índices. E se houver vários índices na tabela e a coluna de apenas um deles mudar? Nesse caso, o HOT update não é um assistente.
Vamos verificar o que "excluir de baixo para cima" pode fazer. Para o experimento, vamos pegar uma tabela com duas colunas indexadas separadamente e limpeza automática desabilitada.
CREATE TABLE t(col1 int, col2 int) WITH (autovacuum_enabled=off);
CREATE INDEX t_col1 ON t(col1);
CREATE INDEX t_col2 ON t(col2);
INSERT INTO t VALUES (1, 1);
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size --------+-------------+------------- 8192 | 16384 | 16384
Há uma linha na tabela antes da atualização em massa. O tamanho da tabela é de uma página e ambos os índices ocupam duas páginas (página de serviço + página de dados).
Agora alteramos apenas uma coluna col2 100.000 vezes e olhamos o tamanho da tabela e dos índices.
SELECT 'UPDATE t SET col2 = col2+1' FROM generate_series(1,100000)\gexec
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 2121728 | 2260992
Esses resultados foram obtidos no PostgreSQL 12. Como você pode ver, a atualização HOT não funcionou e os dois índices aumentaram quase igualmente em tamanho.
Agora, este mesmo experimento no PostgreSQL 13:
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 663552 | 2260992
O índice t_col1, no qual não houve alterações, aumentou muito menos, cerca de 3,5 vezes. Este é o resultado de uma famosa otimização da versão 13: desduplicação de índice . Mesmo assim, ele cresceu.
E, finalmente, vamos ver o que há no PostgreSQL 14:
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 16384 | 2260992
Caramba! No índice t_col1, há apenas uma página com dados restantes. Isso é legal!
Claro, se o autovacuum estivesse ligado, poderia haver tempo para eliminar algumas das linhas mortas durante o experimento. Mas é para isso que serve o experimento. Além disso, em condições reais, com atualizações frequentes (um ótimo exemplo são as tabelas de filas), o autovacuum definitivamente não terá tempo para limpar tudo a tempo.
Descrição de Viktor Egorov.
Execução paralela REINDEX CONCURRENTLY
commit: f9900df5
No artigo commitfest de novembro, eu já escrevi sobre a execução paralela sem bloqueio de CREATE INDEX CONCURRENTLY. Otimização semelhante agora está disponível para REINDEX CONCURRENTLY.
Linguagens procedimentais
Procedimentos são mais rápidos para executar
commit: ee895a65
Procedimentos foram concebidos para serem capazes de completar transações. Se tal procedimento que executa COMMIT for chamado muitas vezes, por exemplo, em um loop, então, a cada iteração do loop, todas as instruções dentro do procedimento serão analisadas novamente.
Não havia nenhum motivo sério para analisar novamente os comandos, que foram eliminados no patch. Agora, chamar procedimentos em um loop requer menos trabalho e recursos. E, como resultado, ele é executado mais rápido.
PL / pgSQL: operador de atribuição completamente redesenhado
commit: 844fe9f1 , c9d52984 , 1788828d , 1c1cbe27
Sem mais delongas:
DO $$
<<local>>
DECLARE
a bookings[];
x bookings;
BEGIN
/* */
local.a[1].book_ref := 'ABCDEF';
local.a[1].book_date := current_date;
local.a[1].total_amount := 0;
/* */
local.a[2:3] := (SELECT array_agg(t.*)
FROM (SELECT b.* FROM bookings b LIMIT 2) AS t
);
FOREACH x IN ARRAY a LOOP
RAISE NOTICE '%', x;
END LOOP;
END;
$$;
NOTICE: (ABCDEF,"2021-02-04 00:00:00+03",0.00) NOTICE: (00000F,"2017-07-05 03:12:00+03",265700.00) NOTICE: (000012,"2017-07-14 09:02:00+03",37900.00) DO
Agora, dentro de um bloco PL / pgSQL, você pode atribuir valores a elementos de array de um tipo composto, bem como fatias de array.
Para tanto, o operador de atribuição PL / pgSQL foi completamente redesenhado. E o analisador do servidor aprendeu a analisar expressões PL / pgSQL.
Para avaliar uma expressão, você não precisa mais emitir um comando como
«
SELECT expr
»
. Você pode verificar isso facilmente observando a mensagem de erro no exemplo a seguir:
DO $$ BEGIN RAISE NOTICE '%', 2 + 'a'; END; $$;
ERROR: invalid input syntax for type integer: "a" LINE 1: 2 + 'a' ^ QUERY: 2 + 'a' CONTEXT: PL/pgSQL function inline_code_block line 1 at RAISE
A palavra SELECT não está mais na linha QUERY.
Replicação
Manipulação de réplica de alterações de parâmetro de configuração na
confirmação do assistente : 15251c0a
As alterações no assistente para configurações que afetam o tamanho da memória compartilhada do servidor não passam despercebidas em réplicas físicas. Quando um registro WAL chega à réplica informando que esses parâmetros foram alterados, a réplica irá parar automaticamente, encerrando todas as sessões atuais. A lista de parâmetros pode ser encontrada na documentação .
Não é muito bom. Portanto, fizemos o seguinte: recebendo um registro das alterações nos parâmetros, a réplica continua funcionando, mas pausa a replicação e emite uma mensagem para o log. O administrador pode esperar que sessões importantes terminem, atualizar os parâmetros nos arquivos de configuração para corresponder aos valores no mestre e retomar a replicação. A réplica irá parar imediatamente após isso, mas como as alterações na configuração já foram feitas, ela pode ser iniciada imediatamente, minimizando o tempo de inatividade.
Alterar restore_command sem reiniciar o servidor
commit: 942305a3
Continuação do trabalho de Sergei Kornilov, adotado na versão 13. Então tornou-se possível alterar os parâmetros sem reiniciar o servidor primary_conninfo , primary_slot_name e wal_receiver_create_temp_slot .
Agora, restore_command foi adicionado a eles .
Servidor
Uso aprimorado de
commit de estatísticas estendidas : 25a9e54d
Estatísticas estendidas agora são usadas em mais casos para avaliar a cardinalidade de condições em consultas. Em particular, as estatísticas estendidas agora serão usadas quando diferentes condições para as quais as estatísticas estendidas podem ser usadas individualmente forem combinadas via OR.
No exemplo, coletaremos estatísticas estendidas para os aeroportos de partida e chegada. E então contaremos o número de voos entre Sheremetyevo e Pulkovo ou na direção oposta.
CREATE STATISTICS s ON departure_airport, arrival_airport FROM flights;
ANALYZE flights;
O número exato de voos é 610. Compare com as estimativas do planejador nas versões 13 e 14.
EXPLAIN SELECT *
FROM flights
WHERE (departure_airport = 'SVO' AND arrival_airport = 'LED')
OR (departure_airport = 'LED' AND arrival_airport = 'SVO');
PostgreSQL 13:
Seq Scan on flights (cost=0.00..1054.42 rows=885 width=63)
PostgreSQL 14:
Seq Scan on flights (cost=0.00..1054.42 rows=607 width=63)
Como você pode ver, a estimativa na versão 14 é quase precisa.
Infraestrutura geral para suportar a notação de índice para qualquer
confirmação de tipo de dados : c7aba7c1 , 0ec5f7e7 , 676887a3 A
notação de índice é usada para trabalhar com matrizes. Por exemplo, vamos encontrar o elemento com índice 3:
SELECT (ARRAY[10,20,30,40,50])[3];
array ------- 30
Mas existem outros tipos de dados em que essa sintaxe seria conveniente de usar. Em primeiro lugar, estamos falando sobre json. Foi com a ideia de apoiar a notação de índice para json que a longa jornada de trabalho de Dmitry Dolgov neste patch começou.
E agora, alguns anos depois, esse apoio apareceu. O primeiro patch cria a infraestrutura de notação de índice necessária para tipos de dados arbitrários. O segundo patch adiciona notação de índice ao tipo hstore e o terceiro ao tipo json b .
Agora, em vez de funções e operadores especiais, você pode extrair as partes necessárias do valor json. Vamos encontrar o número de telefone nos detalhes de contato de um dos ingressos:
SELECT contact_data, contact_data['phone'] AS phone
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]+----------------------------------------------------------------------------------------------------------------------------------------------------------- contact_data | {"email": "antonova.irina04121972@postgrespro.ru", "phone": "+70844502960"} phone | "+70844502960"
A notação de índice também pode ser usada para gravar em jsonb. Vamos adicionar o endereço ao contato de Irina Antonova encontrado anteriormente:
UPDATE tickets
SET contact_data['address'] =
'{"city": "",
"street": " ",
"building": "7"
}'::jsonb
WHERE ticket_no = '0005432000994';
Observe que o endereço em si é composto e você também pode usar a notação de índice para se referir a partes dele:
SELECT contact_data['address'] AS address,
contact_data['address']['city'] AS city,
contact_data['address']['street'] AS street,
contact_data['address']['building'] AS building,
contact_data['phone'] AS phone,
contact_data['email'] AS email
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]---------------------------------------------------------------- address | {"city": "", "street": " ", "building": "7"} city | "" street | " " building | "7" phone | "+70844502960" email | "antonova.irina04121972@postgrespro.ru"
É muito conveniente!
(Esclarecimento. Todos os contatos no banco de dados de demonstração são fictícios e não existe tal funcionário no Postgres Pro.)
Descrição para hstore de depesz.
Comandos SQL
Confirmação de tipos de dados multirange
: 6df7a969
Cada tipo de dados de intervalo agora tem seu próprio tipo de dados multirange . Esse tipo é essencialmente uma matriz de intervalos individuais. Os intervalos em um tipo multibanda não devem se sobrepor, mas pode haver lacunas entre os intervalos.
Intervalos regulares são intervalos contíguos de valores do subtipo correspondente: intervalo in4range para subtipo int, intervalo timestamptz para subtipo de carimbo de data / hora, etc. Mas e se você precisar armazenar intervalos com lacunas em alguns lugares? É aqui que as bandas múltiplas vêm para o resgate.
Digamos que desejamos armazenar os tempos de commitfest na tabela para cada versão do PostgreSQL. Um único commitfest pode ser considerado um período de um mês. Mas como representar todos os cinco commitfests de uma versão?
O intervalo para o subtipo timestamptz é chamado tstzrange e o multirange é tstzmultirange. Os tipos disponíveis são descritos na documentação . Crie uma tabela:
CREATE TABLE pg_commitfest (
version text,
working_period tstzmultirange
);
Para formar valores, usamos o construtor:
INSERT INTO pg_commitfest VALUES
('13', tstzmultirange(
tstzrange('2019-07-01', '2019-08-01', '[)'),
tstzrange('2019-09-01', '2019-10-01', '[)'),
tstzrange('2019-11-01', '2019-12-01', '[)'),
tstzrange('2020-01-01', '2020-02-01', '[)'),
tstzrange('2020-03-01', '2020-04-07', '[]')
)
),
('14', tstzmultirange(
tstzrange('2020-07-01', '2020-08-01', '[)'),
tstzrange('2020-09-01', '2020-10-01', '[)'),
tstzrange('2020-11-01', '2020-12-01', '[)'),
tstzrange('2021-01-01', '2021-02-01', '[)'),
tstzrange('2021-03-01', '2021-04-01', '[)')
)
);
A lista de funções e operadores para trabalhar com tipos multibanda inclui o mesmo que para as faixas normais, mais aquelas destinadas a multibanda apenas.
Por exemplo, podemos descobrir em qual versão do PostgreSQL a comunidade de desenvolvedores trabalhou no último ano:
SELECT version
FROM pg_commitfest
WHERE working_period @> '2021-01-01'::timestamptz;
version --------- 14
Ou datas de início e término para o trabalho na versão 13:
SELECT lower(working_period), upper(working_period)
FROM pg_commitfest
WHERE version = '13';
lower | upper ------------------------+------------------------ 2019-07-01 00:00:00+03 | 2020-04-07 00:00:00+03
Novos tipos personalizados de vários intervalos podem ser criados. Isso é útil nos casos em que não há banda embutida e o tipo multibanda correspondente. O mesmo comando CREATE TYPE… AS RANGE é usado, no qual você pode especificar um nome para o tipo de intervalo múltiplo gerado automaticamente.
Por exemplo, estamos interessados em intervalos e vários intervalos de tempo, subtipo de tempo. Para criar um intervalo, você precisa de uma função que calcule a diferença entre dois valores do tipo tempo:
CREATE FUNCTION time_diff(a time, b time) RETURNS double precision
AS $$
SELECT extract(epoch FROM (a - b));
$$ LANGUAGE sql STRICT IMMUTABLE;
Criamos um tipo para o intervalo de tempo e, ao mesmo tempo, para o multi-intervalo:
CREATE TYPE timerange AS RANGE (
subtype = time,
subtype_diff = time_diff,
multirange_type_name = timemultirange
);
Agora o horário de trabalho pode ser formado pela seguinte expressão:
SELECT timemultirange(
timerange('09:00', '13:00', '[)'),
timerange('14:00', '18:00', '[)')
) AS working_hours;
working_hours ------------------------------------------- {[09:00:00,13:00:00),[14:00:00,18:00:00)}
Descrição de depesz.
Funções Ltrim e rtrim para strings binárias
commit: a6cf3df4 Você
também pode cortar bytes no início e no final de uma string de bytea ao mesmo tempo usando a função btrim. Agora você pode aparar cada aresta separadamente com as novas funções ltrim e rtrim para strings binárias.
GRANTED BY frase nos comandos GRANT e REVOKE
o commit: 6aaaa76b
Para compatibilidade com o padrão SQL nos comandos GRANT e REVOKE adicionado frase opcional GRANTED BY. Por exemplo:
GRANT ALL ON TABLE table_name TO role_specification
[GRANTED BY role_specification];
REVOKE ALL ON TABLE table_name FROM role_specification
[GRANTED BY role_specification];
O nome da função em GRANTED BY deve corresponder à função atual. Portanto, não funcionará emitir / retirar direitos em nome de outra função. A frase foi adicionada para cumprir o padrão.
Esta é uma continuação do trabalho descrito no artigo do commitfest de setembro.
Administração do Sistema
initdb --no-instruções
commit: e09155bd
O utilitário initdb é usado para inicializar o cluster. E no final de seu trabalho, ele exibe uma instrução de como iniciar um cluster:
Success. You can now start the database server using: pg_ctl -D /usr/local/pgsql/data -l logfile start
Mas isso nem sempre é verdade. Por exemplo, em distribuições de pacotes debian, o utilitário pg_ctlcluster é usado para iniciar o cluster, não pg_ctl. E seus parâmetros são diferentes.
Com a nova opção --no-instruções, o initdb não fornecerá mais conselhos de inicialização, que podem ser aproveitados pelas distribuições de pacotes.
pg_dump: restaura uma única partição como um
commit de tabela independente : 9a4c0e36 , 9eabfe30
Se uma tabela particionada for incluída na cópia lógica do pg_dump, não será possível restaurar uma partição separada dessa cópia como uma tabela independente. Imediatamente após o comando CREATE TABLE vem o comando ALTER TABLE… ATTACH PARTITION, que não só é desnecessário em tal situação, mas também termina com um erro. não restauramos a tabela pai.
CREATE TABLE t (id int) PARTITION BY RANGE(id);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES FROM (1) TO (1001);
CREATE TABLE t_p2 PARTITION OF t FOR VALUES FROM (1001) TO (2001);
\! pg_dump -Fc -f db.dump
\! pg_restore db.dump -t t_p1 -f -
... CREATE TABLE public.t_p1 ( id integer ); ALTER TABLE ONLY public.t ATTACH PARTITION public.t_p1 FOR VALUES FROM (1) TO (1001); ...
Agora os comandos ALTER TABLE… ATTACH PARTITION para todas as partições são descarregados separadamente e depois de todos os comandos para criar partições CREATE TABLE. Portanto, ao restaurar uma única partição especificada com a opção -t, apenas o comando CREATE TABLE será executado, o que permite restaurar a partição como uma tabela independente.
É tudo por agora. Estamos aguardando o commitfest final em 14 de março .