我正在使用一个批量更新的postgreSQL数据库。我需要知道数据库(或数据库中的表)最后一次被更新或修改的时间。
我在postgeSQL论坛上看到有人建议使用日志记录和查询您的日志时间。这对我来说是行不通的,因为我无法控制客户的代码库。
发布于 2009-05-22 19:02:13
您可以编写一个trigger,使其在每次对特定表执行插入/更新操作时运行。常见的用法是将行的"created“或"last_updated”列设置为当前时间,但如果不想更改现有表,也可以在中央位置更新时间。
因此,举个例子,典型的方法如下:
CREATE FUNCTION stamp_updated() RETURNS TRIGGER LANGUAGE 'plpgsql' AS $$
BEGIN
NEW.last_updated := now();
RETURN NEW;
END
$$;
-- repeat for each table you need to track:
ALTER TABLE sometable ADD COLUMN last_updated TIMESTAMP;
CREATE TRIGGER sometable_stamp_updated
BEFORE INSERT OR UPDATE ON sometable
FOR EACH ROW EXECUTE PROCEDURE stamp_updated();
然后,要找到上次更新时间,您需要从跟踪的每个表中选择"MAX(last_updated)“,并取其中最大的一个,例如:
SELECT MAX(max_last_updated) FROM (
SELECT MAX(last_updated) AS max_last_updated FROM sometable
UNION ALL
SELECT MAX(last_updated) FROM someothertable
) updates
对于具有串行(或类似生成的)主键的表,您可以尝试通过使用主键索引来避免顺序扫描来查找最新的更新时间,或者在last_updated上创建索引。
-- get timestamp of row with highest id
SELECT last_updated FROM sometable ORDER BY sometable_id DESC LIMIT 1
请注意,在in不是完全连续的情况下,这可能会给出稍微错误的结果,但是您需要多大的准确性呢?(请记住,事务意味着行可以以不同于创建它们的顺序对您可见。)
避免向每个表添加“已更新”列的另一种方法是使用一个中心表来存储更新时间戳。例如:
CREATE TABLE update_log(table_name text PRIMARY KEY, updated timestamp NOT NULL DEFAULT now());
CREATE FUNCTION stamp_update_log() RETURNS TRIGGER LANGUAGE 'plpgsql' AS $$
BEGIN
INSERT INTO update_log(table_name) VALUES(TG_TABLE_NAME);
RETURN NEW;
END
$$;
-- Repeat for each table you need to track:
CREATE TRIGGER sometable_stamp_update_log
AFTER INSERT OR UPDATE ON sometable
FOR EACH STATEMENT EXECUTE stamp_update_log();
这将为您提供一个表,每个表更新一行:然后您可以这样做:
SELECT MAX(updated) FROM update_log
以获取上次更新时间。(如果需要,您可以将其按表拆分)。这个表当然会继续增长:要么在“更新”上创建一个索引(这应该会让获取最新的索引变得非常快),要么周期性地截断它,如果这符合你的用例的话(例如,在表上使用独占锁,获取最新的更新时间,如果你需要定期检查是否有更改,则截断它)。
另一种方法是在数据库配置中设置'log_statement = mod‘(为集群全局设置,或者在需要跟踪的数据库或用户上设置),然后所有修改数据库的语句都将写入服务器日志。然后你需要在数据库之外编写一些东西来扫描服务器日志,过滤掉你不感兴趣的表,等等。
发布于 2011-08-28 05:24:14
看起来您可以使用pg_stat_database
来获取事务计数,并检查此计数在每次备份运行时是否发生更改-有关更多详细信息,请参阅this dba.se answer和注释
发布于 2009-05-22 18:19:54
请参阅以下文章:
MySQL与PostgreSQL:向表http://www.pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-a-last-modified-column-to-a-table.html添加“Last Modified Time”列
https://stackoverflow.com/questions/899203
复制相似问题