Tabelas acessadas durante o último período

Você pode obter algumas informações sobre a última alteração em uma tabela com xmin, por exemplo:

select max(xmin::text::bigint) from t;

Mas você precisa estar ciente de muitas advertências , incluindo modulo, wraparound e frozen xids .

banco de ensaio:

set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create or replace function f(p_schema in text, p_table in text) 
                  returns integer language plpgsql immutable as $$
declare
  n integer;
begin
  execute 'select max(xmin::text::bigint) from '||p_schema||'.'||p_table into n;
  return n;
end;$$;
--
create table foo as select generate_series(1, 100) as id;
create table bar as select generate_series(1, 100) as id;
create table baz as select generate_series(1, 100) as id;
--

método:

select table_name, f(table_schema, table_name)
from information_schema.tables
where table_schema='stack'
order by 2 desc;
/*
 table_name |   f
------------+--------
 baz        | 784657
 bar        | 784656
 foo        | 784655
*/
--
update foo set id=id+1 where id=100;
--
select table_name, f(table_schema, table_name)
from information_schema.tables
where table_schema='stack'
order by 2 desc;
/*
 table_name |   f
------------+--------
 foo        | 784658
 baz        | 784657
 bar        | 784656
*/

limpar:

drop schema stack cascade;
set role dba;
drop role stack;

Uma instalação básica do PostgreSQL não registra o acesso às tabelas.

Se você precisar disso, terá que implementá-lo sozinho. Eu usaria gatilhos para isso. Eu uso uma configuração como esta para muitas das minhas tabelas. Eu adiciono uma coluna nomeada log_upàs tabelas para as quais desejo rastrear atualizações:

log_up timestamptz DEFAULT current_timestamp;

Use timestamptz( timestamp with time zone) que funciona em fusos horários:

• Ignorando totalmente os fusos horários no Rails e no PostgreSQL

Função de gatilho:

CREATE OR REPLACE FUNCTION trg_log_up()
  RETURNS trigger AS
$func$
BEGIN
   NEW.log_up := current_timestamp;
   RETURN NEW;
END;
$func$
  LANGUAGE plpgsql VOLATILE;

Acionar:

CREATE TRIGGER log_up
BEFORE UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_log_up();

Existem alguns parâmetros de registro relacionados nos quais você pode estar interessado adicionalmente. Gostar log_connectionsou log_statement.

Atualização: Considere também "commit timestamps" adicionados no Postgres 9.5 :

• Use a comparação de carimbo de data/hora do sistema na consulta SELECT

Adicionar gatilho a todas as tabelas

Você pode criar um script para todas as tabelas existentes consultando o catálogo do banco de dados. Por exemplo, para gerar as instruções DDL para todas as tabelas no esquema public:

SELECT string_agg(format('CREATE TRIGGER log_up BEFORE UPDATE ON %s '
                         'FOR EACH ROW EXECUTE PROCEDURE trg_log_up();'
                        , c.oid::regclass), E'\n')
FROM   pg_namespace n
JOIN   pg_class     c ON c.relnamespace = n.oid
WHERE  n.nspname = 'public';
-- AND c.relname ~~* '%tbl%' -- to filter tables by name

Retorna:

CREATE TRIGGER log_up BEFORE UPDATE ON tbl1 FOR EACH ROW EXECUTE PROCEDURE trg_log_up();
CREATE TRIGGER log_up BEFORE UPDATE ON tbl2 FOR EACH ROW EXECUTE PROCEDURE trg_log_up();
CREATE TRIGGER log_up BEFORE UPDATE ON tbl3 FOR EACH ROW EXECUTE PROCEDURE trg_log_up();
...

Claro, todos eles precisam ter uma coluna log_updo tipo timestamptzprimeiro. Você pode criar um script DDL para adicionar a coluna a todas as tabelas de maneira semelhante.

Log apenas por último UPDATEpor tabela

Se você estiver interessado apenas no último UPDATEpor tabela, uma solução mais simples servirá. Aqui está uma demonstração de como acompanhar em uma tabela centralizada:

CREATE TABLE lastup (
  schema_name text
, tbl_name text
, ts timestamptz
, PRIMARY KEY (schema_name, tbl_name)
);

Acionar. Consulte o manual sobre as variáveis ​​especiais que utilizo:

CREATE OR REPLACE FUNCTION trg_lastup()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE lastup
   SET    ts = current_timestamp
   WHERE  schema_name = TG_TABLE_SCHEMA
   AND    tbl_name    = TG_TABLE_NAME;

   RETURN NULL;   -- For AFTER trigger irrelevant
END
$func$  LANGUAGE plpgsql;

Tabela fictícia para testes:

CREATE TABLE dummy (id int);
INSERT INTO dummy VALUES (1), (2), (3);

Digite a linha para a tabela na tabela de log:

INSERT INTO lastup(schema_name, tbl_name) VALUES ('public', 'dummy');

Acionar. Observe que eu uso um AFTERgatilho FOR EACH STATEMENT(mais barato). Mais no manual aqui.

CREATE TRIGGER log_up
AFTER UPDATE ON dummy
FOR EACH STATEMENT EXECUTE PROCEDURE trg_lastup();

Teste:

UPDATE dummy
SET    id = id + 5
WHERE  id < 3;

Voilá:

SELECT * FROM lastup;

Ou , se você deseja excluir atualizações vazias (nada alterado), mas a um custo mais alto porque várias linhas atualizadas acionam várias atualizações de log:

CREATE OR REPLACE FUNCTION trg_lastup()
  RETURNS trigger AS
$func$
BEGIN
   IF OLD IS DISTINCT FROM NEW THEN  -- check for changes
      UPDATE lastup
      SET    ts = current_timestamp
      WHERE  schema_name = TG_TABLE_SCHEMA
      AND    tbl_name    = TG_TABLE_NAME;
   END IF;

   RETURN NULL;  -- For AFTER trigger!
END;
$func$  LANGUAGE plpgsql;

CREATE TRIGGER log_up
AFTER UPDATE ON dummy
FOR EACH ROW EXECUTE PROCEDURE trg_lastup();  -- per ROW instead of STATEMENT

Para criar gatilhos para todas as tabelas que você deseja incluir neste regime, use um script de criação de DDL semelhante ao acima.