Existe uma maneira de descobrir a estrutura de uma exibição de maneira semelhante à forma como as tabelas são descritas pelas entradas em information_schema.columns? Eu também posso usar as pg_*tabelas.
Em outras palavras, semelhante a como psqltem \dque descreve as estruturas de ambas as tabelas e visualizações, gostaria de acessar essas informações por meio de consultas SQL.
Há um sistema PostgreSQL PITR rodando em um sistema bastante grande, com centenas de bancos de dados com centenas de tabelas em cada um, e estamos enfrentando problemas baseados em restrições de tempo ao tentar restaurar tabelas individuais de um único banco de dados ou um conjunto de bancos de dados . O PITR está sendo gerenciado com Barman.
Com essa configuração, precisamos restaurar um cluster inteiro (que pode conter dezenas de dbs), extrair a tabela única e restaurar os dados - o que pode levar horas.
Estatisticamente, devido ao número de dbs envolvidos e ao número de pessoas envolvidas, isso acontece com frequência suficiente para se tornar um problema.
Existem soluções mais rápidas que nos dariam recursos semelhantes ao PITR? Eu pensei em fazer pg_dumpbackups manuais para cada tabela individualmente a cada hora, mas talvez existam soluções melhores?
Eu tenho uma tabela que contém essencialmente 3 colunas: ID, FIRST, LAST. São todos inteiros.
Eu estou querendo saber se há uma maneira de usar generate_series() para obter um resultado de consulta onde para cada linha na tabela original, há muitas linhas contendo números entre FIRSTe LAST?
Por exemplo, para uma linha com dados a (42, 5, 8)saída contém linhas (42, 5), (42, 6)e (42, 7).(42, 8)
Existe uma maneira de registrar consultas que usam varreduras sequenciais no PostgreSQL? Ou apenas registre tudo e depois grep para seqscans?
No PostgreSQL, o VACUUM redefinirá a contagem de tuplas mortas (n_dead_tup em pg_stat_all_tables)?
A situação que estou vendo é que o vácuo aparentemente está sendo executado manualmente, uma vez por dia, e os dados n_dead_tup / n_live_tup mostram que há um número grande e crescente de tuplas mortas.
PostgreSQL 9.3 aqui.
Aqui está algo estranho: há uma grande tabela chamada "products" que é particionada com herança de forma que haja dois filhos: products_active e products_inactive, e a restrição é que em products_active, status=1, e products_inactive obtenham todos os outros status.
Há uma grande consulta que une várias tabelas, das quais esta é a primeira parte:
SELECT
products.id, products.status, products.brand_id, products.name, products.description, products.data, products.website,
products.packaging, products.container, products.country_of_origin, products.category_id, products.product_type_id, products.tsv_keywords,
COUNT(prices.id) as prices_count,
ROUND(AVG(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as avg_price,
ROUND(MAX(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as high_price,
ROUND(MIN(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as low_price,
ts_rank(tsv_keywords, plainto_tsquery('merlot')) as rank,
ROUND(AVG(ST_Distance(ST_GeographyFromText('SRID=4326;POINT(0.001 0.001)'),ST_GeographyFromText('SRID=4326;POINT(' || stores.longitude || ' ' || stores.latitude || ')')))) AS distance
FROM
products
JOIN product_types ON products.product_type_id = product_types.id
JOIN categories ON products.category_id = categories.id
JOIN prices ON prices.product_id = products.id
JOIN currencies ON prices.currency_id = currencies.id
JOIN stores ON prices.store_id = stores.id
JOIN brands ON products.brand_id = brands.id
JOIN merchants ON stores.merchant_id = merchants.id
JOIN manufacturers ON brands.manufacturer_id = manufacturers.id
, delivery_zones
WHERE ...
(consulta completa aqui: http://pastebin.com/VjJPTQWj )
O problema é: observe a FROM products...parte, se eu substituir isso por FROM products_active AS products, a consulta dá erro com:
ERROR: column "products.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: products.id, products.status, products.brand_id, products....
As duas tabelas (products e products_active) possuem estruturas idênticas! Eles são herdados um do outro!
Estou perdendo uma sutileza em nomes de tabelas de alias?
Estou tentando otimizar uma consulta como:
UPDATE master SET count =
(SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)
Onde a tabela principal é grande, executar isso resulta em uma enorme varredura sequencial. Por outro lado, essa contagem não muda com tanta frequência, então minha ideia é reescrever assim:
UPDATE master SET count =
(SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)
WHERE count !=
(SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)
EXPLAIN me diz que isso resulta em dois subplanos, o que eu esperaria. Existe uma maneira de evitar os dois subplanos executados?
Uma tentativa de reescrever o acima como
UPDATE master SET count =
(SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id) AS cnt
WHERE count != cnt
falha com erro de sintaxe em "AS".