hank's questions

Eu tenho esta tabela:

CREATE TABLE fragment
(
  fragment_id integer,
  start_date timestamp without time zone,
  end_date timestamp without time zone,
  duration integer,
  -- <10+ more columns>
  revision_1 integer,
  revision_2 integer
)

É bem grande: 44 milhões de linhas, 27 GB de espaço em disco. A taxa de inserção diária é de cerca de 70 mil linhas.

Os dados nesta tabela quase nunca são atualizados, exceto para as duas últimas colunas denominadas revision_1e revision_2. Eles são atualizados por meio de gatilhos definidos em outras tabelas relacionadas. As atualizações ocorrem com muita frequência, especialmente para novas linhas na fragmenttabela. Cada linha pode ser atualizada até 50-100 vezes. Linhas antigas (digamos com 1 semana), no entanto, param de ser atualizadas, pois são consideradas 'processadas'.

Tanto quanto eu sei, UPDATEa operação no Postgres é implementada como algo como DELETE+ INSERT. Portanto, quando um valor em uma única coluna é atualizado, toda a linha é marcada como excluída e uma nova linha é criada. Acho que é por isso que minha fragmentmesa é aspirada todos os dias, o que leva várias horas.

A questão é: geralmente é uma boa ideia extrair 'colunas quentes' em uma tabela separada? Quero dizer algo assim:

CREATE TABLE fragment_revision
(
  fragment_id integer,
  revision_1 integer,
  revision_2 integer
)

Tenho uma tabela no PostgreSQL que descreve alguns eventos que possuem uma data inicial e uma data final:

CREATE TABLE my_table
(
  event_id serial NOT NULL,
  start_date timestamp without time zone NOT NULL,
  end_date timestamp without time zone NOT NULL
)

Um único evento pode se sobrepor ao anterior e ao seguinte. Na tabela a seguir, os primeiros 3 dos 4 eventos formam um intervalo de tempo contíguo:

1   '2015-04-02 22:09:03'   '2015-04-02 22:19:05'
2   '2015-04-02 22:17:38'   '2015-04-02 22:27:38'
3   '2015-04-02 22:25:21'   '2015-04-02 22:36:23'
4   '2015-04-02 22:45:23'   '2015-04-02 22:55:23'

É possível escrever uma consulta que verifique se existe um intervalo de data contíguo entre duas datas especificadas?

Eu gostaria de ter algo como:

select ...
from my_table
where start_date > '2015-04-02' and end_date < '2015-04-06'

Estou tentando executar um arquivo de script sql em uma única transação. O arquivo contém inclusões para alguns outros scripts que, no meu exemplo, criam algumas tabelas. Se parece com isso:

\include ../tables/table1.cre
\include ../tables/table2.cre
...
\include ../tables/table10.cre

Estou executando usando psql:

psql -X --set AUTOCOMMIT=off --set ON_ERROR_STOP=on -e --single-transaction -d my_db -f my_script.sql

O problema é que os erros com o comando include meta não causam uma reversão transactioniopn. por exemplo, se algum dos arquivos tableX.cre estiver faltando, quaisquer alterações antes de sua inclusão serão confirmadas. No entanto, se houver algum erro de sintaxe SQL, tudo funcionará conforme o esperado.

É possível manipular de alguma forma erros relacionados à inclusão e reverter a transação ativa?

Estou usando o PostgreSQL 9.2.1.

Estou lutando com o mecanismo LISTEN - NOTIFY do PostgreSQL. Eu abro dois consoles e me conecto ao meu banco de dados usando o cliente psql:

psql -U postgres test_db

No primeiro console eu assino alguma notificação:

test_db=# listen event;
LISTEN

No segundo console estou tentando enviar uma notificação:

test_db=# notify event;
NOTIFY

E nada acontece no primeiro console. Mas se eu enviar uma notificação do console 1, eu a recebo e também recebo todas as notificações anteriores do console 2.

Eu uso o PostgreSQL 9.2.1 no CentOS 6.5 x64.

Estou tentando obter quando minha tabela foi modificada verificando a data de modificação do arquivo, conforme descrito nesta resposta . Mas o resultado nem sempre é correto. A data de modificação do arquivo é atualizada em vários minutos após eu atualizar minha tabela. É um comportamento correto? O PostgreSQL armazena as modificações da tabela em algum cache e depois as libera no disco rígido?

Então, como obtenho a data correta da última modificação de uma tabela (vamos supor que as modificações de vácuo automático também estão corretas)?

Eu uso o PostgreSQL 9.2 no Linux Centos 6.2 x64.