Como listar todas as Foreignkeys (tabela + coluna + pk-of-that-row) que apontam para uma determinada linha em uma determinada tabela.
Exemplo: a tabela "usuário" possui linha com PK '1234'.
Eu quero ver todos os FKs que apontam exatamente para este usuário com ID '1234'.
Eu uso o PostgreSQL 12.8.
Nossos testes do Django estão ficando lentos e muito tempo é gasto em cursor.execue().
Existem aproximadamente duas maneiras agora: reescrever os testes para simular o ORM ou tornar o PostgreSQL mais rápido.
Esta pergunta é sobre "tornar o PostgreSQL mais rápido".
O que posso fazer para melhorar a velocidade do PostgreSQL se D (Durabilidade) do ACID não for importante. Tudo poderia ser mantido na RAM.
Restrição: Esta questão é sobre o que você pode fazer sem modificar o código Django/Python/SQL.
Imagine que você tem um serviço de refeições e todos os dias há uma refeição diferente que os clientes podem pedir.
Mon, Dec 12: Pasta
Tue, Dec 13: Pizza
Wed, Dec 14: Gnocci
As encomendas podem ser feitas até às 9h.
Isso significa que, segunda-feira, às 8h, os clientes podem pedir massas para hoje, às 9h15, eles não podem mais fazer pedidos para hoje.
Como lidar com isso é com o PostgreSQL.
Eu vejo duas maneiras:
Existe uma terceira ou quarta via viável?
Qual caminho é simples e robusto?
Gostaria de bloquear uma tabela: INSERTdeveria ser permitido, mas UPDATEdeveria ser desabilitado.
Estou usando uma versão atual do PostgreSQL.
Eu li Advisory Locks e bloqueio explícito . Talvez eu seja cego, mas não consegui encontrar uma solução adequada para o meu objetivo.
Aqui estão os detalhes do meu caso de uso:
Existe uma tabela que é mais ou menos como registro: vários processos adicionam novas linhas à tabela ao mesmo tempo.
Uma vez em uma hora um script processa os dados que foram adicionados.
Agora eu quero ter certeza de que este script tem um conjunto de trabalho fixo. Nenhuma modificação deve ser feita nas linhas nas quais o script está trabalhando. Mas tudo bem se novas linhas forem adicionadas enquanto os scripts são executados. Essas linhas obtêm processos com a próxima chamada do script.
Sei que existem vários símbolos para expressar a cardinalidade entre as entidades.
Mas existe uma maneira pragmática comum de expressar cardinalidade em ascii?
Exemplo: Usuário -- Grupo. Um usuário pode ter vários grupos e um grupo pode ter vários usuários.
Eu olhei para esta página: https://en.wikipedia.org/wiki/Cardinality_(data_modeling)
Por exemplo: "pedido" de um para muitos ←→ "item de linha"
Aqui estão algumas maneiras gráficas de expressar a cardinalidade:
https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model#Cardinalities
Qual é a maneira mais comum de expressar cardeais?
Existe um padrão/rfc para isso?
Eu quero criar uma tabela que tenha uma coluna chamada "direção".
Esta coluna é "in" ou "out". Não é anulável e só pode conter esses dois valores. Nunca outros valores.
Qual tipo de dado é mais viável?
Eu uso no PostgreSQL. Está 100% ok se o tipo de dados for específico de PG (conhecido apenas por esta implementação de banco de dados).
Se você olhar apenas para o banco de dados, tudo está bem. Você tem transações e, se algo der errado, tudo é revertido. Isso é bom - eu gosto disso.
MAS: Eu quero enviar e-mails. Agora estou com problemas porque não consigo reverter.
exemplo:
Como resolver isso é uma questão diferente, não essa.
Esta questão como chamar isso em geral. Neste exemplo é sobre o envio de e-mails. Mas o mesmo problema assim que você modifica algo em sistemas que estão fora do limite da transação.
Existe um nome para este problema ?
Aproximadamente o mesmo problema surge se você deseja importar arquivos de um diretório. Se você excluir o arquivo dentro da transação, a transação poderá falhar e o arquivo foi excluído, mas nunca importado. Ou você exclui o arquivo após a transação. Em seguida, a exclusão do arquivo pode falhar e o arquivo é importado uma segunda vez.
Não quero reinventar uma solução para isso. É por isso que preciso do termo correspondente para esse problema. Então eu posso ler alguns artigos e aprender o que é "estado da arte" no ano de 2018.
Eu tenho uma tabela my_tablesque é referenciada como chave estrangeira em várias tabelas.
Gostaria de selecionar todas as linhas my_tableque não são referenciadas em outra tabela.
AFAIK, deve ser possível fazer isso de maneira genérica (com alguma mágica de introspecção).
Eu li este artigo sobre o desempenho do PostgreSQL no SSD:
Estas duas configurações parecem ser importantes random_page_costvs.seq_page_cost
Como ambos os parâmetros precisam corresponder ao hardware específico, gostaria de saber se é possível detectar automaticamente os valores correspondentes?
Atualizar
Tenho estes passos em mente:
Eu uso este snippet para detectar índices ausentes:
https://stackoverflow.com/a/12818168/633961
Exemplo:
SELECT
relname AS TableName,
to_char(seq_scan, '999,999,999,999') AS TotalSeqScan,
to_char(idx_scan, '999,999,999,999') AS TotalIndexScan,
to_char(n_live_tup, '999,999,999,999') AS TableRows,
pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'public'
AND 50 * seq_scan > idx_scan -- more then 2%
AND n_live_tup > 10000
AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;
Resultado:
tablename | totalseqscan | totalindexscan | tablerows | tablesize
----------+--------------+----------------+-----------+----------
mytable | 112,479 | 2,978,344 | 1,293,536 | 1716 MB
Estou curioso - gostaria de ver quais instruções SQL realmente fazem uma varredura seq na tabela mytable.
Existe uma maneira de deixar o PostgreSQL emitir um aviso se ele fizer uma varredura sequencial nesta tabela?
Eu tenho uma tabela simples com colunas col1, col2, col3. Todos não anuláveis.
Eu quero deletar todas as linhas onde a tupla (col1, col2) tem várias entradas. Background: uma restrição exclusiva para (col1, col2) deve ser adicionada.
drop table mytable;
create table mytable (
col1 integer not null,
col2 integer not null,
col3 integer not null);
-- rows to delete
insert into mytable values (1, 1, 1);
insert into mytable values (1, 1, 2);
-- rows to keep
insert into mytable values (2, 2, 1);
insert into mytable values (2, 3, 2);
delete from mytable where
(col1, col2) in (
select col1, col2 from mytable
group by (col1, col2) having count(distinct col3) >1) ;
select * from mytable;
Acima funciona no PostgreSQL 10, mas falha em versões mais antigas.
Versões mais antigas me dizem esta mensagem de erro:
ERRO: a coluna "mytable.col1" deve aparecer na cláusula GROUP BY ou ser usada em uma função agregada
Como fazer isso funcionar no PG 9.3?
Eu tenho essa relação N:M:
CREATE TABLE auth_user (
id integer NOT NULL PRIMARY KEY,
username character varying(150) NOT NULL UNIQUE
);
CREATE TABLE auth_group (
id integer NOT NULL PRIMARY KEY,
name character varying(80) NOT NULL UNIQUE
);
CREATE TABLE auth_user_groups (
id integer NOT NULL PRIMARY KEY,
user_id integer REFERENCES auth_user(id) NOT NULL,
group_id integer REFERENCES auth_group(id) NOT NULL,
CONSTRAINT user_groups UNIQUE(user_id, group_id)
);
INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);
Como selecionar todos os nomes de usuário que estão no grupo 'group1'?
Eu uso o PostgreSQL, mas o SQL que funciona em todos os lugares é o preferido.
No meu ambiente, a maioria das consultas do PostgreSQL são geradas a partir de solicitações http.
No meu caso, cada solicitação http tem um cabeçalho exclusivo: X-Request-ID
Se houver um erro ou aviso nos logs do PostgreSQL, gostaria de ver o X-Request-ID relacionado para ver de onde veio a consulta sql.
Como adicionar uma mensagem extra como X-Request-ID para registrar mensagens do PostgreSQL?
Relacionado: https://serverfault.com/a/797611/90324
Eu quero armazenar uma resposta HTTP inteira na linha do banco de dados PostgreSQL.
Eu preciso do status HTTP, os cabeçalhos e o corpo.
AFAIK não há tipo de dados nativo para isso.
Como eu poderia armazenar uma resposta HTTP?
Atualizar
Eu estive pensando sobre isso novamente. Acho que é melhor seguir este padrão: não vou pesquisar nada dentro da resposta http. É como uma bolha. Tudo o que eu quero pesquisar nele enquanto é extraído antes e colocado em uma coluna diferente. Até agora, apenas o código de status http será usado e terá uma coluna própria.
Temos um sistema baseado em PostgreSQL que está instalado em 20 clientes.
Às vezes eu executo uma consulta SQL em todos os sistemas. Até agora eu faço isso com a ferramenta de linha de comando parallelassim:
parallel -q -j0 ssh {} "echo {}; psql -tAc \"select count(*) from auth_user\";echo" ::: $(ssh j list-systems prod)
Estou perdendo a capacidade de classificar a saída. Eu gostaria de evitar scripts de shell sujos e usar a sabedoria do SQL.
Como obter as consultas N SQL no PostgreSQL, para que eu possa executar o SQL comum nele?
(Sub pergunta: Existe uma tag correspondente para esse tipo de pergunta? Quero dizer, perguntas em que o SQL abrange vários bancos de dados?)
Meus passos:
pg_dumpallem um servidorprod-server-oldprod-server-oldpara sempre.prod-server-new)prod-server-newagoraComo posso evitar modificações no banco de dados durante pg_dumpall(entre a etapa 1 e a etapa 2), para que as modificações durante esse período não possam ser perdidas?
No meu caso, é 100% OK ter um pequeno tempo de inatividade.
Existem vários bancos de dados no "cluster" (aliás, eu não gosto da palavra "cluster". Acho que a maioria das pessoas pensa que um "cluster" é um grupo de vários computadores, mas neste caso significa um servidor Postgres, que contém vários bancos de dados).
Minha pergunta foi marcada como possível duplicada para "Tornar o banco de dados Postgres temporariamente somente leitura (para executar instantâneos de volume)". Não acho que seja uma duplicata, pois no meu caso é diferente, pois não peço um estado temporário somente leitura.
Se eu buscar apenas uma linha, a consulta demora muito mais: 1433 ms vs 23 ms
Existe um trabalho em torno?
Lento:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "modwork_ticket" WHERE
"modwork_ticket"."email_sender_id" = '[email protected]'
ORDER BY "modwork_ticket"."date_created" ASC limit 1;
--------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..542.14 rows=1 width=1146) (actual time=1433.128..1433.129 rows=1 loops=1)
Buffers: shared hit=1466151 read=4661
-> Index Scan using modwork_ticket_date_created on modwork_ticket (cost=0.43..606714.36 rows=1120 width=1146) (actual time=1433.125..1433
Filter: ((email_sender_id)::text = '[email protected]'::text)
Rows Removed by Filter: 1705251
Buffers: shared hit=1466151 read=4661
Planning time: 2.504 ms
Execution time: 1433.218 ms
Velozes:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "modwork_ticket" WHERE
"modwork_ticket"."email_sender_id" = '[email protected]'
ORDER BY "modwork_ticket"."date_created" ASC;
--------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=4335.83..4338.63 rows=1120 width=1146) (actual time=23.637..23.794 rows=584 loops=1)
Sort Key: date_created
Sort Method: quicksort Memory: 732kB
Buffers: shared hit=544 read=4
-> Bitmap Heap Scan on modwork_ticket (cost=25.11..4279.11 rows=1120 width=1146) (actual time=20.479..22.595 rows=584 loops=1)
Recheck Cond: ((email_sender_id)::text = '[email protected]'::text)
Heap Blocks: exact=538
Buffers: shared hit=541 read=4
-> Bitmap Index Scan on modwork_ticket_email_sender_id_like (cost=0.00..24.83 rows=1120 width=0) (actual time=20.388..20.388 rows=
Index Cond: ((email_sender_id)::text = '[email protected]'::text)
Buffers: shared hit=3 read=4
Planning time: 0.173 ms
Execution time: 23.974 ms
Versão:
PostgreSQL 9.6.3 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit
Eu corro "VACUUM ANALYZE" antes de executar as consultas acima.
Eu tenho ticket de mesa que tem essas (e outras) colunas:
Se ticket_typeou clientfor atualizado, a tabela transfere_tabledeverá ser arquivada. O ticket.id do ticket alterado deve ser inserido na tabela de transferência.
Aqui está o código de trabalho:
CREATE FUNCTION add_ticket_to_transfere_table () RETURNS trigger
LANGUAGE plpythonu AS $$
plpy.execute(plpy.prepare(
'''INSERT INTO transfere_table (ticket_id) VALUES ($1)''',
['int']), [TD['new']['id']])
$$;
CREATE TRIGGER add_ticket_to_transfere_table_trigger
AFTER UPDATE OF ticket_type_id, client_id ON ticket
FOR EACH ROW
WHEN (OLD.ticket_type_id != NEW.ticket_type_id or
OLD.client_id != NEW.client_id)
EXECUTE PROCEDURE add_ticket_to_transfere_table();
Como eu só usei plpythonu até agora, resolvi assim. Funciona, mas vejo duas coisas para melhorar:
Como realizar essas melhorias?
Existem outras coisas que poderiam ser melhoradas?
Estou usando o PostgreSQL 9.3.13
Eu tenho um simples relacionamento N:M:
Usuário da tabela:
Permissão de Tabela:
Permissão de usuário da tabela:
Eu quero garantir que, se o usuário tiver "is_superuser=True", ele deverá ter todas as permissões disponíveis definidas via N:M Table UserPermission.
Operações típicas:
Como garantir isso com o Postgres 9.6?
Sub-questão (por favor, deixe um comentário): Existe um nome para este padrão?
"Evite redundância" é importante para mim.
Desejo armazenar os dados de entrada para gerenciamento de configuração em um banco de dados relacional.
Meus dados de entrada:
Em 95% de todos os casos, todos os atributos são iguais:
Se eu armazenasse esses dados em YAML, poderia usar variáveis para evitar a repetição.
AFAIK isso não funciona em bancos de dados relacionais.
Eu poderia armazenar isso no banco de dados, mas AFAIK preciso avaliar sozinho:
Como posso evitar redundância se armazenar isso em um banco de dados relacional? Infelizmente esses valores nem sempre são iguais, apenas na maioria dos casos.
Eu uso o PostgreSQL