Re: Last modification time

Johan Vromans <jvromans(at)squirrel(dot)nl> writes:

> Greetings,
>
> For a big application, I want to generate reports from the database and
> keep these on-line as long as they reflect the actual contents of the
> database. I only want to regenerate the reports when needed, i.e.,
> when the database contents have changed.
>
> I'm sure PostgreSQL can tell me when the last update has been
> COMMITted but until now I haven't been able to find out how. I must
> have used the wrong search terms...
>
> Can anyone tell me how to find the last update time of a database?

There isn't any out-of-the-box way that I know of.

I would put an AFTER trigger on all the tables concerned that inserts
a row into an audit table. Your report generator can then run
periodically, see if there are any new audit entries, generate
reports, and clean out the audit table (if desired).

Note that the audit table may grow very fast and need vacuuming a lot
if you clean it out.

Audit tables are useful for other things too, if you can afford them.

-Doug