dakini's questions

Vi em alguns planos de consulta que o pai é um Finalize GroupAggregate, mas seus nós filhos são Partial HashAggregates. Quando isso faz sentido?

Por exemplo, tenho uma consulta semelhante a algo como:

=# SELECT x, count(*) AS n FROM t GROUP BY x ;

Não há classificação envolvida, então por que ele escolhe um GroupAggregate no topo? E por que os trabalhadores paralelos estão usando HashAggregate?

"Finalize GroupAggregate  (cost=44630.76..47219.48 rows=10218 width=24) (actual time=270.025..309.145 rows=27909 loops=1)"
"  Group Key: x"
"  ->  Gather Merge  (cost=44630.76..47015.12 rows=20436 width=24) (actual time=270.014..293.964 rows=61056 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Sort  (cost=43630.73..43656.28 rows=10218 width=24) (actual time=264.612..270.608 rows=20352 loops=3)"
"              Sort Key: x"
"              Sort Method: external merge  Disk: 728kB"
"              Worker 0:  Sort Method: external merge  Disk: 720kB"
"              Worker 1:  Sort Method: external merge  Disk: 776kB"
"              ->  Partial HashAggregate  (cost=39474.60..42950.27 rows=10218 width=24) (actual time=198.285..223.757 rows=20352 loops=3)"
"                    Group Key: x"
"                    Batches: 5  Memory Usage: 1073kB  Disk Usage: 2312kB"
"                    Worker 0:  Batches: 5  Memory Usage: 1073kB  Disk Usage: 1760kB"
"                    Worker 1:  Batches: 5  Memory Usage: 1073kB  Disk Usage: 3400kB"
"                    ->  Parallel Seq Scan on t (cost=0.00..17344.46 rows=345446 width=16) (actual time=0.053..52.217 rows=276357 loops=3)"

Eu também vi algo semelhante nesta pergunta . Embora, neste caso, eu não conheça a consulta original.

Estou executando uma consulta como

explain (analyze, buffers) select col1, col2, count(col3) as c from table1 group by 2, 1 order by 2, 1

Quando work_memestá definido como 4 MB, o plano fica assim:

"GroupAggregate  (cost=0.70..211944.54 rows=573788 width=26) (actual time=5.146..2601.133 rows=1867574 loops=1)"
"  Group Key: col2, col1"
"  Buffers: shared hit=1844356 read=9682"
"  ->  Incremental Sort  (cost=0.70..191999.45 rows=1894295 width=21) (actual time=5.131..1848.190 rows=1894295 loops=1)"
"        Sort Key: col2, col1"
"        Presorted Key: col2"
"        Full-sort Groups: 58831  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB"
"        Buffers: shared hit=1844356 read=9682"
"        ->  Index Scan using table1_pkey on table1  (cost=0.43..121686.41 rows=1894295 width=21) (actual time=5.071..923.512 rows=1894295 loops=1)"
"              Buffers: shared hit=1844356 read=9682"
"Planning:"
"  Buffers: shared hit=2"
"Planning Time: 0.127 ms"
"JIT:"
"  Functions: 7"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.614 ms, Inlining 0.000 ms, Optimization 0.346 ms, Emission 4.648 ms, Total 5.609 ms"
"Execution Time: 2725.164 ms"

Quando eu aumento o work_mem para 1GB, de repente fica muito diferente

"Sort  (cost=107700.32..109134.79 rows=573788 width=26) (actual time=6461.310..6821.930 rows=1867574 loops=1)"
"  Sort Key: col2, col1"
"  Sort Method: quicksort  Memory: 195057kB"
"  Buffers: shared hit=13813 read=116"
"  ->  HashAggregate  (cost=47079.16..52817.04 rows=573788 width=26) (actual time=1194.218..1777.794 rows=1867574 loops=1)"
"        Group Key: col2, col1"
"        Batches: 1  Memory Usage: 303121kB"
"        Buffers: shared hit=13813 read=116"
"        ->  Seq Scan on table1  (cost=0.00..32871.95 rows=1894295 width=21) (actual time=0.016..214.794 rows=1894295 loops=1)"
"              Buffers: shared hit=13813 read=116"
"Planning:"
"  Buffers: shared read=2"
"Planning Time: 0.122 ms"
"JIT:"
"  Functions: 7"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.477 ms, Inlining 0.000 ms, Optimization 0.216 ms, Emission 4.722 ms, Total 5.416 ms"
"Execution Time: 6967.294 ms"

Observações confusas -

1. Ele mudou para uma varredura sequencial em vez de uma varredura de índice, onde há mais memória
2. Ele abandonou a classificação incremental eficiente (seguida por GroupAggregate) e fez um HashAggregate seguido por classificação rápida
3. O custo do novo plano com 1 GB de memória se mostra menor, mas o tempo de execução é bem maior.

O que está acontecendo?

Estou tentando usar um CTE para inserir alguns dados aleatórios em uma tabela -

create table foo (id integer)

with x as (select random())
  insert into foo (id)
  select x from x

Isso dá um erro: ERROR: column "id" is of type integer but expression is of type record

Apenas o CTE com selectfunciona:

with x as (select random())
--   insert into foo (id)
  select x from x

Também não consigo digitar:

with x as (select random())
  insert into foo (id)
  select x::integer from x

Isso dá um erro: ERROR: cannot cast type record to integer.

O que está errado e como posso corrigir isso?

Essa é uma pergunta meio subjetiva.

Se eu tiver um CTE recursivo, algo como:

WITH RECURSIVE r(x, y) AS ( ...
    SELECT x, y -- select1
    FROM t WHERE .. 
    UNION
    SELECT t.x, t.y -- select2
    FROM r JOIN t
    ON ...
)
SELECT x, y
FROM r

Qual é exatamente o objetivo de especificar a lista de colunas como um argumento na definição de CTE? Se eu não especificar, ele pega a lista de colunas no arquivo SELECT.

Se eu especificar, toda vez que quiser fazer alterações, tenho que refletir isso nos SELECTs do CTE e também na lista de argumentos.

Que benefício eu ganho escrevendo WITH RECURSIVE r(x, y) ASem vez de apenas WITH RECURSIVE r AS?

Ciclos em gráficos levam a loops infinitos em CTEs.

Lidar com eles no Postgres é simples.

Desde 8.0, o MySQL também permite CTEs. Como posso detectar ciclos e loops infinitos em MySQL CTEs?

O objetivo não é interromper a consulta após 1000 ou qualquer número de iterações, mas realmente tratá-la no código (por exemplo, coletando em um array a lista de nós visitados e tendo uma condição de desigualdade para evitar loops).

Ou existem opções "incorporadas" para lidar com isso, como cycle COLNAMEnos CTEs do Postgres?

Meu código atual se parece com isso

with recursive circle as (                                                               
select friend2, name2, 0 as depth from my_view1 where friend1 = 1
union
select m.friend2, m.name2, c.depth+1 from my_view1 m
inner join circle c on c.friend2 = m.friend1)
select * from circle where circle.depth < 2;

As tabelas subjacentes podem ser criadas com:

create table people (person_id integer primary key, name varchar(20) not null);

insert into people (person_id, name) values (1, 'tom'), (2, 'dick'), (3, 'harry'), (4, 'susan'), (5, 'mary'), (6, 'jill');

create table friends (friend1 integer references people (person_id), friend2 integer references people (person_id), primary key (friend1, friend2));

insert into friends (friend1, friend2) values (1,2), (2, 3), (3, 4), (5, 6);

insert into friends (friend1, friend2) values (2,1), (3,2), (4,3), (6,5);

create view my_view1 as select f.friend1, p.name as name1, f.friend2, p1.name as name2 from friends f join people p on p.person_id = f.friend1 join people p1 on p1.person_id = f.friend2 ;

Para um banco de dados de teste, gostaria de criar vários usuários de teste (todos com valores "padrão"). Há uma tabela chamada users, e outra chamada user_properties. Para cada usuário de teste, preciso fazer entradas correspondentes em ambas as tabelas. Eu já tenho um CTE funcionando bem para fazer isso, mas não consigo envolver esse CTE em um loop.

CREATE OR REPLACE FUNCTION ins() 
returns void as 
$BODY$
BEGIN
--RETURN record;

  FOR Loopid  IN 0..10 LOOP

with 
    user as (
    insert into user
    values(default) 
    returning id
    )
    ,user_property as (
    insert into user_property (property_of) 
    select id from user 
    returning id
    )
select id from user_property;

END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Ele cria a função, mas ao executar reclama

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

Mas não parece possível substituir o último selectCTE por um perform- isso é um erro de sintaxe. Então, como posso fazer isso?

As estruturas da tabela podem ser consideradas muito simples, pois a maioria das colunas terá os valores padrão. A tabela usertem uma única coluna ide user_propertiestem duas colunas ide property_of- que é uma chave estrangeira para col idna usertabela. Ambas as idcolunas são sequências geradas automaticamente.

As poucas perguntas ( exemplo ) que têm títulos muito semelhantes têm a resposta de que um loop/cte não é realmente necessário, então elas não me ajudam.

O contexto está se conectando a um banco de dados Postgres de um servidor de descanso.

Para considerar um exemplo hipotético representativo: gostaria de obter uma lista de nomes em que a data de criação da conta é mais antiga/mais recente que um valor arbitrário.

Na consulta de exemplo abaixo, a estrutura da tabela é simples - nameé do tipo texte creation_dateé do tipo timestamp. Então, quando eu faço algo como

server_pg_module:query("select name from new_table where 
current_timestamp - creation_date < '6 days'") 

funciona bem. Mas o que eu realmente quero fazer é obter esse valor do 6 daysservidor. Então eu tento algo como

server_pg_module:query("select name from new_table where
current_timestamp - timestamp < $1", ["6 days"]

ele lança um erro. Eu tentei '6 days', "'6 days'"e algumas outras misturas, todos os erros de lançamento. Então, para verificar, adicionei uma nova coluna intervaldo tipo intervale tentei uma consulta como

server_pg_module:query("insert into new_table (name, interval) values ($1, '3 day')", ["fooo"]). 

que funciona, mas

server_pg_module:query("insert into new_table (name, interval) values ($1, $2)", ["fooo", "3 days"]). 

rompe. Para uma boa medida, além das misturas "'3 days'"mencionadas acima, eu também tentei $2::interval(o que não tenho certeza se é legítimo), mas não funciona.

Assim, acredito que possa ter algo a ver com a expressão de um intervalo em uma consulta de parâmetro ou algo peculiar sobre o módulo que estou usando. Qualquer idéia sobre o que causa o problema e como fazer esse tipo de coisa seria apreciada. Ou pode ser que o problema não seja com pg, mas com o módulo, então eu tenho que resolvê-lo em outro lugar.

Versão do Postgres: 10.x

O módulo que estou usando é pgo (para a linguagem de programação Erlang) https://github.com/SpaceTime-IoT/pgo . A mensagem de erro que recebo (quando passo "2 days"ou "'2 days'"como parâmetro de consulta) se parece com:

{error,{pgsql_error,#{code => <<"08P01">>,file => <<"pqformat.c">>,
                          line => <<"575">>,
                          message => <<"insufficient data left in message">>,
                          routine => <<"pq_copymsgbytes">>,severity => <<"ERROR">>,
                          {unknown,86} => <<"ERROR">>}}}

E quando passo '2 days'como parâmetro, dá um badargerro.

Estou tentando descobrir se há uma maneira de fazer CTEs aninhados funcionarem para esse caso específico.

Considere o seguinte cenário (altamente artificial) baseado no aplicativo real: há uma tabela de coluna única de IDs de funcionários. Depois, há uma tabela de propriedades do funcionário com todos os detalhes. (A principal razão por trás da tabela de col única é geralmente a necessidade de novos IDs de funcionários serem criados em lotes e atribuídos antes que quaisquer detalhes da equipe real sejam conhecidos.)

Agora para a tarefa em mãos, estamos inserindo detalhes (ou seja, o nome) para um novo funcionário, mas primeiro precisamos verificar se um funcionário com esse nome já existe. Se isso acontecer, simplesmente retornaremos o id e, caso contrário, criaremos um novo registro de funcionário e inseriremos os detalhes, retornando finalmente o id recém-criado.

Para recriar este cenário de teste:

CREATE TABLE public.employee (
    id text DEFAULT gen_random_uuid(),
    PRIMARY KEY (id)
);

CREATE TABLE public.employee_details (
    employee_id text,
    name text,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (employee_id) REFERENCES public.employee(id)
);

A consulta que estou tentando moldar se parece com a mostrada abaixo.

with 
e as 
    (select name, employee_id from employee_details where name = 'jack bauer'), 

i as (insert into employee_details (name, employee_id) 
    select 'jack bauer', 
        (with a as (insert into employee values(default) RETURNING id) select a.id from a)
    where not exists (select 1 from e) returning name, employee_id) 

select employee_id, name from e
union all 
select employee_id, name from i; 

Se eu substituir o CTE aninhado por um id já criado (executando o CTE aninhado separadamente), ele funcionará (mas pode resultar na criação de um id supérfluo). Também é possível simplesmente mover o CTE aninhado para o nível superior (para que tudo se pareça com with e as (..), i as (..), a as (..) select .. where not exists..., mas isso também significaria que um ID de funcionário supérfluo é criado onde nenhum detalhe precisa ser inserido. Eu quero descobrir uma maneira para fazê-lo "inline" - então o novo id só será criado se a not existscláusula retornar true.

Eu continuo recebendo o erro:

A cláusula WITH que contém uma instrução de modificação de dados deve estar no nível superior.

Eu acho que o problema é que o CTE aninhado retorna uma "coluna", enquanto toda a consulta funcionará se obtiver um "valor" (o que acontece quando alguém simplesmente copia um valor de texto em vez do CTE). Eu me deparei com uma discussão um pouco relacionada a esta questão, mencionando um bug aparente que foi corrigido desde 9.3. Não sei se isso está relacionado aos meus problemas aqui. Para citar a discussão vinculada:

O código de análise parse parece pensar que WITH só pode ser anexado ao nível superior ou a um SELECT de nível folha dentro de uma árvore de operação definida; mas a gramática segue o padrão SQL que não diz tal coisa

Estou usando o Postgres 10.3.

Estou tentando ter uma maneira reproduzível de preencher um banco de dados com alguns dados de teste.

Suponha (um exemplo muito simplificado com) três tabelas básicas: nome, cidade, trabalho e duas tabelas de relacionamento: nome-cidade e nome-trabalho. Eu preciso criar uma entrada em cada uma das três tabelas base e usar as entradas acima mencionadas para criar entradas nas DUAS tabelas de relacionamento.

O que eu já tenho é uma forma de usar uma série de withconsultas para criar entradas nas 3 tabelas base e inserir valores em UMA tabela de relacionamento.

with x as 
(INSERT INTO "public"."name" VALUES(DEFAULT) RETURNING "id"),
y as 
(INSERT INTO "public"."job" VALUES(DEFAULT) RETURNING "id"),
z as 
(INSERT INTO "public"."city" VALUES(DEFAULT) RETURNING "id")

INSERT INTO "public"."name-job"("name", "job")  
select x.id, y.id from x,y;

Eu realmente gostaria de adicionar uma segunda instrução de inserção

INSERT INTO "public"."name-city"("name", "city")  
select x.id, z.id from x,z;

após a primeira inserção, mas não sei como. Eu tentei separar as duas instruções de inserção com vírgulas e colocá-las entre parênteses e depois separá-las com vírgulas e algumas outras maneiras, mas nada funciona.

Não é inteiramente uma opção usar uma nova instrução com a segunda inserção porque preciso que os mesmos valores de x, y, z sejam usados. Dada a minha falta de conhecimento/experiência, é perfeitamente possível que eu esteja perdendo algo óbvio... então quaisquer idéias sobre como eu posso fazer isso, idealmente, sem ferramentas altamente sofisticadas, seriam muito bem-vindas.

FWIW, estou usando o Postgres (10.x)

Estou tentando decidir sobre o design do banco de dados, com o mínimo possível de suposições (sobre como o aplicativo da Web realmente evolui) neste estágio.

Como primeiro passo, entendendo que JOINS são caros, estou considerando um pequeno número de tabelas monolíticas em oposição a um grande número de tabelas menores normalizadas. Como segundo ponto, estou confuso entre usar hstore vs. tabelas regulares vs. JSONB (com indexação GiST).

AFAIK (sinta-se à vontade para corrigir):

1. Geralmente, no Postgres, hstore é conhecido por ter um desempenho melhor do que outros tipos de dados. Esta apresentação do FOSDEM PGDAY tem algumas estatísticas interessantes (na segunda metade dos slides). https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fosdem-2013.pdf

2. Uma vantagem com hstore é a indexação rápida (GiN ou GiST). No entanto, com indexação JSONB, GiN e GiST também pode ser aplicada a dados JSON.

3. Este blog de um profissional do 2º Quadrante diz "Neste ponto provavelmente vale a pena substituir o uso do hstore pelo jsonb em todos os novos aplicativos" (vá até o final): http://blog.2ndquadrant.com/postgresql-anti-patterns-unnecessary -jsonhstore-dynamic-columns/

Então, gostaria de decidir o seguinte:

1. Para a parte principal (estruturada) dos dados: deve ir em algumas tabelas relacionais (relativamente grandes com muitas colunas) ou deve ser um número de armazenamentos de valor-chave usando hstore?
2. Para os dados ad hoc (contribuídos pelo usuário/não estruturados), eles devem estar em armazenamentos de valor de chave JSON ou ad hoc em hstore (com as chaves armazenadas em uma das principais tabelas relacionais)?

Se eu usar meu banco de dados para dados relacionais e optar por armazenar grandes blobs binários no sistema de arquivos, como posso integrar os dois? Como funciona esse processo?

Eu sei que o banco de dados precisará armazenar um caminho/link de diretório para o blob correspondente, mas alguém pode me esclarecer sobre os detalhes? Por exemplo, acho que devo ter alguns scripts de shell para primeiro executar uma verificação de vírus no binário em um local /tmp seguro e, em seguida, movê-lo para o local de armazenamento real e atualizar o caminho no banco de dados. Mas para fazer isso a partir do sistema operacional, será necessário um script para verificar periodicamente se houve uma alteração em um diretório e, em seguida, disparar ações. É possível executar um script de shell de dentro de um banco de dados (sei que o inverso é possível)?

Ou existem ferramentas melhores para fazer esse tipo de coisa (integrar ao armazenamento do sistema de arquivos) no MySQL/Postgres?