Imediatamente após atualizar uma visualização materializada no Postgres, gostaria de saber quantas linhas há nela.
Atualmente faço isso executando um segundo comando SQL ( SELECT count(*) FROM...)
Existe uma maneira mais eficiente de fazer isso? É possível persuadir o comando REFRESH a retornar a contagem de linhas?
O arquivo de configuração postgresql.conf contém uma configuração para max_connections.
Tenho algumas perguntas relacionadas...
max_connectionsaumentado?Vi várias postagens sobre como contar o número de conexões atuais...
1) SELECT count(*) from pg_stat_activity;
2) SELECT count(distinct(numbackends)) FROM pg_stat_database;
3) SELECT sum(numbackends) FROM pg_stat_database WHERE datname is not null;
4) SELECT count(*) FROM pg_stat_activity WHERE datname is not null;
5) select sum(numbackends) from pg_stat_database;
Eles geralmente produzem resultados diferentes e levam tempos variados para serem executados.
Qual consulta deve fornecer os resultados mais precisos e como posso saber se preciso aumentar max_connections?
Por que diferentes tabelas postgres não podem usar índices com o mesmo nome?
Estou tentando alterar várias tabelas de tabelas básicas para tabelas particionadas mensais. A solução normal parece ser
CREATE TABLE copy (LIKE original INCLUDING INDEXES) PARTITION BY RANGE xxxxINSERT INTO copy (SELECT * FROM original WHERE ???)DROP TABLE originalALTER TABLE copy RENAME TO originalMeu problema atual é que, embora CREATE TABLE ... LIKE ... INCLUDING INDEXESuse todos os mesmos nomes de campo e tipos de dados, ele criará um novo conjunto de nomes de índice. Por vários motivos, os índices nesses bancos de dados precisam estar em conformidade com uma convenção de nomenclatura fixa.
Existe uma maneira de copiar uma tabela e copiar os nomes dos índices?
Eu tenho um pouco de SQL simples para redefinir alguns valores em uma tabela
UPDATE tableA SET field1 = 0
WHERE field2 IN (SELECT .......)
Isso funciona bem, mas gostaria de modificá-lo para retornar o valor de todos field1os valores antes de serem redefinidos para zero. Isso é o que eu tentei....
UPDATE tableA SET field1 = 0
WHERE field2 IN (SELECT .......)
RETURNING field1
O problema é que isso só retorna os valores de field1 depois de modificado. É possível obter o valor antes ? sem executar uma consulta separada?
Tenho uma mesa que fica vazia a maior parte do tempo. Ocasionalmente, grandes quantidades de dados serão despejadas nele e precisarão ser processadas o mais rápido possível.
Eu tenho um processo que verifica continuamente a tabela e processa todos os dados que encontra.
Qual é a maneira mais rápida/eficiente de testar quaisquer dados na tabela?
A solução óbvia é ....
SELECT count(*) FROM myTable;
Mas isso pode demorar um pouco (especialmente se tiver alguns milhões de linhas). Outra solução é
SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'myTable'
Mas isso dá apenas um valor aproximado.
Existe uma solução melhor/mais rápida. OBSERVAÇÃO - não estou procurando uma contagem exata de linhas, estou procurando um resultado booleano vazio/não vazio simples.
Estou executando o Postgres 14.6 e tenho várias sessões com um wait eventdescrito como Timeout: VacuumDelay. O SQL sendo executado pela sessão é....
autovacuum: VACUUM public.mytable_202110 (to prevent wraparound)
(essas informações são obtidas no painel do pgAdmin4)
eu tenho uma série de perguntas
Não há PIDS de bloqueio listados e todos os bloqueios foram concedidos. O banco de dados está funcionando (embora lentamente)
ATUALIZAÇÃO - isso pode ser relevante, este banco de dados acabou de ser transferido de um servidor antigo e lento para uma máquina mais nova e melhor. Eu esperava ver um desempenho significativamente melhor, mas estou supondo que as estatísticas e os dados de análise nas tabelas NÃO são transferidos com os dados originais, então o novo banco de dados está gastando muitos recursos analisando dados antigos e arquivados.
Tenho um processo que insere registros em uma tabela.
A tabela é definida com um gatilho como este...
CREATE TRIGGER myTrig AFTER INSERT ON myTable FOR EACH ROW EXECUTE PROCEDURE myProcedure();
O processo que faz o INSERTmyTable está fora do meu controle, basicamente apenas despeja registros na tabela ao longo do dia 24 horas por dia, 7 dias por semana.
A funcionalidade myProcedure()requer recursos de banco de dados. Durante os períodos de maior movimento, a taxa de chegada dos registros é maior do que o banco de dados pode suportar (cerca de 500 registros por segundo é o limite). Se eu desabilitar o gatilho usando ALTER TABLE myTable DISABLE TRIGGER myTrig , o sistema pode lidar com talvez 1.000 registros por segundo.
Posso então reativar o gatilho quando o sistema não estiver tão ocupado, mas obviamente os registros adicionados enquanto o gatilho estava desativado não foram processados corretamente.
Existe alguma maneira de executar novamente o gatilho nos registros selecionados JÁ na tabela?
Meu melhor palpite atual é que, durante os períodos de maior movimento, devo desviar os registros recebidos para uma cópia myTableidêntica ao original (mas sem o acionador) e, quando o sistema se acalmar mais tarde, posso inserir da cópia, de volta ao original, onde eles devem ser processados pelo gatilho (embora mais tarde do que deveriam)
Preciso adicionar um novo campo a uma tabela. O campo armazenará apenas um número que varia de 0 a talvez 30, portanto (em teoria) um campo de um único byte deve servir.
O menor tipo de dado inteiro listado na documentação é SMALLINT, que requer 2 bytes.
A tabela à qual desejo adicionar o campo é particionada e atualmente contém aproximadamente 3 bilhões de linhas. Portanto, adicionar um campo de 2 bytes a cada linha deve exigir 6 bilhões de bytes ou 6 GB.
Por motivos que ainda não entendi (consulte esta pergunta ), estou ficando sem espaço ao adicionar um campo SMALLINT a uma tabela grande.
PERGUNTA - existe um tipo de dados de byte único que posso usar para armazenar um número com apenas um intervalo muito pequeno de valores?
Estou tentando adicionar um INTEGER DEFAULT 0campo a uma tabela muito grande (particionada) espalhada por dois tablespaces.
Cada tablespace está em um disco diferente (um na unidade C, o outro na D). Estou tendo o erro a seguir
ERROR: could not extend file "pg_tblspc/31575/PG_10201707211/31576/1155134.27": No space left on device
HINT: Check free disk space
SQL state: 53100
As unidades C e D têm um diretório chamado pg_tblspc/31575/PG_10201707211/31576, mas nenhum deles tem um arquivo chamado 1155134.27 (acho que é um arquivo temporário?)
A unidade C tem 70 Gbytes de espaço livre, a unidade D 350 Gbytes.
Questão 1 - como posso descobrir qual dos dois discos está realmente com pouco espaço?
Questão 2 - como posso calcular quanto espaço livre é realmente necessário? Um número inteiro requer 4 bytes de armazenamento, a tabela consiste em dados particionados mensais, cada mês tem cerca de 60 milhões de linhas e a tabela tem 4 anos de dados. 4 x 60 x 12 x 4 = cerca de 12.000 Mbytes ou 12 GB, portanto, de acordo com minha matemática simples, deve haver espaço suficiente em qualquer um dos discos para todo o espaço extra necessário. Então, por que o postgres exige mais?
(postgres versão 10.4)
A estrutura de arquivo em cada disco é provavelmente a mesma porque cada espaço de tabela foi criado com comandos muito semelhantes...
CREATE TABLESPACE fastDb OWNER dbOwn LOCATION 'c:/pgdata/fdb';
CREATE TABLESPACE slowDb OWNER dbOwn LOCATION 'd:/pgdata/fdb';
Consegui responder à primeira pergunta simplesmente executando novamente a consulta (leva horas) e monitorando o espaço livre do sistema operacional - d: a unidade está ficando sem espaço.
Ainda não consigo descobrir quanto espaço deve ser necessário. Eu liberei 800 Gb na unidade d: e mudei para um campo SMALLINT (2 bytes em vez de 4), mas ainda estou ficando sem espaço.
Existem técnicas/ferramentas/metodologias para investigar corrupção de nível de linha em uma tabela postgres?
A maioria das soluções propostas neste fórum se resume a 'tente fazer um VACUUM FULL para reconstruir a tabela, se isso não funcionar, solte a tabela e restaure do backup'
Existe alguma maneira de realmente corrigir os dados? existe alguma maneira de ler e editar manualmente os arquivos de dados brutos do postgres e remover / reparar apenas as linhas corrompidas?
O sistema em que estou trabalhando lê dados diretamente de um feed externo para uma tabela postgres e, em seguida, mostra evidências de corrupção antes que o processo de backup noturno tenha a chance de salvá-lo. Reexecutar o feed externo não é uma opção, portanto, na prática, se houver alguma corrupção, os dados do dia inteiro serão perdidos.
É possível escrever uma UPDATEconsulta que simplesmente será encerrada se o registro que ela tentar alterar estiver bloqueado por outro processo (em vez de aguardar a liberação do bloqueio)?
Tenho um processo que deveria atualizar os registros em uma tabela, de vez em quando esses registros ficam bloqueados. A atualização desses registros é desejável, mas não essencial. Se os registros estiverem em uso, prefiro que meu processo simplesmente esqueça a atualização e passe para algo mais importante.
Minha abordagem atual é definir o tempo limite do comando para 1 segundo, mas mesmo isso é mais longo do que eu gostaria de esperar - uma atualização normal leva uma fração de milissegundo, portanto, esperar um segundo é uma grande sobrecarga.
Estou executando um script powershell no Windows que se conecta a um banco de dados postgres.
Em condições normais, tudo funciona bem, mas estou tentando tornar o sistema mais robusto e fazer tudo reiniciar automaticamente após uma falha de energia ou reinicialização.
O problema é que, embora tanto o script quanto o banco de dados sejam iniciados com êxito na inicialização, o script faz muito mais rapidamente - na verdade, ele é reiniciado antes que o banco de dados fique disponível.
O script se conecta com uma string de conexão que inclui isso....
Timeout=60;CommandTimeout=8
Meu entendimento (talvez falho?) é que Timeout=60se refere ao tempo limite da conexão e deve significar "tente se conectar ao banco de dados por até 60 segundos e, se nenhuma conexão for feita, encerre com uma mensagem de erro". Porém, na prática a tentativa de conexão falha imediatamente com o erro
the database system is starting
Por que está falhando imediatamente e não espera por 60 segundos? Existe alguma maneira de testar se o banco de dados foi reiniciado antes de tentar se conectar? Ou eu só preciso continuar tentando me conectar até que seja bem-sucedido?
Eu tenho um banco de dados postgres que contém um campo (VARCHAR) que é o caminho completo e o nome do arquivo de um arquivo no mesmo servidor do banco de dados.
Posso listar os arquivos assim.
SELECT myFileName FROM tableA;
Há um problema - alguns dos arquivos realmente não existem (não em seus locais listados de qualquer maneira).
Eu posso usar pg_stat_file() para listar informações sobre qualquer arquivo que exista
SELECT myFileName, pg_stat_file(myFileName) FROM tableA;
No entanto, se um arquivo referenciado em meu banco de dados não existir de fato, a consulta acima cairá com uma mensagem de erro apropriada
ERROR: could not stat file "?????????????????": No such file or directory.
Pergunta - é possível escrever uma consulta SQL postgres para indicar se um arquivo existe ou não existe?
Eu tenho dois bancos de dados idênticos, desenvolvimento e ao vivo.
Eu executo esta consulta em ambos ....
select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device)
Na dev box a consulta demora cerca de 100ms, na live demora cerca de uma hora.
Ambas as tabelas têm um campo de chave primária chamado deviceId, ambos os bancos de dados têm estrutura idêntica e ambos têm dados muito semelhantes (nenhuma das tabelas contém mais de um milhão de linhas). Ambos os bancos de dados são limpos/analisados regularmente, ambos foram feitos nas últimas 12 horas.
Cada banco de dados está em um computador diferente, o hardware é diferente para cada um, mas em 99% do tempo o live box é cerca de 10% mais lento.
Tanto quanto eu posso dizer, ambos os bancos de dados são configurados da mesma forma.
Meu palpite é que há alguma diferença sutil na distribuição dos dados em cada banco de dados que afeta a escolha do plano de explicação.
Minhas perguntas são:
explicar plano dev - leva menos de um segundo:
Aggregate (cost=7806.68..7806.69 rows=1 width=8)
-> Seq Scan on deviceb (cost=4624.86..7702.06 rows=41848 width=0)
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Unique (cost=0.29..4404.59 rows=88108 width=7)
-> Index Only Scan using device_pkey on device (cost=0.29..4184.32 rows=88108 width=7)
explicar o plano ao vivo - leva cerca de uma hora:
Finalize Aggregate (cost=747368287.67..747368287.68 rows=1 width=8)
-> Gather (cost=747368287.46..747368287.67 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=747367287.46..747367287.47 rows=1 width=8)
-> Parallel Seq Scan on deviceb (cost=0.42..747367217.98 rows=27789 width=0)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.42..26001.96 rows=156913 width=7)
-> Unique (cost=0.42..22822.40 rows=156913 width=7)
-> Index Only Scan using device_pkey on device (cost=0.42..21930.12 rows=156913 width=7)
Eu comparei as versões dev/live das tabelas usando pgAdmin4 - simplesmente destacando o objeto e selecionando as propriedades de cada tabela e seus índices e restrições.
Além disso - meus testes (em ambos os bancos de dados) foram feitos sem outros usuários conectados.
DDL, conforme solicitado:
CREATE TABLE public.device (
deviceid character(6) COLLATE pg_catalog."default" NOT NULL,
recentfixtime timestamp without time zone,
newfixes integer DEFAULT 0,
lastfixid bigint DEFAULT 0,
CONSTRAINT device_pkey PRIMARY KEY (deviceid)
USING INDEX TABLESPACE fastspace
);
CREATE TABLE public.deviceb (
deviceid character(6) COLLATE pg_catalog."default" NOT NULL,
flightid integer DEFAULT 0,
lastfirstfixtime timestamp without time zone,
lastprocfixtime timestamp without time zone,
lastprocfly boolean,
priority integer DEFAULT 0,
lastlandtime timestamp without time zone,
CONSTRAINT deviceb_pkey PRIMARY KEY (deviceid)
USING INDEX TABLESPACE fastspace
)
ATUALIZAÇÃO - RESOLVIDO, graças a Jjanes, a versão sugerida é incrivelmente rápida em ambos os bancos de dados.
select count(*) FROM DeviceB where NOT EXISTS (
SELECT 1 from Device where Device.deviceID=DeviceB.deviceID
)
Admito que ainda não tenho certeza de qual é a causa raiz, mas, para ser completo, tentarei responder a algumas das outras perguntas. O Autoanalyse ainda está ativado (pgAdmin/statistics mostra uma última data do Autoanalyse no início desta manhã. ambas as tabelas (ao vivo) têm menos de 200.000 linhas, ambas as tabelas no dev têm menos de 100.000. O hardware nas caixas dev/live é diferente (o maior diferença é que dev tem 32G de memória, live apenas 16G. Possivelmente a diferença mais significativa está nas versões, dev é 13.1, live ainda está em 10.4
A que tipo de dados esta mensagem de erro se refere?
ERROR: numeric field overflow DETAIL: A field with precision 1, scale 0 must round to an absolute value less than 10^1.
Estou recebendo o erro listado acima, está vindo de um gatilho de inserção. Como nenhum dos meus tipos de dados é definido explicitamente como NUMERIC(1,0)e como o valor máximo é 10 à potência de 1 (ou seja, 10), estou supondo que esse erro está realmente se referindo a um BOOLEANcampo. No entanto, não consigo encontrar confirmação disso.
ATUALIZAÇÃO
Resolvido. Meu gatilho está tentando analisar algumas strings em números. O erro listado é retornado quando to_number()não obtém uma string no formato esperado.
Isso retorna 1,4 (correto) -
select to_number('1.4','9D9');
Isso retorna o erro mencionado
select to_number('1d4','9D9');
Ainda não tenho certeza por que está reclamando, NUMERIC(1,0)mas pelo menos sei por que há um erro.
Qual dos dois arquivos de configuração do postgres postgresql.confe pg_hba.conftem prioridade?
pg_hba.confcontrola os métodos de autenticação do cliente, incluindo 'md5' e 'scram-sha-256'
postgresql.confinclui uma entrada para password_encryption que pode ser 'md5' ou 'scram-sha-256' (ou em branco, eu acho)
Então, se esses valores não forem definidos para a mesma coisa, o que tem prioridade?
Estou tentando automatizar a criação de um banco de dados postgres rodando no Windows 10. Gostaria de poder iniciar a partir de uma instalação padrão do postgres e automatizar as seguintes etapas (não necessariamente nesta ordem)
Não é muito difícil reduzir isso a uma série de comandos de banco de dados (desde que sejam executados na ordem correta), o problema é que, para alguns comandos, continuo encontrando o erro 'não pode ser emitido dentro de um bloco de transação'. Isso significa que alguns comandos como DROP DATABASE IF EXISTS e CREATE TABLESPACE só podem ser executados (até onde eu sei) dando um passo único manualmente, enquanto eu posso chamar meus scripts de criação de tabela como um único bloco e recriar um 100 tabelas em uma fração de segundo.
Não estou muito preocupado com os poucos segundos extras que estou desperdiçando executando alguns dos comandos individuais, mas, ao fazer alguns deles manualmente, corro o risco de perder etapas e cometer erros.
Como posso automatizar (ou seja, escrever scripts para) todo o processo?
Atualização, o script se parece um pouco com isso ...
DROP DATABASE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????
ALTER DEFAULT privileges IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM ????;
ALTER DEFAULT privileges IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM ????;
DROP USER IF EXISTS ????;
DROP USER IF EXISTS ????;
DROP USER IF EXISTS ????;
CREATE USER ???? PASSWORD '????';
CREATE USER ???? PASSWORD '????';
CREATE USER ???? PASSWORD '????';
CREATE TABLESPACE fastspace OWNER ???? LOCATION 'c:/????/????';
CREATE TABLESPACE slowspace OWNER ???? LOCATION 'd:/????/????';
DROP DATABASE ????;
CREATE DATABASE ????
WITH OWNER = ????
ENCODING = 'UTF8'
TABLESPACE = fastspace
LC_COLLATE = 'English_United Kingdom.1252'
LC_CTYPE = 'English_United Kingdom.1252'
CONNECTION LIMIT = -1;
GRANT CREATE ON DATABASE ???? TO ????;
COMMENT ON DATABASE ????
IS '????database';
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO ????;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ????;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ????;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ????;
ALTER DEFAULT privileges IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ????;
Muitos (a maioria) dos comandos acima podem ser executados como parte de um bloco de transação, mas alguns não podem (descartar e criar o banco de dados, por exemplo).
Eu tenho uma consulta escrita algo assim .....
with subquery as
(
SELECT A, B, C, D,
row_number() OVER(PARTITION BY X ORDER BY Y) as RN
FROM blah blah
ORDER BY A,B
LIMIT 100
)
SELECT C,D
FROM subquery
WHERE RN=1
Como você pode ver, a subconsulta é classificada, mas a consulta de nível superior não.
A consulta de nível superior não faz nada complicado, apenas seleciona com base na partição por cláusula.
A ordem de classificação da subconsulta será mantida nos resultados finais?
A observação dos vários conjuntos de dados com os quais testei indica que a ordem de classificação É mantida, mas não tenho certeza se isso é apenas uma coincidência de sorte ou não.
Eu tenho várias tabelas postgres com uma coluna do tipo INT que preciso mudar para BIGINT. Cada partição da tabela contém cerca de 100 milhões de linhas, minha intenção é desanexar as partições individuais ALTER o tipo de coluna, e depois anexar novamente.
Minha pergunta é esta, a coluna que precisa de atualização está indexada. É provável que seja mais rápido descartar o índice, alterar o tipo de coluna e recriar o índice? Ou devo deixar o índice no lugar e apenas alterar o tipo de coluna?
observe que os valores da coluna não serão alterados, apenas o tipo de dados (não tenho certeza se isso é relevante ou não!)
obrigado
Se eu fizer um VACUUMem uma tabela Postgres (sem a FULLopção) eu sei que ele deve disponibilizar qualquer espaço livre para o banco de dados, mas não para o sistema operacional.
Deve tornar o espaço livre disponível para qualquer objeto de banco de dados? ou apenas a mesa que foi aspirada?