Erwin Brandstetter's questions

De acordo com o SQL padrão UNION/ UNION ALLnão garante nenhuma ordem de classificação específica sem uma ORDER BYcláusula externa - como quase não há lugar no SQL onde a ordem de classificação é garantida sem ORDER BY.

No entanto, o Postgres usa uma etapa "Append" para casos simples de UNION ALL, portanto, os resultados da primeira perna (mesmo que não classificados entre suas partições) sempre vêm antes da próxima perna, etc. O Postgres simplesmente anexa os resultados de cada perna na ordem dada. Isso é particularmente relevante com uma LIMITcláusula:

SELECT 1 FROM tbl  -- or any complex query
UNION ALL
SELECT 2
LIMIT  1

Obviamente, isso não vale para UNION(sem ALL). Mas fora isso, nunca vi o Postgres retornar fora de ordem, ou seja, '2' da consulta acima, enquanto o primeiro SELECTtambém retornaria linha(s). Nem mesmo se a primeira etapa for extremamente cara.

Eu baseei consultas sobre esse comportamento no passado. Agora , encontrei uma alegação de que o Postgres pode retornar linhas fora de ordem aqui, mas sem comprovação real.

O atual manual do Postgres tem a dizer sobre o assunto:

UNIONefetivamente acrescenta o resultado de query2ao resultado de query1(embora não haja garantia de que esta seja a ordem em que as linhas são realmente retornadas). Além disso, elimina linhas duplicadas de seu resultado, da mesma forma que DISTINCT, a menos que UNION ALLseja usado.

Isso é bastante obscuro. A ordem citada se aplica à lista de SELECTcláusulas, ou linhas dentro de cada cláusula, ou apenas ao conjunto retornado? Além disso, UNION ALLé mencionado apenas na segunda frase, então não está claro se a primeira frase tão importante deve se aplicar a UNION ALL...

Alguém pode mostrar um exemplo, onde as linhas são retornadas fora de ordem, quebrando a sequência de UNION ALLcláusulas? Em qualquer versão do Postgres. (Mesmo que a versão mais recente seja a mais interessante.)

Se não for isso, há motivos para acreditar que isso pode mudar em uma versão futura?

ORDER BYnão é a questão imediata aqui. A questão é se várias UNION ALLcláusulas retornam linhas na sequência determinada (antes LIMITpodem ser acionadas e impedir que outras ramificações sejam executadas).

psql13.2 executa o buffer de consulta não importa o que aconteça ao sair do editor, mesmo depois de sair - nesse caso, o buffer pode conter a consulta SQL executada mais recentemente agora.

Reproduzir:

1. Inicie o editor ( vimno meu caso) com \eou \e filenamea partir do psqlconsole.
2. Digite algo ou não, não faz diferença.
3. Cancele a edição com :q(ou :q!para forçar)

Comportamento esperado: volte ao estado antes de iniciar o editor. (Cancelei a edição!)
Comportamento observado: execute novamente a última consulta SQL executada.

O que parece acontecer com \e:

1. ele executa (o equivalente a) \p, portanto, o buffer de consulta atual contém o comando anterior quando estava vazio. (O que não é visível para o usuário ao editar um arquivo!) O manual sobre \p :

   \pou\print

   Imprima o buffer de consulta atual na saída padrão. Se o buffer de consulta atual estiver vazio, a consulta executada mais recentemente será impressa.

2. Inicie o editor. Se um nome de arquivo foi fornecido, carregue o arquivo, caso contrário, carregue o buffer de consulta.

3. Se o editor terminar com uma gravação, copie o resultado para o buffer de consulta.
   Se a edição for cancelada ( :qno vim), não sobrescreva o buffer de consulta. (Ele mantém o comando anterior agora!)

4. Execute o buffer de consulta. Ou, para ser mais preciso, faça assim:

   • Como evitar a execução da consulta após sair do editor \e no psql?

O comportamento parece bom para \p(basta copiar o buffer de consulta, sem danos), mas não para \e, que pode executar um comando que eu nem consegui ver.

É um bug?
De qualquer forma, gostaria de pelo menos desabilitar a execução automática ao sair do editor. Mas isso não parece ser possível. Ou eu estou esquecendo de alguma coisa?

O manual do Postgres 12 diz:

As operações a seguir são sempre restritas em paralelo.

• Varreduras de expressões de tabela comuns (CTEs).
• Varreduras de tabelas temporárias.
• ...

Mais abaixo na mesma página de manual:

[...] Da mesma forma, as funções devem ser marcadas PARALLEL RESTRICTEDse acessarem tabelas temporárias, estado de conexão do cliente, cursores, instruções preparadas ou estado local de back-end diverso que o sistema não pode sincronizar entre os trabalhadores. Por exemplo, setseede randomsão paralelas restritas por este último motivo.

Nenhuma menção a CTEs. Agora não tenho certeza se posso usar PARALLEL SAFEpara funções que contêm um CTE. Faria sentido para mim que esses fossem apenas PARALLEL RESTRICTED.

Contexto: Eu tenho que determinar o melhor rótulo para funções definidas pelo usuário existentes. A configuração é nova desde o Postgres 9.6, e pode ter um grande impacto no desempenho, pois as operações que envolvem funções que não são não PARALLEL SAFEserão executadas por trabalhadores paralelos, PARALLEL RESTRICTEDapenas pelo líder. (E PARALLEL USAFEdesativa o paralelismo completamente.)

Eu postei uma pergunta relacionada em pgsql-general .

Afirmação 1

O operador de concatenação ||pode concatenar qualquer valor do tipo string, retornando text. No Postgres, cada tipo tem uma representação de texto e pode ser convertido em text. Consequentemente, citando o manual :

No entanto, o operador de concatenação de strings ( ||) ainda aceita entradas que não sejam strings, desde que pelo menos uma entrada seja do tipo string

Relacionado:

• Como concatenar colunas em um SELECT do Postgres?

Afirmação 2

Concatenar um ou mais NULLvalores torna o resultado NULL.

test=# SELECT (text 'foo' || NULL) IS NULL
test-#      , (text 'bar' || char '1' || NULL ) IS NULL
test-#      , (NULL::bigint || text 'baz') IS NULL;
 ?column? | ?column? | ?column? 
----------+----------+----------
 t        | t        | t

Pergunta

É possível concatenar a texte um NULLvalor e obter um resultado não nulo?

Em outras palavras, como isso é possível?

test=# SELECT col IS NULL AS col_is_null
test-#     , (text 'foo' || col) IS NULL AS result_is_null
test-# FROM   tbl;
 col_is_null | result_is_null 
-------------+----------------
 t           | f

Aplica-se a qualquer versão do Postgres.
Um cliente meu tropeçou nisso, confiando no resultado como NULL, e achei intrigante o suficiente para compartilhar.
É uma pergunta um pouco complicada, pois eu sei a resposta.

_{Nota: CASE ou COALESCEcapturar NULLvalores normalmente é um bom estilo, mas não é disso que trata esta pergunta. Trata-se de concatenação com um NULLvalor real, com o operador de concatenação ||e ainda obtendo um resultado não nulo.}

Trabalhar com o Amazon Aurora Postgres versão 2.0 , que é basicamente o PostgreSQL 10.4 com uma camada de armazenamento modificada.

Tentando restaurar um dump que contém várias linhas como:

ALTER FUNCTION myschema.f_myfunc(anyarray, anyelement) OWNER TO myrole;

Eu recebo uma mensagem de erro para cada ALTER FUNCTIONassim:

ERROR:  improper qualified name (too many dotted names)

Tentar o mesmo no psql produz o mesmo erro. Mesmo com a forma mais simples:

ALTER FUNCTION foo() OWNER TO myrole;

Não há nomes pontilhados. Encontrei reclamações semelhantes aqui: https://forums.aws.amazon.com/thread.jspa?messageID=872096&tstart=0

Deve ser um bug no Aurora - ou estou perdendo alguma coisa?

Temos visto repetidamente tentativas fracassadas de indexar colunas com valores que excedem um tamanho máximo. O Postgres 10 tem esse tipo de mensagem de erro:

ERROR:  index row size xxxx exceeds maximum 2712 for index "foo_idx"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
       Consider a function index of an MD5 hash of the value, or use full text indexing.

Exemplos:

• Sobrecarga de índice variável de caracteres e limite de comprimento
• Erro de tamanho máximo da linha do índice

etc.

Agora, a_horse_with_no_name demonstrou um caso com valores muito maiores text(10.000 caracteres) que ainda parece funcionar com um UNIQUEíndice no Postgres 9.6. Citando seu caso de teste:

create table tbl (col text);
create unique index on tbl (col);

insert into tbl
values (rpad(md5(random()::text), 10000, md5(random()::text)));

select length(val) from x;  -- 10000

Nenhum erro e o valor da coluna realmente testado com um comprimento de 10.000 caracteres.

Houve mudanças recentes ou como isso é possível?

As funções do Postgres são declaradas com classificação de volatilidade VOLATILE, STABLEouIMMUTABLE . O projeto é conhecido por ser muito rigoroso com esses rótulos para funções internas. E com razão. Exemplo proeminente: os índices de expressão permitem apenas IMMUTABLEfunções e essas devem ser verdadeiramente imutáveis ​​para evitar resultados incorretos.

As funções definidas pelo usuário ainda podem ser declaradas conforme a escolha do proprietário. O manual aconselha:

Para obter melhores resultados de otimização, você deve rotular suas funções com a categoria de volatilidade mais estrita que seja válida para elas.

... e adiciona uma extensa lista de coisas que podem dar errado com um rótulo de volatilidade incorreto.

Ainda assim, há casos em que fingir imutabilidade faz sentido. Principalmente quando você sabe que a função é, de fato, imutável dentro do seu escopo. Exemplo:

• O PostgreSQL suporta agrupamentos “insensíveis ao acento”?

Deixando de lado todas as possíveis implicações na integridade dos dados , qual é o efeito no desempenho? Pode-se supor que declarar uma função IMMUTABLEsó pode ser benéfico para o desempenho . É assim mesmo?

Declarar a volatilidade da função pode IMMUTABLE prejudicar o desempenho?

Vamos assumir o Postgres 10 atual para reduzi-lo, mas todas as versões recentes são interessantes.

Este é um desdobramento dos comentários da pergunta anterior:

• A consulta do Postgres 9.4.4 leva uma eternidade

Usando o PostgreSQL 9.4, sempre parece haver uma Recheck Cond:linha após as varreduras de índice de bitmap nos planos de consulta gerados por arquivos EXPLAIN.

Como na EXPLAINsaída da pergunta referenciada:

->  Bitmap Heap Scan on table_three  (cost=2446.92..19686.74 rows=8159 width=7)
      Recheck Cond: (("timestamp" > (now() - '30 days'::interval)) AND (client_id > 0))
      ->  BitmapAnd  (cost=2446.92..2446.92 rows=8159 width=0)
            ->  Bitmap Index Scan on table_one_timestamp_idx  (cost=0.00..1040.00 rows=79941 width=0)
                  Index Cond: ("timestamp" > (now() - '30 days'::interval))
            ->  Bitmap Index Scan on fki_table_three_client_id  (cost=0.00..1406.05 rows=107978 width=0)
                  Index Cond: (client_id > 0)

Ou na saída de EXPLAIN ANALYZEuma tabela enorme e simples (com muito pouco work_mem):

EXPLAIN ANALYZE SELECT * FROM aa WHERE a BETWEEN 100000 AND 200000;

Bitmap Heap Scan on aa  (cost=107.68..4818.05 rows=5000 width=4) (actual time=27.629..213.606 rows=100001 loops=1)
  Recheck Cond: ((a >= 100000) AND (a <= 200000))
  Rows Removed by Index Recheck: 758222
  Heap Blocks: exact=693 lossy=3732
  ->  Bitmap Index Scan on aai  (cost=0.00..106.43 rows=5000 width=0) (actual time=27.265..27.265 rows=100001 loops=1)
        Index Cond: ((a >= 100000) AND (a <= 200000))

Isso significa que as condições de índice devem ser verificadas uma segunda vez após uma varredura de índice de bitmap?
O que mais podemos aprender com a EXPLAINsaída?

Dada esta configuração no Postgres 9.4 atual ( desta questão relacionada ):

CREATE TABLE foo (ts, foo) AS 
VALUES (1, 'A')  -- int, text
     , (7, 'B');

CREATE TABLE bar (ts, bar) AS
VALUES (3, 'C')
     , (5, 'D')
     , (9, 'E');

db<>fiddle aqui (também da pergunta anterior).

Escrevi a SELECTcom a FULL JOINpara atingir o objetivo da questão referenciada. Simplificado:

SELECT ts, f.foo, b.bar
FROM   foo f
FULL   JOIN bar b USING (ts);

Conforme especificações, a forma correta de endereçar a coluna tsé sem qualificação de tabela. Qualquer um dos valores de entrada ( f.tsou b.ts) pode ser NULL. A USINGcláusula cria um caso estranho: introduzir uma coluna de "entrada" que não está realmente presente na entrada. Até agora tão elegante.

Eu coloquei isso em uma função plpgsql. Por conveniência (ou requisitos), quero os mesmos nomes de coluna para o resultado da função de tabela. Portanto, devemos evitar conflitos de nomenclatura entre nomes de colunas e parâmetros de função idênticos. Deve ser evitado escolhendo nomes diferentes, mas aqui estamos:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT COALESCE(f.ts, b.ts), f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;

Ênfase em negrito para destacar o problema . Não posso usar tssem qualificação de tabela como antes, porque plpgsql geraria uma exceção (não estritamente necessária, mas provavelmente útil na maioria dos casos):

ERROR:  column reference "ts" is ambiguous
LINE 1: SELECT ts, f.foo, b.bar
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

Sei que posso usar nomes diferentes ou uma subconsulta ou usar outra função. Mas eu me pergunto se há uma maneira de referenciar a coluna. Não consigo usar a qualificação de tabela. Alguém poderia pensar que deveria haver uma maneira.
Existe?