6

I need to get the latest modified time of a table, so came across 

select relfilenode from pg_class where relname = 'test';

which gives me the relfilenode id, this seems to be a directory name in

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

For which I later extract the latest modified time.

Is this the right way to do this or are there any better methods to do the same

Akash
  • 4,956
  • 11
  • 42
  • 70

2 Answers2

5

Testing the mtime of the table's relfilenode won't work well. As Eelke noted VACUUM among other operations will modify the timestamp. Hint bit setting will modify the table too, causing it to appear to be "modified" by a SELECT. Additionally, sometimes a table has more than one fork to its on-disk relation (1GB chunks), and you'd have to check all of them to find the most recent.

If you want to keep a last modified time for a table, add an AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ... FOR EACH STATEMENT trigger that updates a timestamp row in a table you use for tracking modification times.

The downside of the trigger is that it'll contest a single row lock on the table, so it'll serialize all your transactions. It'll also greatly increase the chance of getting deadlocks. What you really want is probably something nontransactional that doesn't have to roll back when the transaction does, where if multiple transactions update a counter the highest value wins. There's nothing like that built in, though it might not be too hard as a C extension.

A slightly more sophisticated option would be to create a trigger that uses dblink to do an update of the last-updated counter. That'll avoid most of the contention problems but it'll actually make deadlocking worse because PostgreSQL's deadlock detection won't be able to "see" the fact that the two sessions are deadlocked via an intermediary. You'd need a way to SELECT ... FOR UPDATE with a timeout to make it reliable without aborting transactions too often.

In any case, a trigger won't catch DDL, though. DDL triggers ("Event triggers") are coming in Pg 9.3.

See also:

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • The severity of the single row contention could be reduced, of course. Updates and insert times could be logged against the row itself, and delete timestamps could be inserted to another table, indexed for quicker access to max(). Truncates would bring their own contention problems I'd think, and having them log to a single row in another table ought to be do-able. It's a bit messy for a pattern, but it would work. – David Aldridge Sep 02 '13 at 06:50
  • @DavidAldridge Yeah... I didn't want to get into that ;-) . One option can be a "running commentary" table that records each change and is periodically aggregated down to individual rows using a `WITH (DELETE FROM ... RETURNING) INSERT INTO` wCTE to collapse the rows down without requiring a table lock - concurrent inserts won't hurt. The downside is that getting the last changed time now requires a scan on a side-table. – Craig Ringer Sep 02 '13 at 07:51
1

I don't think that would be completly reliable as a vacuum would also modify the file(s) containing the table but the logical content of the table does not change during a vacuum.

You could create triggers for INSERT, UPDATE and DELETE that maintain the last modified timestamp for each table in another table. This method has a slight performance impact but will provide accurate information.

Eelke
  • 20,897
  • 4
  • 50
  • 76