主页 / dba / 问题 / 12374
Accepted
Bhavik Ambani
Asked: 2012-02-08 02:45:35 +0800 CST

上一期间访问的表

  • 772

我想检查在给定时间段内更新了哪些表，例如按每个表访问时间的降序排列。

我怎样才能为 PostgreSQL 获取它？

postgresql trigger

2 个回答

  1. Best Answer

    您可以使用 获取有关表的最后更改的一些信息xmin，例如：

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

    但是您需要注意许多注意事项，包括取模和环绕以及冻结的 xid。

    试验台：

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

    方法：

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

    清理：

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

  2. vanilla PostgreSQL 安装不会记录对表的访问。

    如果您需要，则必须自己实施。我会为此使用触发器。我为我的许多表使用这样的设置。我添加了一个名为log_up表的列，我想跟踪更新：

    log_up timestamptz DEFAULT current_timestamp;

    使用timestamptz( timestamp with time zone) 跨时区工作：

    触发功能：

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

    扳机：

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

    您可能还对一些相关的日志记录参数感兴趣。喜欢log_connections或log_statement。

    更新： 还要考虑在 Postgres 9.5中添加的“提交时间戳”：

    将触发器添加到所有表

    您可以通过查询数据库目录为所有当前存在的表创建脚本。例如，为架构中的所有表生成 DDL 语句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

    退货：

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

    当然，他们都需要先有一个log_up类型的列timestamptz。您可以创建一个 DDL 脚本，以类似的方式将列添加到所有表中。

    UPDATE每个表只记录最后一个

    如果您只对UPDATE每个表的最后一个感兴趣，那么一个更简单的解决方案就可以了。这是一个如何在一个集中表中跟踪的演示：

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

    扳机。有关我使用的特殊变量，请参阅手册：

    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;

    用于测试的虚拟表：

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

    在日志表中为表输入行：

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

    扳机。请注意，我使用AFTER触发器FOR EACH STATEMENT（更便宜）。此处的手册中有更多内容。

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

    测试：

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

    瞧：

    SELECT * FROM lastup;

    或者，如果你想排除空更新（没有改变），但代价更高，因为多个更新行触发多个日志更新：

    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

    要为要包含在此机制中的所有表创建触发器，请使用与上面类似的 DDL 创建脚本。

    • 2

相关问题