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

From: "Louis-David Mitterrand" <cunctator@apartia.ch>


> On Thu, Feb 15, 2001 at 03:09:16PM -0000, Richard Huxton wrote:
> > From: "Louis-David Mitterrand" <cunctator@apartia.ch>
> >
> > > Is there a way to check the last time a table was UPDATEd or INSERTEd?
> > > Is there a timestamp somewhere in the system tables?
> > >
[snipped]
>
> Yes, I have read that thread qnd implemented a similar trigger in the
> past.
>
> 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?

Ah - sorry - misunderstood.

I don't know of any field in the system tables. In fact, I can't see any
timestamps on any system tables and you'd have thought there would be some.
You could do some hack by checking the modified date on the individual
database files (no, I don't like it either).

Or, you could set up a "table_last_modified" table recording (table_name
text, last_mod timestamp) and use a trigger on each table you want to
monitor something like:

CREATE TRIGGER ... UPDATE OR INSERT ... ON FOO ...
    UPDATE table_last_modified SET last_mod=now() WHERE table_name='foo'

Probably more effort than you were looking for, but should work.

- Richard Huxton