Obtendo a data da última modificação de uma tabela de banco de dados PostgreSQL

Não há registro confiável e confiável da última hora modificada de uma tabela. Usar o relfilenode está errado por vários motivos:

• As gravações são inicialmente registradas no log de cabeça de gravação (WAL) e, em seguida , preguiçosamente no heap (os arquivos de tabela). Uma vez que o registro está no WAL, o Pg não se apressa em gravá-lo no heap, e ele pode nem ser gravado até o próximo ponto de verificação do sistema;

• Tabelas maiores têm várias bifurcações, você teria que verificar todas as bifurcações e escolher o carimbo de data/hora mais recente;

• Um simples SELECTpode gerar atividade de gravação na tabela subjacente devido à configuração do bit de dica;

• autovaccum e outras manutenções que não alteram os dados visíveis do usuário ainda modificam os arquivos de relação;

• algumas operações, como vaccum full, substituirão o relfilenode. Pode não estar onde você espera se você estiver tentando olhar para ele simultaneamente sem usar um bloqueio apropriado.

Algumas opções

Se você não precisar de confiabilidade, poderá usar as informações em pg_stat_databasee pg_stat_all_tables. Isso pode fornecer a hora da última redefinição de estatísticas e estatísticas de atividade desde a última redefinição de estatísticas. Ele não informa quando foi a atividade mais recente, apenas que foi desde a última reinicialização das estatísticas, e não há informações sobre o que aconteceu antes da reinicialização das estatísticas. Então é limitado, mas já está lá.

Uma opção para fazer isso de forma confiável é usar um gatilho para atualizar uma tabela contendo os horários da última modificação de cada tabela. Esteja ciente de que isso serializará todas as gravações na tabela , destruindo a simultaneidade. Também adicionará um pouco de sobrecarga a cada transação. Eu não recomendo.

Uma alternativa um pouco menos terrível é usar LISTENe NOTIFY. Tenha um processo de daemon externo conectado ao PostgreSQL e LISTENpara eventos. Use ON INSERT OR UPDATE OR DELETEgatilhos para enviar NOTIFYs quando uma tabela for alterada, com o oid da tabela como a carga de notificação. Eles são enviados quando a transação é confirmada. Seu daemon pode acumular notificações de alteração e gravá-las preguiçosamente em uma tabela no banco de dados. Se o sistema travar, você perde o registro das modificações mais recentes, mas tudo bem, você apenas trata todas as tabelas como recém-modificadas se estiver inicializando após uma falha.

Para evitar o pior dos problemas de simultaneidade, você pode registrar os carimbos de data e hora de alteração usando um before insert or update or delete or truncate on tablename for each statement executegatilho, generalizado para usar a relação oid como parâmetro. Isso inseriria um (relation_oid, timestamp)par em uma tabela de log de alterações. Em seguida, você tem um processo auxiliar em uma conexão separada ou chamado periodicamente pelo seu aplicativo, agrega essa tabela para obter as informações mais recentes, mescla-a em uma tabela de resumo das alterações mais recentes e trunca a tabela de log. A única vantagem disso sobre a abordagem de ouvir/notificar é que ele não perde informações sobre falhas - mas também é ainda menos eficiente.

Outra abordagem pode ser escrever uma função de extensão C que usa (por exemplo) ProcessUtility_hook, ExecutorRun_hook, etc para interceptar alterações de tabela e atualizar estatísticas lentamente. Eu não olhei para ver o quão prático isso seria; dê uma olhada nas várias opções de _hook nas fontes.

A melhor maneira seria corrigir o código de estatísticas para registrar essas informações e enviar um patch ao PostgreSQL para inclusão no núcleo. Não comece apenas escrevendo código; levante sua ideia sobre -hackers uma vez que você tenha pensado sobre isso o suficiente para ter uma maneira bem definida de fazê-lo (ou seja, comece lendo o código, não poste apenas perguntando "como eu ..."). Pode ser bom adicionar os horários da última atualização a pg_stat_..., mas você teria que convencer a comunidade de que valeu a pena a sobrecarga ou fornecer uma maneira de rastreá-la opcionalmente - e você teria que escrever o código para manter as estatísticas e envie um patch , porque apenas alguém que deseja esse recurso vai se incomodar com isso.

Como eu faria

Se eu tivesse que fazer isso e não tivesse tempo de escrever um patch para fazê-lo corretamente, provavelmente usaria a abordagem de escuta/notificação descrita acima.

Atualização para carimbos de data/hora de confirmação do PostgreSQL 9.5

Atualização : PostgreSQL 9.5 tem timestamps de commit . Se você os tiver ativado postgresql.conf(e fez isso no passado também), você pode verificar o carimbo de data/hora do commit para a linha com o maior xminpara aproximar o horário da última modificação. É apenas uma aproximação, porque se as linhas mais recentes tiverem sido excluídas, elas não serão contadas.

Além disso, os registros de carimbo de data/hora de confirmação são mantidos apenas por um tempo limitado. Portanto, se você quiser saber quando uma tabela que não foi muito modificada é modificada, a resposta será efetivamente "não sei, há um tempo".

O PostgreSQL 9.5 nos permite rastrear o último commit modificado.

1. Verifique se o commit da faixa está ativado ou desativado usando a seguinte consulta

   show track_commit_timestamp;
   

2. Se retornar "ON", vá para a etapa 3 senão modifique postgresql.conf

   cd /etc/postgresql/9.5/main/
   vi postgresql.conf
   

   Mudar

   track_commit_timestamp = off
   

   para

   track_commit_timestamp = on
   

3. Reinicie o servidor PostgreSQL

4. Repita o passo 1.

5. Use a seguinte consulta para rastrear o último commit

   SELECT pg_xact_commit_timestamp(xmin), * FROM  YOUR_TABLE_NAME;
   
   SELECT pg_xact_commit_timestamp(xmin), * FROM YOUR_TABLE_NAME where COLUMN_NAME=VALUE;
   

Eu tenho quase o mesmo requisito para manter um cache de algumas tabelas em um aplicativo cliente. Digo quase , porque realmente não preciso saber a hora da última modificação, mas apenas detectar se algo mudou desde a última vez que o cache foi sincronizado.

Aqui está minha abordagem:

Desde que você tenha uma coluna id(PK), created_on(timestamp de inserção) e updated_on(timestamp de atualização, pode ser NULL) em cada tabela, você pode

SELECT id,greatest(created_on,updated_on) FROM %s ORDER BY greatest(created_on,updated_on) DESC LIMIT 1;

Se você concatenar isso e anexar o número de linhas, poderá criar uma tag de versão que se parece com count:id#timestamp, e ela será exclusiva para cada versão dos dados na tabela.

Sim, isso pode ser esperado - os dados sobre a mudança são armazenados no log de transações imediatamente. Os arquivos de dados podem ser atualizados com atraso checkpoint_timeout (o padrão é 5 minutos). O Postgres não é mantido permanentemente a qualquer momento que você solicitar.