<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.36 2006/06/29 20:00:08 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.37 2006/08/19 01:36:23 tgl Exp $ -->

<chapter id="monitoring">

<title>Monitoring Database Activity</title>

<row>

<entry><structname>pg_stat_activity</></entry>

<entry>One row per server process, showing database OID, database name,

process <acronym>ID</>, user OID, user name, current query, time at

process <acronym>ID</>, user OID, user name, current query, query's

waiting status, time at

which the current query began execution, time at which the process

was started, and client's address and port number. The columns

that report data on the current query are available unless the

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_backend_waiting</function>(<type>integer</type>)</literal></entry>

<entry><type>boolean</type></entry>

<entry>

True if the given server process is waiting for a lock,

but only if the current user is a superuser or the same user as that of

the session being queried (and

<varname>stats_command_string</varname> is on)

</entry>

</row>

<row>

<entry><literal><function>pg_stat_get_backend_activity_start</function>(<type>integer</type>)</literal></entry>

<entry><type>timestamp with time zone</type></entry>

pg_stat_get_backend_pid(S.backendid)AS procpid,

pg_stat_get_backend_userid(S.backendid)AS usesysid,

U.rolnameAS usename,

pg_stat_get_backend_activity(S.backendid)AS current_query,

pg_stat_get_backend_activity(S.backendid)AS current_query,

pg_stat_get_backend_waiting(S.backendid)AS waiting,

pg_stat_get_backend_activity_start(S.backendid)AS query_start,

pg_stat_get_backend_start(S.backendid)AS backend_start,

pg_stat_get_backend_client_addr(S.backendid)AS client_addr,

beentry->st_databaseid=MyDatabaseId;

beentry->st_userid=userid;

beentry->st_clientaddr=clientaddr;

beentry->st_waiting= false;

beentry->st_activity[0]='\0';

beentry->st_activity[PGBE_ACTIVITY_SIZE-1]='\0';

}

pgstat_report_waiting(boolwaiting)

{

volatilePgBackendStatus*beentry;

if (!pgstat_collect_querystring)

return;

beentry=MyBEEntry;

beentry->st_waiting=waiting;

}

LOCK_PRINT("WaitOnLock: sleeping on lock",

locallock->lock,locallock->tag.mode);

if (update_process_title)

{

old_status=get_ps_display(&len);

set_ps_display(new_status, false);

new_status[len]='\0';/* truncate off " waiting" */

}

pgstat_report_waiting(true);

awaitedLock=locallock;

awaitedOwner=owner;

awaitedLock=NULL;

if (update_process_title)

{

set_ps_display(new_status, false);

pfree(new_status);

}

pgstat_report_waiting(false);

LOCK_PRINT("WaitOnLock: wakeup on lock",

locallock->lock,locallock->tag.mode);

externDatumpg_stat_get_backend_dbid(PG_FUNCTION_ARGS);

externDatumpg_stat_get_backend_userid(PG_FUNCTION_ARGS);

externDatumpg_stat_get_backend_activity(PG_FUNCTION_ARGS);

externDatumpg_stat_get_backend_waiting(PG_FUNCTION_ARGS);

externDatumpg_stat_get_backend_activity_start(PG_FUNCTION_ARGS);

externDatumpg_stat_get_backend_start(PG_FUNCTION_ARGS);

externDatumpg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);

}

pg_stat_get_backend_waiting(PG_FUNCTION_ARGS)

{

int32beid=PG_GETARG_INT32(0);

boolresult;

PgBackendStatus*beentry;

if ((beentry=pgstat_fetch_stat_beentry(beid))==NULL)

PG_RETURN_NULL();

if (!superuser()&&beentry->st_userid!=GetUserId())

PG_RETURN_NULL();

result=beentry->st_waiting;

PG_RETURN_BOOL(result);

}

pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS)

{

DESCR("Statistics: User ID of backend");

DATA(insertOID=1940 (pg_stat_get_backend_activityPGNSPPGUID12fftfs125"23"_null__null__null_pg_stat_get_backend_activity-_null_ ));

DESCR("Statistics: Current query of backend");

DATA(insertOID=2853 (pg_stat_get_backend_waitingPGNSPPGUID12fftfs116"23"_null__null__null_pg_stat_get_backend_waiting-_null_ ));

DESCR("Statistics: Is backend currently waiting for a lock");

DATA(insertOID=2094 (pg_stat_get_backend_activity_startPGNSPPGUID12fftfs11184"23"_null__null__null_pg_stat_get_backend_activity_start-_null_));

DESCR("Statistics: Start time for current query of backend");

DATA(insertOID=1391 (pg_stat_get_backend_startPGNSPPGUID12fftfs11184"23"_null__null__null_pg_stat_get_backend_start-_null_));

Oidst_userid;

SockAddrst_clientaddr;

boolst_waiting;

charst_activity[PGBE_ACTIVITY_SIZE];

}PgBackendStatus;

externvoidpgstat_bestart(void);

externvoidpgstat_report_activity(constchar*what);

externvoidpgstat_report_waiting(boolwaiting);

externvoidpgstat_initstats(PgStat_Info*stats,Relationrel);

pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);

pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings() a(name text, setting text, unit text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text);

pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid.rolconfig AS useconfig FROM pg_authid WHERE pg_authid.rolcanlogin;

pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid));

pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_waiting(s.backendid) AS waiting,pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid));

pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));

pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;

pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit FROM pg_database d;