Last table change in PostgreSQL

Question

I need to get the last modified table time, so I came across

select relfilenode from pg_class where relname = 'test'; 

which gives me the relfilenode id, it looks like the directory name in

 L:\Databases\PostgresSQL\data\base\inodenumber 

For what I later retrieve the last modified time.

Is this the right way to do this, or are there any more efficient methods to do the same

Answer 1

Testing mtime relfilenode tables will not work well. As Eelke VACUUM noted, among other operations, the timestamp will be changed. Setting the prompt bit will also change the table, as a result of which it will be “changed” to SELECT . Also, sometimes a table has more than one fork for its relationship on the disk (1 GB pieces), and you will need to check all of them to find the latest ones.

If you want to save the last modified time for the table, add an AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ... FOR EACH STATEMENT , which updates the timestamp row in the table that you use to track the modification time.

The downside of the trigger is that it will dispute the lock of one row in the table, so it will serialize all your transactions. It will also significantly increase the likelihood of deadlocks. What you really want is probably something non-transactional that doesn't need to be rolled back when a transaction occurs, where if several transactions update the counter, the highest value wins. Nothing of the kind is built in there, although it may not be too complicated like the C. extension.

A slightly more complicated option would be to create a trigger that uses dblink to update the last updated counter. This will avoid most of the conflicting problems, but it will actually make the deadlock worse, because detecting a deadlock PostgreSQL will not be able to "see" the fact that two sessions have reached an impasse through an intermediary. You will need a SELECT ... FOR UPDATE method with a timeout to make it reliable without interrupting the transaction too often.

In any case, the trigger will not catch DDL. DDL triggers ("Event triggers") are included in Pg 9.3.

See also:

• How to find the last time the PostgreSQL database was updated?
• How to get the "last modified time" table in postgres?

Answer 2

I do not think this would be completely reliable, since the vacuum would also modify the file (s) containing the table, but the logical contents of the table did not change during the vacuum.

You can create triggers for INSERT, UPDATE, and DELETE that support the last modified timestamp for each table in another table. This method has a small impact on performance, but provides accurate information.