Index: src/backend/catalog/system_views.sql =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/backend/catalog/system_views.sql,v retrieving revision 1.60 diff -c -r1.60 system_views.sql *** src/backend/catalog/system_views.sql 7 Apr 2009 00:31:26 -0000 1.60 --- src/backend/catalog/system_views.sql 10 Jul 2009 17:38:08 -0000 *************** *** 356,362 **** pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(D.oid) AS tup_updated, ! pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted FROM pg_database D; CREATE VIEW pg_stat_user_functions AS --- 356,364 ---- pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(D.oid) AS tup_updated, ! pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted, ! pg_stat_get_db_lock_waits(D.oid) AS lock_waits, ! pg_stat_get_db_lock_wait_time(D.oid) AS lock_wait_time FROM pg_database D; CREATE VIEW pg_stat_user_functions AS *************** *** 403,408 **** --- 405,424 ---- REVOKE ALL on pg_user_mapping FROM public; + CREATE VIEW pg_stat_lock_waits AS + SELECT + L.field1, + L.field2, + L.field3, + L.field4, + L.typ, + L.method, + pg_stat_get_lock_waits( L.field1, L.field2, L.field3, L.field4, + L.typ, L.method) AS waits, + pg_stat_get_lock_wait_time( L.field1, L.field2, L.field3, L.field4, + L.typ, L.method) AS wait_time + FROM pg_stat_get_lock_wait_tagset() AS L; + -- -- We have a few function definitions in here, too. -- At some point there might be enough to justify breaking them out into Index: src/backend/postmaster/pgstat.c =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/backend/postmaster/pgstat.c,v retrieving revision 1.189 diff -c -r1.189 pgstat.c *** src/backend/postmaster/pgstat.c 11 Jun 2009 14:49:01 -0000 1.189 --- src/backend/postmaster/pgstat.c 11 Jul 2009 00:29:19 -0000 *************** *** 101,106 **** --- 101,107 ---- #define PGSTAT_DB_HASH_SIZE 16 #define PGSTAT_TAB_HASH_SIZE 512 #define PGSTAT_FUNCTION_HASH_SIZE 512 + #define PGSTAT_LOCK_HASH_SIZE 512 /* ---------- *************** *** 110,115 **** --- 111,117 ---- bool pgstat_track_activities = false; bool pgstat_track_counts = false; int pgstat_track_functions = TRACK_FUNC_OFF; + bool pgstat_track_locks = false; int pgstat_track_activity_query_size = 1024; /* ---------- *************** *** 173,178 **** --- 175,193 ---- static bool have_function_stats = false; /* + * Backends store per-lock info that's waiting to be sent to the collector + * in this hash table (indexed by lock LOCKTAG). + */ + static HTAB *pgStatLocks = NULL; + + /* + * Indicates if backend has some lock stats that it hasn't yet + * sent to the collector. + */ + static bool have_lock_stats = false; + + + /* * Tuple insertion/deletion counts for an open transaction can't be propagated * into PgStat_TableStatus counters until we know if it is going to commit * or abort. Hence, we keep these counts in per-subxact structs that live *************** *** 253,258 **** --- 268,274 ---- static void pgstat_send_tabstat(PgStat_MsgTabstat *tsmsg); static void pgstat_send_funcstats(void); + static void pgstat_send_lockstats(void); static HTAB *pgstat_collect_oids(Oid catalogid); static PgStat_TableStatus *get_tabstat_entry(Oid rel_id, bool isshared); *************** *** 273,278 **** --- 289,295 ---- static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len); static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len); static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len); + static void pgstat_recv_lockstat(PgStat_MsgLockstat *msg, int len); /* ------------------------------------------------------------ *************** *** 751,756 **** --- 768,777 ---- /* Now, send function statistics */ pgstat_send_funcstats(); + + /* Also send lock (wait) statistics */ + pgstat_send_lockstats(); + } /* *************** *** 847,852 **** --- 868,927 ---- } + /* + * Subroutine for pgstat_report_stat: populate and send a lock stat message + */ + static void + pgstat_send_lockstats(void) + { + /* we assume this inits to all zeroes: */ + static const PgStat_LockCounts all_zeroes; + + PgStat_MsgLockstat msg; + PgStat_BackendLockEntry *entry; + HASH_SEQ_STATUS fstat; + + if (pgStatLocks == NULL) + return; + pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_LOCKSTAT); + msg.m_databaseid = MyDatabaseId; + msg.m_nentries = 0; + + hash_seq_init(&fstat, pgStatLocks); + while ((entry = (PgStat_BackendLockEntry *) hash_seq_search(&fstat)) != NULL) + { + PgStat_LockEntry *m_ent; + + /* Skip it if no counts accumulated since last time */ + if (memcmp(&entry->l_counts, &all_zeroes, + sizeof(PgStat_LockCounts)) == 0) + continue; + + /* need to convert format of time accumulators */ + m_ent = &msg.m_entry[msg.m_nentries]; + memcpy(&m_ent->l_tag, &entry->l_tag, sizeof(LOCKTAG)); + m_ent->l_numwaits = entry->l_counts.l_numwaits; + m_ent->l_wait_time = INSTR_TIME_GET_MICROSEC(entry->l_counts.l_wait_time); + + if (++msg.m_nentries >= PGSTAT_NUM_LOCKENTRIES) + { + pgstat_send(&msg, offsetof(PgStat_MsgLockstat, m_entry[0]) + + msg.m_nentries * sizeof(PgStat_LockEntry)); + msg.m_nentries = 0; + } + + /* reset the entry's counts */ + MemSet(&entry->l_counts, 0, sizeof(PgStat_LockCounts)); + } + + if (msg.m_nentries > 0) + pgstat_send(&msg, offsetof(PgStat_MsgLockstat, m_entry[0]) + + msg.m_nentries * sizeof(PgStat_LockEntry)); + + have_lock_stats = false; + } + + /* ---------- * pgstat_vacuum_stat() - * *************** *** 1392,1397 **** --- 1467,1544 ---- } + /* + * Initialize lock wait tracking data. + * Called by the lock manager before entering lock wait. + */ + void + pgstat_init_lock_wait(LOCKTAG *locktag) + { + PgStat_BackendLockEntry *htabent; + bool found; + instr_time l_curr; + instr_time l_start; + + + if (!pgStatLocks) + { + /* First time through - initialize lock stat table */ + HASHCTL hash_ctl; + + memset(&hash_ctl, 0, sizeof(hash_ctl)); + hash_ctl.keysize = sizeof(LOCKTAG); + hash_ctl.entrysize = sizeof(PgStat_BackendLockEntry); + hash_ctl.hash = tag_hash; + pgStatLocks = hash_create("Lock stat entries", + PGSTAT_LOCK_HASH_SIZE, + &hash_ctl, + HASH_ELEM | HASH_FUNCTION); + } + + + /* Get the stats entry for this lock, create if necessary */ + htabent = hash_search(pgStatLocks, locktag, + HASH_ENTER, &found); + if (!found) + { + MemSet(&htabent->l_tag, 0, sizeof(LOCKTAG)); + MemSet(&htabent->l_counts, 0, sizeof(PgStat_LockCounts)); + memcpy(&htabent->l_tag, locktag, sizeof(LOCKTAG)); + } + + htabent->l_counts.l_numwaits++; + + l_curr = htabent->l_counts.l_wait_time; + INSTR_TIME_SET_CURRENT(l_start); + INSTR_TIME_ADD(l_curr, l_start); + htabent->l_counts.l_wait_time = l_curr; + } + + /* + * Calculate lock end wait data. + * Called by the lock manager after ending lock wait. + */ + void + pgstat_end_lock_wait(LOCKTAG *locktag) + { + PgStat_BackendLockEntry *htabent; + bool found; + instr_time l_start; + instr_time l_end; + + /* Get the stats entry for this lock */ + htabent = hash_search(pgStatLocks, locktag, + HASH_ENTER, &found); + Assert(found); + + l_start = htabent->l_counts.l_wait_time; + INSTR_TIME_SET_CURRENT(l_end); + INSTR_TIME_SUBTRACT(l_end, l_start); + htabent->l_counts.l_wait_time = l_end; + + } + + /* ---------- * pgstat_initstats() - * *************** *** 2010,2015 **** --- 2157,2191 ---- /* ---------- + * pgstat_fetch_stat_lockentry() - + * + * Support function for the SQL-callable pgstat* functions. Returns + * the collected statistics for one lock(tag) or NULL. + * ---------- + */ + PgStat_StatLockEntry * + pgstat_fetch_stat_lockentry(LOCKTAG *locktag) + { + PgStat_StatDBEntry *dbentry; + PgStat_StatLockEntry *lockentry = NULL; + + /* load the stats file if needed */ + backend_read_statsfile(); + + /* Lookup our database, then find the requested function. */ + dbentry = pgstat_fetch_stat_dbentry(MyDatabaseId); + if (dbentry != NULL && dbentry->locks != NULL) + { + lockentry = (PgStat_StatLockEntry *) hash_search(dbentry->locks, + (void *) locktag, + HASH_FIND, NULL); + } + + return lockentry; + } + + + /* ---------- * pgstat_fetch_stat_beentry() - * * Support function for the SQL-callable pgstat* functions. Returns *************** *** 2833,2838 **** --- 3009,3018 ---- pgstat_recv_funcpurge((PgStat_MsgFuncpurge *) &msg, len); break; + case PGSTAT_MTYPE_LOCKSTAT: + pgstat_recv_lockstat((PgStat_MsgLockstat *) &msg, len); + break; + default: break; } *************** *** 2899,2904 **** --- 3079,3085 ---- result->tables = NULL; result->functions = NULL; + result->locks = NULL; result->n_xact_commit = 0; result->n_xact_rollback = 0; result->n_blocks_fetched = 0; *************** *** 2909,2914 **** --- 3090,3097 ---- result->n_tuples_updated = 0; result->n_tuples_deleted = 0; result->last_autovac_time = 0; + result->n_lock_waits = 0; + result->lock_wait_time = 0; memset(&hash_ctl, 0, sizeof(hash_ctl)); hash_ctl.keysize = sizeof(Oid); *************** *** 2926,2931 **** --- 3109,3122 ---- PGSTAT_FUNCTION_HASH_SIZE, &hash_ctl, HASH_ELEM | HASH_FUNCTION); + + hash_ctl.keysize = sizeof(LOCKTAG); + hash_ctl.entrysize = sizeof(PgStat_StatLockEntry); + hash_ctl.hash = tag_hash; + result->locks = hash_create("Per-database Lock", + PGSTAT_LOCK_HASH_SIZE, + &hash_ctl, + HASH_ELEM | HASH_FUNCTION); } return result; *************** *** 2948,2956 **** --- 3139,3149 ---- HASH_SEQ_STATUS hstat; HASH_SEQ_STATUS tstat; HASH_SEQ_STATUS fstat; + HASH_SEQ_STATUS lstat; PgStat_StatDBEntry *dbentry; PgStat_StatTabEntry *tabentry; PgStat_StatFuncEntry *funcentry; + PgStat_StatLockEntry *lockentry; FILE *fpout; int32 format_id; const char *tmpfile = permanent ? PGSTAT_STAT_PERMANENT_TMPFILE : pgstat_stat_tmpname; *************** *** 3019,3025 **** --- 3212,3229 ---- fwrite(funcentry, sizeof(PgStat_StatFuncEntry), 1, fpout); } + /* + * Walk through the database's lock stats table. + */ + hash_seq_init(&lstat, dbentry->locks); + while ((lockentry = (PgStat_StatLockEntry *) hash_seq_search(&lstat)) != NULL) + { + fputc('L', fpout); + fwrite(lockentry, sizeof(PgStat_StatLockEntry), 1, fpout); + } + + /* * Mark the end of this DB */ fputc('d', fpout); *************** *** 3095,3104 **** --- 3299,3311 ---- PgStat_StatTabEntry tabbuf; PgStat_StatFuncEntry funcbuf; PgStat_StatFuncEntry *funcentry; + PgStat_StatLockEntry lockbuf; + PgStat_StatLockEntry *lockentry; HASHCTL hash_ctl; HTAB *dbhash; HTAB *tabhash = NULL; HTAB *funchash = NULL; + HTAB *lockhash = NULL; FILE *fpin; int32 format_id; bool found; *************** *** 3194,3199 **** --- 3401,3407 ---- memcpy(dbentry, &dbbuf, sizeof(PgStat_StatDBEntry)); dbentry->tables = NULL; dbentry->functions = NULL; + dbentry->locks = NULL; /* * Don't collect tables if not the requested DB (or the *************** *** 3224,3229 **** --- 3432,3446 ---- PGSTAT_FUNCTION_HASH_SIZE, &hash_ctl, HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT); + + hash_ctl.keysize = sizeof(LOCKTAG); + hash_ctl.entrysize = sizeof(PgStat_StatLockEntry); + hash_ctl.hash = tag_hash; + hash_ctl.hcxt = pgStatLocalContext; + dbentry->locks = hash_create("Per-database lock", + PGSTAT_LOCK_HASH_SIZE, + &hash_ctl, + HASH_ELEM | HASH_FUNCTION | HASH_CONTEXT); /* * Arrange that following records add entries to this *************** *** 3231,3236 **** --- 3448,3454 ---- */ tabhash = dbentry->tables; funchash = dbentry->functions; + lockhash = dbentry->locks; break; /* *************** *** 3239,3244 **** --- 3457,3463 ---- case 'd': tabhash = NULL; funchash = NULL; + lockhash = NULL; break; /* *************** *** 3306,3311 **** --- 3525,3562 ---- break; /* + * 'L' Lock wait entries + */ + case 'L': + if (fread(&lockbuf, 1, sizeof(PgStat_StatLockEntry), + fpin) != sizeof(PgStat_StatLockEntry)) + { + ereport(pgStatRunningInCollector ? LOG : WARNING, + (errmsg("corrupted pgstat.stat file"))); + goto done; + } + + /* + * Skip if lock belongs to a not requested database. + */ + if (lockhash == NULL) + break; + + lockentry = (PgStat_StatLockEntry *) hash_search(lockhash, + (void *) &lockbuf.l_tag, + HASH_ENTER, &found); + + if (found) + { + ereport(pgStatRunningInCollector ? LOG : WARNING, + (errmsg("corrupted pgstat.stat file"))); + goto done; + } + + memcpy(lockentry, &lockbuf, sizeof(lockbuf)); + break; + + /* * 'E' The EOF marker of a complete stats file. */ case 'E': *************** *** 3649,3654 **** --- 3900,3907 ---- hash_destroy(dbentry->tables); if (dbentry->functions != NULL) hash_destroy(dbentry->functions); + if (dbentry->locks != NULL) + hash_destroy(dbentry->locks); if (hash_search(pgStatDBHash, (void *) &(dbentry->databaseid), *************** *** 3688,3700 **** --- 3941,3958 ---- hash_destroy(dbentry->tables); if (dbentry->functions != NULL) hash_destroy(dbentry->functions); + if (dbentry->locks != NULL) + hash_destroy(dbentry->locks); dbentry->tables = NULL; dbentry->functions = NULL; + dbentry->locks = NULL; dbentry->n_xact_commit = 0; dbentry->n_xact_rollback = 0; dbentry->n_blocks_fetched = 0; dbentry->n_blocks_hit = 0; + dbentry->n_lock_waits = 0; + dbentry->lock_wait_time = 0; memset(&hash_ctl, 0, sizeof(hash_ctl)); hash_ctl.keysize = sizeof(Oid); *************** *** 3712,3717 **** --- 3970,3983 ---- PGSTAT_FUNCTION_HASH_SIZE, &hash_ctl, HASH_ELEM | HASH_FUNCTION); + + hash_ctl.keysize = sizeof(LOCKTAG); + hash_ctl.entrysize = sizeof(PgStat_StatLockEntry); + hash_ctl.hash = tag_hash; + dbentry->locks = hash_create("Per-database Lock", + PGSTAT_LOCK_HASH_SIZE, + &hash_ctl, + HASH_ELEM | HASH_FUNCTION); } /* ---------- *************** *** 3933,3935 **** --- 4199,4301 ---- HASH_REMOVE, NULL); } } + + /* ---------- + * pgstat_recv_lockstat() - + * + * Count what the backend has done. + * ---------- + */ + static void + pgstat_recv_lockstat(PgStat_MsgLockstat *msg, int len) + { + PgStat_LockEntry *lockmsg = &(msg->m_entry[0]); + PgStat_StatDBEntry *dbentry; + PgStat_StatLockEntry *lockentry; + int i; + bool found; + + dbentry = pgstat_get_db_entry(msg->m_databaseid, true); + Assert(dbentry->locks); + + /* + * Process all lock entries in the message. + */ + for (i = 0; i < msg->m_nentries; i++, lockmsg++) + { + lockentry = (PgStat_StatLockEntry *) hash_search(dbentry->locks, + (void *) &(lockmsg->l_tag), + HASH_ENTER, &found); + + if (!found) + { + /* + * If it's a new lock entry, initialize counters to the values + * we just got. + */ + lockentry->l_numwaits = lockmsg->l_numwaits; + lockentry->l_wait_time = lockmsg->l_wait_time; + } + else + { + /* + * Otherwise add the values to the existing entry. + */ + lockentry->l_numwaits += lockmsg->l_numwaits; + lockentry->l_wait_time += lockmsg->l_wait_time; + } + + /* update the lock wait totals and time for this database */ + dbentry->n_lock_waits += lockmsg->l_numwaits; + dbentry->lock_wait_time += lockmsg->l_wait_time; + } + } + + /* + * Scan the collected locktags and return them for use by user callable stats + * functions. + */ + PgStat_LockWaitTags * + pgstat_get_lock_wait_tags(void) + { + + PgStat_StatDBEntry *dbentry; + PgStat_LockWaitTags *waitTags; + HASH_SEQ_STATUS lstat; + PgStat_StatLockEntry *lockentry; + LOCKTAG locktag; + int numtags; + int i = 0; + + waitTags = (PgStat_LockWaitTags *) palloc(sizeof(PgStat_LockWaitTags)); + + /* load the stats file if needed */ + backend_read_statsfile(); + + /* get the database entry for the db and count its lock hash */ + dbentry = pgstat_fetch_stat_dbentry(MyDatabaseId); + if (dbentry == NULL || dbentry->locks == NULL) + { + waitTags->n_tags = 0; + waitTags->locktags = NULL; + } + else + { + numtags = hash_get_num_entries(dbentry->locks); + + waitTags->n_tags = numtags; + waitTags->locktags = (LOCKTAG *) palloc(sizeof(LOCKTAG) * numtags); + + /* scan the lock hash and copy the locktags */ + hash_seq_init(&lstat, dbentry->locks); + while ((lockentry = (PgStat_StatLockEntry *) hash_seq_search(&lstat)) != NULL) + { + locktag = lockentry->l_tag; + memcpy(&(waitTags->locktags[i]), &locktag, sizeof(LOCKTAG)); + i++; + } + } + + return waitTags; + } + Index: src/backend/storage/lmgr/lock.c =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/backend/storage/lmgr/lock.c,v retrieving revision 1.188 diff -c -r1.188 lock.c *** src/backend/storage/lmgr/lock.c 11 Jun 2009 14:49:02 -0000 1.188 --- src/backend/storage/lmgr/lock.c 10 Jul 2009 20:07:46 -0000 *************** *** 793,801 **** --- 793,805 ---- locktag->locktag_field4, locktag->locktag_type, lockmode); + if (pgstat_track_locks) + pgstat_init_lock_wait (locktag); WaitOnLock(locallock, owner); + if (pgstat_track_locks) + pgstat_end_lock_wait (locktag); TRACE_POSTGRESQL_LOCK_WAIT_DONE(locktag->locktag_field1, locktag->locktag_field2, locktag->locktag_field3, Index: src/backend/storage/lmgr/proc.c =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/backend/storage/lmgr/proc.c,v retrieving revision 1.207 diff -c -r1.207 proc.c *** src/backend/storage/lmgr/proc.c 11 Jun 2009 14:49:02 -0000 1.207 --- src/backend/storage/lmgr/proc.c 10 Jul 2009 20:16:47 -0000 *************** *** 38,43 **** --- 38,44 ---- #include "access/transam.h" #include "access/xact.h" #include "miscadmin.h" + #include "pgstat.h" #include "postmaster/autovacuum.h" #include "storage/ipc.h" #include "storage/lmgr.h" *************** *** 528,533 **** --- 529,538 ---- GrantAwaitedLock(); } + /* If tracking lock waits, do necessary arithmetic here */ + if (pgstat_track_locks) + pgstat_end_lock_wait (&lockAwaited->tag.lock); + lockAwaited = NULL; LWLockRelease(partitionLock); Index: src/backend/utils/adt/pgstatfuncs.c =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/backend/utils/adt/pgstatfuncs.c,v retrieving revision 1.54 diff -c -r1.54 pgstatfuncs.c *** src/backend/utils/adt/pgstatfuncs.c 11 Jun 2009 14:49:04 -0000 1.54 --- src/backend/utils/adt/pgstatfuncs.c 10 Jul 2009 20:42:09 -0000 *************** *** 67,72 **** --- 67,74 ---- extern Datum pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS); extern Datum pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS); + extern Datum pg_stat_get_db_lock_waits(PG_FUNCTION_ARGS); + extern Datum pg_stat_get_db_lock_wait_time(PG_FUNCTION_ARGS); extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS); extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS); *************** *** 78,87 **** --- 80,99 ---- extern Datum pg_stat_clear_snapshot(PG_FUNCTION_ARGS); extern Datum pg_stat_reset(PG_FUNCTION_ARGS); + extern Datum pg_stat_get_lock_wait_tagset(PG_FUNCTION_ARGS); + extern Datum pg_stat_get_lock_waits(PG_FUNCTION_ARGS); + extern Datum pg_stat_get_lock_wait_time(PG_FUNCTION_ARGS); /* Global bgwriter statistics, from bgwriter.c */ extern PgStat_MsgBgWriter bgwriterStats; + /* Working status for pg_stat_get_lock_wait_tagset */ + typedef struct + { + PgStat_LockWaitTags *waitTags; + int currIdx; + } PgStat_LockWaitTags_State; + Datum pg_stat_get_numscans(PG_FUNCTION_ARGS) { *************** *** 1039,1044 **** --- 1051,1086 ---- } Datum + pg_stat_get_db_lock_waits(PG_FUNCTION_ARGS) + { + Oid dbid = PG_GETARG_OID(0); + int64 result; + PgStat_StatDBEntry *dbentry; + + if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL) + result = 0; + else + result = (int64) (dbentry->n_lock_waits); + + PG_RETURN_INT64(result); + } + + Datum + pg_stat_get_db_lock_wait_time(PG_FUNCTION_ARGS) + { + Oid dbid = PG_GETARG_OID(0); + int64 result; + PgStat_StatDBEntry *dbentry; + + if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL) + result = 0; + else + result = (int64) (dbentry->lock_wait_time); + + PG_RETURN_INT64(result); + } + + Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS) { PG_RETURN_INT64(pgstat_fetch_global()->timed_checkpoints); *************** *** 1099,1101 **** --- 1141,1270 ---- PG_RETURN_VOID(); } + + + /* Get the lock "tags" for captured lock waits */ + Datum + pg_stat_get_lock_wait_tagset(PG_FUNCTION_ARGS) + { + FuncCallContext *funcctx; + PgStat_LockWaitTags *waitTags; + PgStat_LockWaitTags_State *state; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + TupleDesc tupdesc; + MemoryContext oldcontext; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + /* make a tupdesc */ + tupdesc = CreateTemplateTupleDesc(6, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "field1", + INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "field2", + INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 3, "field3", + INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "field4", + INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "typ", + INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "method", + INT4OID, -1, 0); + funcctx->tuple_desc = BlessTupleDesc(tupdesc); + + state = (PgStat_LockWaitTags_State *) palloc(sizeof(PgStat_LockWaitTags_State)); + funcctx->user_fctx = (void *) state; + + state->waitTags = pgstat_get_lock_wait_tags(); + state->currIdx = 0; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff performed for each call */ + funcctx = SRF_PERCALL_SETUP(); + state = (PgStat_LockWaitTags_State *) funcctx->user_fctx; + waitTags = state->waitTags; + + while (state->currIdx < waitTags->n_tags) + { + LOCKTAG *locktag; + Datum values[6]; + bool nulls[6]; + HeapTuple tuple; + Datum result; + + locktag = &(waitTags->locktags[state->currIdx]); + + /* setup values for use in a tuple */ + MemSet(values, 0, sizeof(values)); + MemSet(nulls, false, sizeof(nulls)); + + /* read all the values from the locktag */ + values[0] = UInt32GetDatum(locktag->locktag_field1); + values[1] = UInt32GetDatum(locktag->locktag_field2); + values[2] = UInt32GetDatum(locktag->locktag_field3); + values[3] = UInt16GetDatum(locktag->locktag_field4); + values[4] = UInt8GetDatum(locktag->locktag_type); + values[5] = UInt8GetDatum(locktag->locktag_lockmethodid); + + state->currIdx++; + + /* form the tuple */ + tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls); + result = HeapTupleGetDatum(tuple); + SRF_RETURN_NEXT(funcctx, result); + } + + SRF_RETURN_DONE(funcctx); + } + + Datum + pg_stat_get_lock_waits(PG_FUNCTION_ARGS) + { + LOCKTAG locktag; + int64 result; + PgStat_StatLockEntry *lockentry; + + locktag.locktag_field1 = PG_GETARG_INT32(0); + locktag.locktag_field2 = PG_GETARG_INT32(1); + locktag.locktag_field3 = PG_GETARG_INT32(2); + locktag.locktag_field4 = PG_GETARG_INT32(3); + locktag.locktag_type = PG_GETARG_INT32(4); + locktag.locktag_lockmethodid = PG_GETARG_INT32(5); + + if ((lockentry = pgstat_fetch_stat_lockentry(&locktag)) == NULL) + result = 0; + else + result = (int64) (lockentry->l_numwaits); + + PG_RETURN_INT64(result); + } + + Datum + pg_stat_get_lock_wait_time(PG_FUNCTION_ARGS) + { + LOCKTAG locktag; + int64 result; + PgStat_StatLockEntry *lockentry; + + locktag.locktag_field1 = PG_GETARG_INT32(0); + locktag.locktag_field2 = PG_GETARG_INT32(1); + locktag.locktag_field3 = PG_GETARG_INT32(2); + locktag.locktag_field4 = PG_GETARG_INT32(3); + locktag.locktag_type = PG_GETARG_INT32(4); + locktag.locktag_lockmethodid = PG_GETARG_INT32(5); + + if ((lockentry = pgstat_fetch_stat_lockentry(&locktag)) == NULL) + result = 0; + else + result = (int64) (lockentry->l_wait_time); + + PG_RETURN_INT64(result); + } Index: src/backend/utils/misc/guc.c =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/backend/utils/misc/guc.c,v retrieving revision 1.505 diff -c -r1.505 guc.c *** src/backend/utils/misc/guc.c 11 Jun 2009 14:49:06 -0000 1.505 --- src/backend/utils/misc/guc.c 10 Jul 2009 21:51:32 -0000 *************** *** 885,890 **** --- 885,898 ---- &pgstat_track_counts, true, NULL, NULL }, + { + {"track_locks", PGC_SUSET, STATS_COLLECTOR, + gettext_noop("Collects statistics on lock activity."), + NULL + }, + &pgstat_track_locks, + false, NULL, NULL + }, { {"update_process_title", PGC_SUSET, STATS_COLLECTOR, Index: src/backend/utils/misc/postgresql.conf.sample =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/backend/utils/misc/postgresql.conf.sample,v retrieving revision 1.261 diff -c -r1.261 postgresql.conf.sample *** src/backend/utils/misc/postgresql.conf.sample 3 Jul 2009 19:14:25 -0000 1.261 --- src/backend/utils/misc/postgresql.conf.sample 10 Jul 2009 21:52:12 -0000 *************** *** 365,370 **** --- 365,371 ---- #track_activities = on #track_counts = on #track_functions = none # none, pl, all + #track_locks = off #track_activity_query_size = 1024 #update_process_title = on #stats_temp_directory = 'pg_stat_tmp' Index: src/include/pgstat.h =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/include/pgstat.h,v retrieving revision 1.83 diff -c -r1.83 pgstat.h *** src/include/pgstat.h 11 Jun 2009 14:49:08 -0000 1.83 --- src/include/pgstat.h 11 Jul 2009 00:07:05 -0000 *************** *** 13,18 **** --- 13,19 ---- #include "libpq/pqcomm.h" #include "portability/instr_time.h" + #include "storage/lock.h" #include "utils/hsearch.h" #include "utils/relcache.h" #include "utils/timestamp.h" *************** *** 43,49 **** PGSTAT_MTYPE_ANALYZE, PGSTAT_MTYPE_BGWRITER, PGSTAT_MTYPE_FUNCSTAT, ! PGSTAT_MTYPE_FUNCPURGE } StatMsgType; /* ---------- --- 44,51 ---- PGSTAT_MTYPE_ANALYZE, PGSTAT_MTYPE_BGWRITER, PGSTAT_MTYPE_FUNCSTAT, ! PGSTAT_MTYPE_FUNCPURGE, ! PGSTAT_MTYPE_LOCKSTAT } StatMsgType; /* ---------- *************** *** 398,403 **** --- 400,459 ---- Oid m_functionid[PGSTAT_NUM_FUNCPURGE]; } PgStat_MsgFuncpurge; + /* ---------- + * PgStat_LockCounts The actual per-lock type counts kept by a backend + * + * This struct should contain only actual event counters, because we memcmp + * it against zeroes to detect whether there are any counts to transmit. + * + * Note that the time counters are in instr_time format here. We convert to + * microseconds in PgStat_Counter format when transmitting to the collector. + * ---------- + */ + typedef struct PgStat_LockCounts + { + PgStat_Counter l_numwaits; + instr_time l_wait_time; + } PgStat_LockCounts; + + /* ---------- + * PgStat_BackendLockEntry Per-Lock info in a backend + * ---------- + */ + typedef struct PgStat_BackendLockEntry + { + LOCKTAG l_tag; /* LOCKTAG for this lock */ + PgStat_LockCounts l_counts; + } PgStat_BackendLockEntry; + + /* ---------- + * PgStat_LockEntry Per-Lock info in a MsgLockstat + * ---------- + */ + typedef struct PgStat_LockEntry + { + LOCKTAG l_tag; /* LOCKTAG for this lock */ + PgStat_Counter l_numwaits; + PgStat_Counter l_wait_time; + } PgStat_LockEntry; + + /* ---------- + * PgStat_MsgLockstat Sent by the backend to report lock usage + * statistics. + * ---------- + */ + #define PGSTAT_NUM_LOCKENTRIES \ + ((PGSTAT_MSG_PAYLOAD - sizeof(Oid) - sizeof(int)) \ + / sizeof(PgStat_LockEntry)) + + typedef struct PgStat_MsgLockstat + { + PgStat_MsgHdr m_hdr; + Oid m_databaseid; + int m_nentries; + PgStat_LockEntry m_entry[PGSTAT_NUM_LOCKENTRIES]; + } PgStat_MsgLockstat; + /* ---------- * PgStat_Msg Union over all possible messages. *************** *** 418,423 **** --- 474,480 ---- PgStat_MsgBgWriter msg_bgwriter; PgStat_MsgFuncstat msg_funcstat; PgStat_MsgFuncpurge msg_funcpurge; + PgStat_MsgLockstat msg_lockstat; } PgStat_Msg; *************** *** 429,435 **** * ------------------------------------------------------------ */ ! #define PGSTAT_FILE_FORMAT_ID 0x01A5BC98 /* ---------- * PgStat_StatDBEntry The collector's data per database --- 486,492 ---- * ------------------------------------------------------------ */ ! #define PGSTAT_FILE_FORMAT_ID 0x01A5BC99 /* ---------- * PgStat_StatDBEntry The collector's data per database *************** *** 448,453 **** --- 505,512 ---- PgStat_Counter n_tuples_updated; PgStat_Counter n_tuples_deleted; TimestampTz last_autovac_time; + PgStat_Counter n_lock_waits; + PgStat_Counter lock_wait_time; /* * tables and functions must be last in the struct, because we don't write *************** *** 455,460 **** --- 514,520 ---- */ HTAB *tables; HTAB *functions; + HTAB *locks; } PgStat_StatDBEntry; *************** *** 505,510 **** --- 565,583 ---- } PgStat_StatFuncEntry; + /* ---------- + * PgStat_StatFuncEntry The collector's data per function + * ---------- + */ + typedef struct PgStat_StatLockEntry + { + LOCKTAG l_tag; + + PgStat_Counter l_numwaits; + PgStat_Counter l_wait_time; /* times in microseconds */ + } PgStat_StatLockEntry; + + /* * Global statistics kept in the stats collector */ *************** *** 584,589 **** --- 657,671 ---- instr_time f_start; } PgStat_FunctionCallUsage; + /* + * Structure to hold information passed to the lock wait user level functions, + * For each collected locktag an entry is stored. + */ + typedef struct PgStat_LockWaitTags + { + int n_tags; /* the number of tags in the array */ + LOCKTAG *locktags; /* the tags */ + } PgStat_LockWaitTags; /* ---------- * GUC parameters *************** *** 592,597 **** --- 674,680 ---- extern bool pgstat_track_activities; extern bool pgstat_track_counts; extern int pgstat_track_functions; + extern bool pgstat_track_locks; extern PGDLLIMPORT int pgstat_track_activity_query_size; extern char *pgstat_stat_tmpname; extern char *pgstat_stat_filename; *************** *** 696,701 **** --- 779,788 ---- extern void pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, bool finalize); + extern void pgstat_init_lock_wait(LOCKTAG *locktag); + extern void pgstat_end_lock_wait(LOCKTAG *locktag); + extern PgStat_LockWaitTags *pgstat_get_lock_wait_tags(void); + extern void AtEOXact_PgStat(bool isCommit); extern void AtEOSubXact_PgStat(bool isCommit, int nestDepth); *************** *** 718,723 **** --- 805,811 ---- extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid); extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid); extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid); + extern PgStat_StatLockEntry *pgstat_fetch_stat_lockentry(LOCKTAG *locktag); extern int pgstat_fetch_stat_numbackends(void); extern PgStat_GlobalStats *pgstat_fetch_global(void); Index: src/include/catalog/catversion.h =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/include/catalog/catversion.h,v retrieving revision 1.532 diff -c -r1.532 catversion.h *** src/include/catalog/catversion.h 7 Jul 2009 18:23:14 -0000 1.532 --- src/include/catalog/catversion.h 11 Jul 2009 00:08:58 -0000 *************** *** 53,58 **** */ /* yyyymmddN */ ! #define CATALOG_VERSION_NO 200907071 #endif --- 53,58 ---- */ /* yyyymmddN */ ! #define CATALOG_VERSION_NO 200907101 #endif Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/include/catalog/pg_proc.h,v retrieving revision 1.546 diff -c -r1.546 pg_proc.h *** src/include/catalog/pg_proc.h 7 Jul 2009 18:49:16 -0000 1.546 --- src/include/catalog/pg_proc.h 11 Jul 2009 00:13:37 -0000 *************** *** 3028,3033 **** --- 3028,3037 ---- DESCR("statistics: tuples updated in database"); DATA(insert OID = 2762 ( pg_stat_get_db_tuples_deleted PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_tuples_deleted _null_ _null_ _null_ )); DESCR("statistics: tuples deleted in database"); + DATA(insert OID = 2964 ( pg_stat_get_db_lock_waits PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_lock_waits _null_ _null_ _null_ )); + DESCR("statistics: lock waits in database"); + DATA(insert OID = 2965 ( pg_stat_get_db_lock_wait_time PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_lock_wait_time _null_ _null_ _null_ )); + DESCR("statistics: lock wait time in database"); DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ )); DESCR("statistics: number of timed checkpoints started by the bgwriter"); DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ )); *************** *** 3049,3055 **** DESCR("statistics: execution time of function"); DATA(insert OID = 2980 ( pg_stat_get_function_self_time PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_function_self_time _null_ _null_ _null_ )); DESCR("statistics: self execution time of function"); ! DATA(insert OID = 2230 ( pg_stat_clear_snapshot PGNSP PGUID 12 1 0 0 f f f f f v 0 0 2278 "" _null_ _null_ _null_ _null_ pg_stat_clear_snapshot _null_ _null_ _null_ )); DESCR("statistics: discard current transaction's statistics snapshot"); DATA(insert OID = 2274 ( pg_stat_reset PGNSP PGUID 12 1 0 0 f f f f f v 0 0 2278 "" _null_ _null_ _null_ _null_ pg_stat_reset _null_ _null_ _null_ )); --- 3053,3064 ---- DESCR("statistics: execution time of function"); DATA(insert OID = 2980 ( pg_stat_get_function_self_time PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_function_self_time _null_ _null_ _null_ )); DESCR("statistics: self execution time of function"); ! DATA(insert OID = 2966 ( pg_stat_get_lock_wait_tagset PGNSP PGUID 12 1 1000 0 f f f t t v 0 0 2249 "" "{23,23,23,23,23,23}" "{o,o,o,o,o,o}" "{field1,field2,field3,field4,typ,method}" _null_ pg_stat_get_lock_wait_tagset _null_ _null_ _null_ )); ! DESCR("statistics: get locktags for lock waiters"); ! DATA(insert OID = 2967 ( pg_stat_get_lock_waits PGNSP PGUID 12 1 0 0 f f f t f s 6 0 20 "23 23 23 23 23 23" _null_ _null_ _null_ _null_ pg_stat_get_lock_waits _null_ _null_ _null_ )); ! DESCR("statistics: number of lock waits"); ! DATA(insert OID = 2995 ( pg_stat_get_lock_wait_time PGNSP PGUID 12 1 0 0 f f f t f s 6 0 20 "23 23 23 23 23 23" _null_ _null_ _null_ _null_ pg_stat_get_lock_wait_time _null_ _null_ _null_ )); ! DESCR("statistics: lock wait time"); DATA(insert OID = 2230 ( pg_stat_clear_snapshot PGNSP PGUID 12 1 0 0 f f f f f v 0 0 2278 "" _null_ _null_ _null_ _null_ pg_stat_clear_snapshot _null_ _null_ _null_ )); DESCR("statistics: discard current transaction's statistics snapshot"); DATA(insert OID = 2274 ( pg_stat_reset PGNSP PGUID 12 1 0 0 f f f f f v 0 0 2278 "" _null_ _null_ _null_ _null_ pg_stat_reset _null_ _null_ _null_ )); Index: src/test/regress/expected/rules.out =================================================================== RCS file: /home/postgres/pgrepo/pgsql/src/test/regress/expected/rules.out,v retrieving revision 1.149 diff -c -r1.149 rules.out *** src/test/regress/expected/rules.out 6 Feb 2009 21:15:12 -0000 1.149 --- src/test/regress/expected/rules.out 11 Jul 2009 00:20:42 -0000 *************** *** 1293,1299 **** 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; ! 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, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted FROM pg_database d; pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, (pg_stat_get_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); --- 1293,1300 ---- 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; ! 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, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_lock_waits(d.oid) AS lock_waits, pg_stat_get_db_lock_wait_time(d.oid) AS lock_wait_time FROM pg_database d; ! pg_stat_lock_waits | SELECT l.field1, l.field2, l.field3, l.field4, l.typ, l.method, pg_stat_get_lock_waits(l.field1, l.field2, l.field3, l.field4, l.typ, l.method) AS waits, pg_stat_get_lock_wait_time(l.field1, l.field2, l.field3, l.field4, l.typ, l.method) AS wait_time FROM pg_stat_get_lock_wait_tagset() l(field1, field2, field3, field4, typ, method); pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, (pg_stat_get_function_time(p.oid) / 1000) AS total_time, (pg_stat_get_function_self_time(p.oid) / 1000) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); *************** *** 1329,1335 **** shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color)))); street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); toyemp | SELECT emp.name, emp.age, emp.location, (12 * emp.salary) AS annualsal FROM emp; ! (51 rows) SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; --- 1330,1336 ---- shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color)))); street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); toyemp | SELECT emp.name, emp.age, emp.location, (12 * emp.salary) AS annualsal FROM emp; ! (52 rows) SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename;