Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
possible duplicate of this question? –  Jack Douglas Feb 7 '12 at 13:46

2 Answers 2

up vote 3 down vote accepted

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;
share|improve this answer
1  
+1 Not perfectly reliable, but very simple and cheap. –  Erwin Brandstetter Feb 8 '12 at 13:04

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.

share|improve this answer
    
What if I want to CREATE TRIGGER log_up BEFORE UPDATE ON <<all tables of database>> FOR EACH ROW EXECUTE PROCEDURE trg_log_up(); –  Bhavik Ambani Feb 7 '12 at 11:51
    
But for that I will have to change all the tables of my database, which is not feasible –  Bhavik Ambani Feb 7 '12 at 13:04
    
@BhavikAmbani: I added another solution for the last update per table. –  Erwin Brandstetter Feb 7 '12 at 18:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.