Re: Last modification time of a database?

On Mar 23, 2009, at 5:00 PM, Craig Ringer wrote:

> Erik Jones wrote:
>
>> Am I missing something obvious here? If not, has anyone come up
>> with a reliable way to do this?
>
> Triggers on all your tables that append to a logging table?
>
> Have the client do it?
>
> Note that you do *NOT* want to have triggers that attempt to UPDATE
> a table to record the last modified time for that table. They'll
> cause transactions that touch the same table to block waiting until
> the first one commits/rolls back, so they'll ruin your concurrency.
> They may also cause unexpected deadlock aborts of transactions.

These are all client databases at the web hosting company I work at.
I can't go putting triggers on all of their tables. I think I'll just
start taking snapshots of pertinent data from pg_stat_activity and
after I've been collecting data for a while run a report of dbs that
haven't seen connections in X long since what I'm really after is
inactive databases.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k