Re: last UPDATE or INSERT time of a table? (not a row!)

Louis-David Mitterrand <cunctator@apartia.ch> writes:
> What I meant to do is detect a change at the _table_ level, not the row
> level. Is there such a field somewhere in the pg_tables?

There is not.

>> You could do some hack by checking the modified date on the individual
>> database files (no, I don't like it either).

This will not work.  The last file update time as seen by the Unix
kernel may be later than the last logical update of the table contents,
due to delayed update of tuple commit status bits and suchlike.  Not to
mention VACUUM, rolled-back transactions, etc.  Under WAL it gets worse:
we may actually postpone data-file writes as long as we can (since we
know it's written to the WAL logfile), so the kernel update time might
also be older than the last committed transaction for the table.

On top of that, a solution based on looking into the database directory
is not available remotely, nor to anyone not logged in as the postgres
user.

I like the recommendation someone else made: add a trigger that writes
an update to some other table whenever you change the table of interest.

            regards, tom lane