<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,

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_activity_start</function>(<type>integer</type>)</literal></entry>

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

CREATE VIEW pg_roles AS

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_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,

#include "postgres.h"

beentry->st_databaseid = MyDatabaseId;

beentry->st_userid = userid;

beentry->st_clientaddr = clientaddr;

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

/* Also make sure the last byte in the string area is always 0 */

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

}

#include "access/twophase.h"

#include "access/twophase_rmgr.h"

#include "miscadmin.h"

#include "storage/lmgr.h"

#include "utils/memutils.h"

#include "utils/ps_status.h"

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" */

}

awaitedLock = locallock;

awaitedOwner = owner;

awaitedLock = NULL;

if (update_process_title)

{

set_ps_display(new_status, false);

pfree(new_status);

}

LOCK_PRINT("WaitOnLock: wakeup on lock",

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

extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);

extern Datum pg_stat_get_backend_userid(PG_FUNCTION_ARGS);

extern Datum pg_stat_get_backend_activity(PG_FUNCTION_ARGS);

extern Datum pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS);

extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS);

extern Datum pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);

}

pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS)

{

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

DATA(insert OID = 1940 ( pg_stat_get_backend_activity PGNSP PGUID 12 f f t f s 1 25 "23" _null_ _null_ _null_ pg_stat_get_backend_activity - _null_ ));

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

DATA(insert OID = 2094 ( pg_stat_get_backend_activity_start PGNSP PGUID 12 f f t f s 1 1184 "23" _null_ _null_ _null_ pg_stat_get_backend_activity_start - _null_));

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

DATA(insert OID = 1391 ( pg_stat_get_backend_start PGNSP PGUID 12 f f t f s 1 1184 "23" _null_ _null_ _null_ pg_stat_get_backend_start - _null_));

#ifndef PGSTAT_H

Oid st_userid;

SockAddr st_clientaddr;

/* current command string; MUST be null-terminated */

char st_activity[PGBE_ACTIVITY_SIZE];

} PgBackendStatus;

extern void pgstat_bestart(void);

extern void pgstat_report_activity(const char *what);

extern void pgstat_initstats(PgStat_Info *stats, Relation rel);

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_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;