<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.72 2009/11/28 23:38:07 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.73 2009/11/29 18:14:30 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,

query's waiting status, time at which the current transaction and

current query began execution, time at which the process was

started, client's address and port number, and application name.

name, process <acronym>ID</>, user OID, user name,application name,

currentquery, query's waiting status, time at which the current

transaction andcurrent query began execution, time at which the

process wasstarted,andclient's address and port number.

The columns that report data on the current query are available unless

the parameter <varname>track_activities</varname> has been turned off.

Furthermore, these columnsand the application nameare only visible if

theuser examining theview is a superuser or the same as the user

owning the processbeing reported on.

Furthermore, these columns are only visible if the user examining

the view is a superuser or the same as the user owning the process

being reported on.

</entry>

</row>

S.procpid,

S.usesysid,

U.rolnameAS usename,

S.application_name,

S.current_query,

S.waiting,

S.xact_start,

S.query_start,

S.backend_start,

S.client_addr,

S.client_port,

S.application_name

S.client_port

FROM pg_database D, pg_stat_get_activity(NULL)AS S, pg_authid U

WHERES.datid=D.oidAND

S.usesysid=U.oid;

TupleDescInitEntry(tupdesc, (AttrNumber)1,"datid",OIDOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)2,"procpid",INT4OID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)3,"usesysid",OIDOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)4,"current_query",TEXTOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)5,"waiting",BOOLOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)6,"act_start",TIMESTAMPTZOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)7,"query_start",TIMESTAMPTZOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)8,"backend_start",TIMESTAMPTZOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)9,"client_addr",INETOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)10,"client_port",INT4OID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)11,"application_name",TEXTOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)4,"application_name",TEXTOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)5,"current_query",TEXTOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)6,"waiting",BOOLOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)7,"act_start",TIMESTAMPTZOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)8,"query_start",TIMESTAMPTZOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)9,"backend_start",TIMESTAMPTZOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)10,"client_addr",INETOID,-1,0);

TupleDescInitEntry(tupdesc, (AttrNumber)11,"client_port",INT4OID,-1,0);

funcctx->tuple_desc=BlessTupleDesc(tupdesc);

MemSet(nulls,0,sizeof(nulls));

if (*(int*) (funcctx->user_fctx)>0)

{

beentry=pgstat_fetch_stat_beentry(*(int*) (funcctx->user_fctx));

}

{

beentry=pgstat_fetch_stat_beentry(funcctx->call_cntr+1);/* 1-based index */

}

if (!beentry)

{

inti;

for (i=0;i<sizeof(nulls) /sizeof(nulls[0]);i++)

nulls[i]= true;

nulls[3]= false;

values[3]=CStringGetTextDatum("<backend information not available>");

nulls[4]= false;

values[4]=CStringGetTextDatum("<backend information not available>");

tuple=heap_form_tuple(funcctx->tuple_desc,values,nulls);

SRF_RETURN_NEXT(funcctx,HeapTupleGetDatum(tuple));

values[0]=ObjectIdGetDatum(beentry->st_databaseid);

values[1]=Int32GetDatum(beentry->st_procpid);

values[2]=ObjectIdGetDatum(beentry->st_userid);

if (beentry->st_appname)

values[3]=CStringGetTextDatum(beentry->st_appname);

nulls[3]= true;

if (superuser()||beentry->st_userid==GetUserId())

{

if (*(beentry->st_activity)=='\0')

{

values[3]=CStringGetTextDatum("<command string not enabled>");

values[4]=CStringGetTextDatum("<command string not enabled>");

}

{

values[3]=CStringGetTextDatum(beentry->st_activity);

values[4]=CStringGetTextDatum(beentry->st_activity);

}

values[4]=BoolGetDatum(beentry->st_waiting);

values[5]=BoolGetDatum(beentry->st_waiting);

if (beentry->st_xact_start_timestamp!=0)

values[5]=TimestampTzGetDatum(beentry->st_xact_start_timestamp);

values[6]=TimestampTzGetDatum(beentry->st_xact_start_timestamp);

nulls[5]= true;

nulls[6]= true;

if (beentry->st_activity_start_timestamp!=0)

values[6]=TimestampTzGetDatum(beentry->st_activity_start_timestamp);

values[7]=TimestampTzGetDatum(beentry->st_activity_start_timestamp);

nulls[6]= true;

nulls[7]= true;

if (beentry->st_proc_start_timestamp!=0)

values[7]=TimestampTzGetDatum(beentry->st_proc_start_timestamp);

values[8]=TimestampTzGetDatum(beentry->st_proc_start_timestamp);

nulls[7]= true;

nulls[8]= true;

memset(&zero_clientaddr,0,sizeof(zero_clientaddr));

if (memcmp(&(beentry->st_clientaddr),&zero_clientaddr,

sizeof(zero_clientaddr)==0))

{

nulls[8]= true;

nulls[9]= true;

nulls[10]= true;

}

{

NI_NUMERICHOST |NI_NUMERICSERV);

if (ret)

{

nulls[8]= true;

nulls[9]= true;

nulls[10]= true;

}

{

clean_ipv6_addr(beentry->st_clientaddr.addr.ss_family,remote_host);

values[8]=DirectFunctionCall1(inet_in,

values[9]=DirectFunctionCall1(inet_in,

CStringGetDatum(remote_host));

values[9]=Int32GetDatum(atoi(remote_port));

values[10]=Int32GetDatum(atoi(remote_port));

}

}

elseif (beentry->st_clientaddr.addr.ss_family==AF_UNIX)

nulls[8]= true;

values[9]=DatumGetInt32(-1);

nulls[9]= true;

values[10]=DatumGetInt32(-1);

}

{

nulls[8]= true;

nulls[9]= true;

nulls[10]= true;

}

}

if (beentry->st_appname)

values[10]=CStringGetTextDatum(beentry->st_appname);

nulls[10]= true;

}

{

values[3]=CStringGetTextDatum("<insufficient privilege>");

nulls[4]= true;

values[4]=CStringGetTextDatum("<insufficient privilege>");

nulls[5]= true;

nulls[6]= true;

nulls[7]= true;

DESCR("statistics: last auto analyze time for a table");

DATA(insertOID=1936 (pg_stat_get_backend_idsetPGNSPPGUID1211000ffftts0023""_null__null__null__null_pg_stat_get_backend_idset_null__null__null_ ));

DESCR("statistics: currently active backend IDs");

DATA(insertOID=2022 (pg_stat_get_activityPGNSPPGUID1211000ffffts102249"23""{23,26,23,26,25,16,1184,1184,1184,869,23,25}""{i,o,o,o,o,o,o,o,o,o,o,o}""{pid,datid,procpid,usesysid,current_query,waiting,xact_start,query_start,backend_start,client_addr,client_port,application_name}"_null_pg_stat_get_activity_null__null__null_ ));

DATA(insertOID=2022 (pg_stat_get_activityPGNSPPGUID1211000ffffts102249"23""{23,26,23,26,25,25,16,1184,1184,1184,869,23}""{i,o,o,o,o,o,o,o,o,o,o,o}""{pid,datid,procpid,usesysid,application_name,current_query,waiting,xact_start,query_start,backend_start,client_addr,client_port}"_null_pg_stat_get_activity_null__null__null_ ));

DESCR("statistics: information about currently active backends");

DATA(insertOID=2026 (pg_backend_pidPGNSPPGUID12100ffftfs0023""_null__null__null__null_pg_backend_pid_null__null__null_ ));

DESCR("statistics: current backend PID");

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, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);

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, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;

pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_port, s.application_nameFROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port, application_name), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));

pg_stat_activity | SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.application_name, s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_portFROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid,application_name,current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = 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_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, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, 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 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_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_alloc() AS buffers_alloc;