Scott,
Thank you.
>I know you're running windows, but if you can get bash working on it,
> here's a simple bash script I wrote that when it detects too many
> people connected creates a table called pg_stat_bk_20110120140634
> (i.e. date and time) so I can then look over what was in
> pg_stat_activity when things were acting up.
I can run scripts from windows scheduler for every 4 minutes or maybe from
pgadmin or maybe frequently accessed table triggers.
Whan command should be used to detect presence of hangup ( maybe query
running more than 60
seconds or too many idle transactions?)
should pg_stat_activity.query_start column used for this ?
How to log memory usage ?
Is it best way to use initially:
create table activity as select * from pg_stat_activity limit 0;
create table locks as select * from pg_locks limit 0;
and if script detects hangup it invokes
insert into activity select * from pg_stat_activity;
insert into locks select * from pg_locks;
How to add log timestamp column to activity and locks tables ?
How to log memory usage ?
Can users notice server perfomance degration due to this?
What else to log ?
How to create plpgsql procedure for this ?
There are few tables which are used frequently. Hangup occurs probably if
queries are invoked againt those tables.
Is it better to implement this as trigger for those tables?
Andrus.