Re: last UPDATE or INSERT time of a table? (not a row!) - Mailing list pgsql-general

From Richard Huxton
Subject Re: last UPDATE or INSERT time of a table? (not a row!)
Date
Msg-id 003a01c0978a$8bd75540$1001a8c0@archonet.com
Whole thread Raw
In response to last UPDATE or INSERT time of a table?  (Louis-David Mitterrand <cunctator@apartia.ch>)
Responses Re: last UPDATE or INSERT time of a table? (not a row!)
List pgsql-general
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


pgsql-general by date:

Previous
From: Hal Davison
Date:
Subject: Array Elements
Next
From: Frank Joerdens
Date:
Subject: Re: Re: PostgreSQL vs Oracle vs DB2 vs MySQL - Which should I use?