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

On Thu, Feb 15, 2001 at 08:04:44PM -0000, Richard Huxton wrote:
> From: "Louis-David Mitterrand" <cunctator(at)apartia(dot)ch>
>
>
> > On Thu, Feb 15, 2001 at 03:09:16PM -0000, Richard Huxton wrote:
> > > From: "Louis-David Mitterrand" <cunctator(at)apartia(dot)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).

But which files? Are they individualized by table? If not, one would
have to fall back on a _database_ modification time, not a table one.

It would be nice to have that information somewhere. What do Pg
developers think?

> 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.

Yes, that's what I'll probably implement after all, now that it is
confirmed that there is no way to obtain that info from the system. Not
that much work.

Thanks for your help, cheers,

--
THESEE: Fuis, tratre. Ne viens point braver ici ma haine,
Et tenter un courroux que je retiens peine.
(Phdre, J-B Racine, acte 4, scne 2)