35

I'm trying to get when my table was modified by checking its file modification date as it is described in this answer. But the result is not always correct. The file modification date updates in several minute after I update my table. Is it correct behaviour? Does PostgreSQL store table modifications in some cache and then flush it to the hard drive?

So, how do I get the correct last modification date of a table (let's assume that auto vacuum modifications are ok too)?

I use PostgreSQL 9.2 under Linux Centos 6.2 x64.

  • 4
    I don't think the file modification time is reliable. It could also change due to autovacuum. The only reliable way is to store a modification timestamp in your table, maintained by a trigger. – a_horse_with_no_name Feb 4 '14 at 14:59
  • One idea would be that the information stored in the WAL files are written to the data files some (shorter or longer) time after committing the transaction. If you want, you can call this a cache :) Otherwise, I second what @a_horse_with_no_name said. – dezso Feb 4 '14 at 15:37
35

There is no reliable, authorative record of the last modified time of a table. Using the relfilenode is wrong for a lot of reasons:

  • Writes are initially recorded to the write-head log (WAL), then lazily to the heap (the table files). Once the record is in WAL, Pg doesn't rush to write it to the heap, and it might not even get written until the next system checkpoint;

  • Larger tables have multiple forks, you'd have to check all the forks and pick the newest timestamp;

  • A simple SELECT can generate write activity to the underlying table due to hint-bit setting;

  • autovaccum and other maintenance that doesn't change the user visible data still modifies the relation files;

  • some operations, like vaccum full, will replace the relfilenode. It might not be where you expect if you're trying to look at it concurrently without taking an appropriate lock.

A few options

If you don't need reliability, you can potentially use the information in pg_stat_database and pg_stat_all_tables. These can give you the time of the last stats reset, and activity stats since the last stats reset. It doesn't tell you when the most recent activity was, only that it was since the last stats reset, and there's no information about what happened before that stats reset. So it's limited, but it's already there.

One option for doing it reliably is to use a trigger to update a table containing the last-modified times for each table. Be aware that doing so will serialize all writes to the table, destroying concurrency. It will also add a fair bit of overhead to every transaction. I don't recommend it.

A slightly less awful alternative is to use LISTEN and NOTIFY. Have an external daemon process connect to PostgreSQL and LISTEN for events. Use ON INSERT OR UPDATE OR DELETE triggers to send NOTIFYs when a table changes, with the table oid as the notify payload. These get sent when the transaction commits. Your daemon can accumulate change notifications and lazily write them back to a table in the database. If the system crashes, you lose your record of most recent modifications, but that's ok, you just treat all tables as just-modified if you're starting up after a crash.

To avoid the worst of the concurrency issues you could instead log the change timestamps using a before insert or update or delete or truncate on tablename for each statement execute trigger, generalized to take the relation oid as a parameter. This would insert a (relation_oid, timestamp) pair into a change-logging table. You then have a helper process on a separate connection, or called periodically by your app, aggregate that table for the latest info, merge it into a summary table of most recent changes, and truncate the log table. The only advantage of this over the listen/notify approach is that it doesn't lose information on crash - but it's even less efficient, too.

Another approach might be to write a C extension function that uses (eg) ProcessUtility_hook, ExecutorRun_hook, etc to trap table changes and lazily update stats. I haven't looked to see how practical this would be; take a look at the various _hook options in the sources.

The best way would be to patch the statistics code to record this information and submit a patch to PostgreSQL for inclusion in core. Don't just start by writing code; raise your idea on -hackers once you've thought about it enough to have a well defined way to do it (i.e. start by reading the code, don't just post asking "how do I ..."). It might be nice to add last-updated times to pg_stat_..., but you'd have to convince the community it was worth the overhead or provide a way to make it optionally tracked - and you'd have to write the code to keep the stats and submit a patch, because only somebody who wants this feature is going to bother with that.

How I'd do it

If I had to do this, and didn't have the time to write a patch to do it properly, I'd probably use the listen/notify approach outlined above.

Update for PostgreSQL 9.5 commit timestamps

Update: PostgreSQL 9.5 has commit timestamps. If you have them enabled in postgresql.conf (and did so in the past too), you can check the commit timestamp for the row with the greatest xmin to approximate the last modified time. It's only an approximation because if the most recent rows have been deleted they won't be counted.

Also, commit timestamp records are only kept for a limited time. So if you want to tell when a table that isn't modified much is modified, the answer will effectively be "dunno, a while ago".

17

PostgreSQL 9.5 let us to track last modified commit.

  1. Check track commit is on or off using the following query

    show track_commit_timestamp;
    
  2. If it return "ON" go to step 3 else modify postgresql.conf

    cd /etc/postgresql/9.5/main/
    vi postgresql.conf
    

    Change

    track_commit_timestamp = off
    

    to

    track_commit_timestamp = on
    

    Reboot the system

    Repeat step 1.

  3. Use the following query to track last commit

    SELECT pg_xact_commit_timestamp(xmin), * FROM  YOUR_TABLE_NAME;
    
    SELECT pg_xact_commit_timestamp(xmin), * FROM YOUR_TABLE_NAME where COLUMN_NAME=VALUE;
    
  • 1
    You don't have to reboot the system on step 2. just restart the process. e.g. sudo service postgresql restart. – ijoseph Jun 7 '18 at 0:55
3

Yes, this can be expected behave - data about change are stored to transaction log immediately. Data files can be updated with checkpoint_timeout delay (default is 5 minutes). Postgres doesn't hold permanently any time that you request.

  • I'm not sure I understand how this answers the question. Yes, the data is stored to the transaction log, but that doesn't mean that one can get a modification time for a specific table easily (if that content is still in the log one can parse the log, but things get replayed out rather quickly). – Charles Duffy Sep 14 '16 at 15:26
  • sure, you can get all necessary informations from log, but the questions was directed to mtime of datafiles - the actualization of data files can be pretty random - few seconds - few minutes (max 1hour) after commit. – Pavel Stehule Sep 14 '16 at 16:47
  • The OP's own attempt was via looking at files, but their real intent is clearly to get a table mtime. But yes, I understand where you're coming from here (explaining why what they were doing didn't work) now. – Charles Duffy Sep 14 '16 at 16:49
2

I have almost the same requirement in order to maintain a cache of some tables on a client application. I say almost, because I don't really need to know the time of last modification, but only to detect if something has changed since the last time the cache was synchronized.

Here's my approach:

Provided you have an id (PK), created_on (insertion timestamp) and updated_on (update timestamp, may be NULL) column on every table, you can

SELECT id,greatest(created_on,updated_on) FROM %s ORDER BY greatest(created_on,updated_on) DESC LIMIT 1;

If you concat this and prepend the number of rows, you can build a version tag that looks like count:id#timestamp, and it will be unique for every version of the data in the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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