Tables accessed during last periods in postgresql

Question

I want to check that which tables are updated during the last period or say in descending order of accesstime of tables, then how can I view that for postgresql ?

Answer 1

You can get some information about the last change to a table with xmin, eg:

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

But you need to be aware of many caveats including modulo and wraparound and frozen xids.

testbed:

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;
--

method:

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
*/

cleanup:

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

Answer 2

A vanilla PostgreSQL installation does not log access to tables.

If you need that you have to implement it yourself. I would use triggers for that. I use a setup like this for many of my tables. I add a column named log_up to tables I want to track updates for:

log_up timestamp(0) without time zone
       DEFAULT (now())::timestamp(0) without time zone

As you can see, I am not interested in fractional seconds.
Trigger function:

CREATE OR REPLACE FUNCTION trg_log_up()
  RETURNS trigger AS
$BODY$
BEGIN

NEW.log_up := localtimestamp(0);
RETURN NEW;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Trigger:

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

There are a couple of related logging parameters you may be interested in additionally. Like log_connections or log_statement.

---

How to add trigger to all tables

You can create a script for all currently existing tables by querying the database catalog. For instance to generate the DDL statements for all tables in the schema public:

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

Returns:

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();
...

Of course, they all need to have a column log_up of type timestamp first. You can create a DDL script to add the column to all tables in a similar fashion.

---

Log only last UPDATE per table

If you are only interested in the last UPDATE per table, a simpler solution will do. Here is a demo how to keep track in one centralized table:

Separate schema for the purpose of this demo - so you can copy and try it at home:

CREATE SCHEMA x;

Table to hold update information:

CREATE TABLE x.lastup (
  schema_name text
, tbl_name text
, ts timestamp
, PRIMARY KEY (schema_name, tbl_name)
);

Trigger. Consult the manual about the special variables I use:

CREATE OR REPLACE FUNCTION x.trg_lastup()
  RETURNS trigger AS
$BODY$
BEGIN

UPDATE x.lastup
SET    ts = localtimestamp(0)
WHERE  schema_name = TG_TABLE_SCHEMA
AND    tbl_name = TG_TABLE_NAME;

RETURN NULL;   -- For AFTER trigger irrelevant

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Dummy table for testing:

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

Enter row for table in log table:

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

Trigger. Note that I use an AFTER trigger FOR EACH STATEMENT (cheaper)! More in the manual.

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

Test:

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

Voilá:

SELECT * FROM x.lastup;

Delete Test setup:

DROP SCHEMA x;

---

Or, if you want to exclude empty update (nothing changed), but at a higher cost because multiple updated rows trigger multiple log updates:

CREATE OR REPLACE FUNCTION x.trg_lastup()
  RETURNS trigger AS
$BODY$
BEGIN

IF OLD IS DISTINCT FROM NEW THEN  -- check for changes
    UPDATE x.lastup
    SET    ts = localtimestamp(0)
    WHERE  schema_name = TG_TABLE_SCHEMA
    AND    tbl_name = TG_TABLE_NAME;
END IF;

RETURN NULL;  -- For AFTER trigger irrelevant

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

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

---

To create triggers for all tables you want to include in this regime, use a similar DDL creation script like above.