[ Newest version of patch applied.]

This patch is an updated version of the lock listing patch. I've made
the following changes:

    - write documentation
    - wrap the SRF in a view called 'pg_locks': all user-level
      access should be done through this view
    - re-diff against latest CVS

One thing I chose not to do is adapt the SRF to use the anonymous
composite type code from Joe Conway. I'll probably do that eventually,
but I'm not really convinced it's a significantly cleaner way to
bootstrap SRF builtins than the method this patch uses (of course, it
has other uses...)

Neil Conway

11 files changed, 248 insertions, 20 deletions

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 393b832ad40..1b063129fc7 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.10 2002/08/04 19:51:30 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.11 2002/08/17 13:04:14 momjian Exp $
 -->
 
 <chapter id="monitoring">
@@ -543,6 +543,134 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S;
 
  </sect2>
  </sect1>
+
+  <sect1 id="monitoring-locks">
+   <title>Viewing Locks</title>
+
+   <para>
+	Another useful tool for monitoring database activity is the
+	<literal>pg_locks</literal> system catalog. This allows the
+	database administrator to view information about the outstanding
+	locks in the lock manager. For example, this capability can be
+	used to:
+	<itemizedlist>
+	 <listitem>
+	  <para>
+	   View all the locks currently outstanding, all the locks on
+	   relations in a particular database, all the locks on a
+	   particular relation, or all the locks held by a particular
+	   <productname>PostgreSQL</productname> backend.
+	  </para>
+	 </listitem>
+	 <listitem>
+	  <para>
+	   View the relation in the current database with the most
+	   un-granted locks (which might be a source of contention among
+	   database clients).
+	  </para>
+	 </listitem>
+	 <listitem>
+	  <para>
+	   Determine the effect of lock contention on overall database
+	   performance, as well as the extent to which contention varies
+	   with overall database traffic.
+	  </para>
+	 </listitem>
+	</itemizedlist>
+
+	For more information on locking and managing concurrency with
+	<productname>PostgreSQL</productname>, refer to the
+	<citetitle>Administrator's Guide</citetitle>.
+   </para>
+
+   <note>
+	<para>
+	 When the <literal>pg_locks</literal> view is accessed, an
+	 exclusive lock on an internal lock manager data structure must be
+	 acquired to ensure that the data produced by the view is
+	 consistent. The lock held on this structure conflicts with normal
+	 database operations, and can therefore have an effect on overall
+	 database performance. Nevertheless, the performance impact of
+	 accessing this view should be minimal in most situations.
+	</para>
+   </note>
+
+   <para>
+	The <literal>pg_locks</literal> view contains one row per
+	lock. This means that if there are multiple locks on a single
+	relation (which may or may not conflict with one another), a
+	single relation may show up many times. Furthermore, only
+	table-level locks are displayed (not row-level ones).
+   </para>
+
+   <table>
+	<title>Lock Status System View</title>
+
+	<tgroup cols="3">
+	 <thead>
+	  <row>
+	   <entry>Column Name</entry>
+	   <entry>Type</entry>
+	   <entry>Description</entry>
+	  </row>
+	 </thead>
+
+	 <tbody>
+	  <row>
+	   <entry><structfield>relation</structfield></entry>
+	   <entry><type>oid</type></entry>
+	   <entry>The OID of the locked relation. When querying
+	   <literal>pg_locks</literal>, this column can be joined with the
+	   <literal>pg_class</literal> system catalog to get more
+	   information on the locked relation.</entry>
+	  </row>
+
+	  <row>
+	   <entry><structfield>database</structfield></entry>
+	   <entry><type>oid</type></entry>
+	   <entry>The OID of the database in which the locked relation
+		exists. If the lock is on a globally-shared object, this value
+		will be 0. When querying <literal>pg_locks</literal>, this
+		column can be joined with the <literal>pg_database</literal>
+		system catalog to get more information on the locked object's
+		database.</entry>
+	  </row>
+
+	  <row>
+	   <entry><structfield>backendpid</structfield></entry>
+	   <entry><type>int4</type></entry>
+	   <entry>The process ID of the
+		<productname>PostgreSQL</productname> backend that has
+		acquired or is attempting to acquire the lock. If you have
+		enabled the statistics collector, this column can be joined
+		with the <literal>pg_stat_activity</literal> view to access
+		more information on the backend holding or waiting to hold the
+		lock.</entry>
+	  </row>
+
+	  <row>
+	   <entry><structfield>mode</structfield></entry>
+	   <entry><type>text</type></entry>
+	   <entry>The mode of the lock. For more information on the
+	   different lock modes available in
+	   <productname>PostgreSQL</productname>, refer to the
+	   <citetitle>User's Guide</citetitle>.</entry>
+	  </row>
+
+	  <row>
+	   <entry><structfield>isgranted</structfield></entry>
+	   <entry><type>text</type></entry>
+	   <entry>A boolean column indicating whether or not this
+	   particular lock has been granted. If the lock has not been
+	   granted, the backend atempting to acquire it will sleep until
+	   the lock is released (or a deadlock situation is detected). A
+	   single backend can be waiting to acquire at most one lock at
+	   any given time.</entry>
+	  </row>
+	 </tbody>
+	</tgroup>
+   </table>
+  </sect1>
 </chapter>
 
 <!-- Keep this comment at the end of the file
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index d6c59fd665b..0a7f2f8fa94 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.26 2002/08/05 19:43:31 petere Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.27 2002/08/17 13:04:14 momjian Exp $
 -->
 
  <chapter id="mvcc">
@@ -408,7 +408,7 @@ ERROR:  Can't serialize access due to concurrent update
     modes on the same table at the same time.  (However, a transaction
     never conflicts with itself --- for example, it may acquire
     <literal>ACCESS EXCLUSIVE</literal> lock and later acquire
-    <literal>ACCESS SHARE</literal> lock on the same table.)  Nonconflicting
+    <literal>ACCESS SHARE</literal> lock on the same table.)  Non-conflicting
     lock modes may be held concurrently by many transactions.  Notice in
     particular that some lock modes are self-conflicting (for example,
     <literal>ACCESS EXCLUSIVE</literal> cannot be held by more than one
@@ -417,6 +417,14 @@ ERROR:  Can't serialize access due to concurrent update
     Once acquired, a lock mode is held till end of transaction.
    </para>
 
+	<para>
+	 To examine a list of the currently outstanding locks in a
+	 database server, use the <literal>pg_locks</literal> system
+	 view. For more information on monitoring the status of the lock
+	 manager subsystem, refer to the <citetitle>Administrator's
+	 Guide</citetitle>.
+	</para>
+
      <variablelist>
       <title>Table-level lock modes</title>
       <varlistentry>
diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c
index d6858da7e33..154c49b3e92 100644
--- a/src/backend/storage/lmgr/lock.c
+++ b/src/backend/storage/lmgr/lock.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/storage/lmgr/lock.c,v 1.111 2002/08/01 05:18:33 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/storage/lmgr/lock.c,v 1.112 2002/08/17 13:04:14 momjian Exp $
  *
  * NOTES
  *	  Outside modules can create a lock table and acquire/release
@@ -1359,6 +1359,66 @@ LockShmemSize(int maxBackends)
 	return size;
 }
 
+/*
+ * GetLockStatusData - Return a summary of the lock manager's internal
+ * status, for use in a user-level statistical reporting function.
+ *
+ * This function should be passed a pointer to a LockData struct. It fills
+ * the structure with the appropriate information and returns. The goal
+ * is to hold the LockMgrLock for as short a time as possible; thus, the
+ * function simply makes a copy of the necessary data and releases the
+ * lock, allowing the caller to contemplate and format the data for
+ * as long as it pleases.
+ */
+void
+GetLockStatusData(LockData *data)
+{
+	HTAB		*holderTable;
+	PROCLOCK	*holder;
+	HASH_SEQ_STATUS seqstat;
+	int i = 0;
+
+	data->currIdx = 0;
+
+	LWLockAcquire(LockMgrLock, LW_EXCLUSIVE);
+
+	holderTable = LockMethodTable[DEFAULT_LOCKMETHOD]->holderHash;
+
+	data->nelements = holderTable->hctl->nentries;
+
+	data->procs = (PGPROC *) palloc(sizeof(PGPROC) * data->nelements);
+	data->locks = (LOCK *) palloc(sizeof(LOCK) * data->nelements);
+	data->holders = (PROCLOCK *) palloc(sizeof(PROCLOCK) * data->nelements);
+
+	hash_seq_init(&seqstat, holderTable);
+
+	while ( (holder = hash_seq_search(&seqstat)) )
+	{
+		PGPROC	*proc;
+		LOCK	*lock;
+
+		/* Only do a shallow copy */
+		proc = (PGPROC *) MAKE_PTR(holder->tag.proc);
+		lock = (LOCK *) MAKE_PTR(holder->tag.lock);
+
+		memcpy(&(data->procs[i]), proc, sizeof(PGPROC));
+		memcpy(&(data->locks[i]), lock, sizeof(LOCK));
+		memcpy(&(data->holders[i]), holder, sizeof(PROCLOCK));
+
+		i++;
+	}
+
+	Assert(i == data->nelements);
+
+	LWLockRelease(LockMgrLock);
+}
+
+char *
+GetLockmodeName(LOCKMODE mode)
+{
+	Assert(mode <= MAX_LOCKMODES);
+	return lock_mode_names[mode];
+}
 
 #ifdef LOCK_DEBUG
 /*
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index ded117dbac8..1ae0a89fd6b 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.171 2002/08/17 12:15:49 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.172 2002/08/17 13:04:15 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -239,8 +239,7 @@ ProcessUtility(Node *parsetree,
 			break;
 
 			/*
-			 * ******************************** portal manipulation ********************************
-			 *
+			 * ************************* portal manipulation ***************************
 			 */
 		case T_ClosePortalStmt:
 			{
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index f219c5bde08..3cbd1a8fa58 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -1,7 +1,7 @@
 #
 # Makefile for utils/adt
 #
-# $Header: /cvsroot/pgsql/src/backend/utils/adt/Makefile,v 1.51 2001/10/04 04:13:40 ishii Exp $
+# $Header: /cvsroot/pgsql/src/backend/utils/adt/Makefile,v 1.52 2002/08/17 13:04:15 momjian Exp $
 #
 
 subdir = src/backend/utils/adt
@@ -17,7 +17,7 @@ endif
 
 OBJS = acl.o arrayfuncs.o arrayutils.o bool.o cash.o char.o \
 	date.o datetime.o datum.o float.o format_type.o \
-	geo_ops.o geo_selfuncs.o int.o int8.o like.o \
+	geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \
 	misc.o nabstime.o name.o not_in.o numeric.o numutils.o \
 	oid.o oracle_compat.o \
 	regexp.o regproc.o ruleutils.o selfuncs.o sets.o \
diff --git a/src/bin/initdb/initdb.sh b/src/bin/initdb/initdb.sh
index d876a888a20..37249c1f79c 100644
--- a/src/bin/initdb/initdb.sh
+++ b/src/bin/initdb/initdb.sh
@@ -27,7 +27,7 @@
 # Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
 # Portions Copyright (c) 1994, Regents of the University of California
 #
-# $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.166 2002/08/15 02:51:26 momjian Exp $
+# $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.167 2002/08/17 13:04:15 momjian Exp $
 #
 #-------------------------------------------------------------------------
 
@@ -799,8 +799,6 @@ CREATE VIEW pg_views AS \
     FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
     WHERE C.relkind = 'v';
 
--- XXX why does pg_tables include sequences?
-
 CREATE VIEW pg_tables AS \
     SELECT \
         N.nspname AS schemaname, \
@@ -1015,6 +1013,21 @@ CREATE VIEW pg_stat_database AS \
             pg_stat_get_db_blocks_hit(D.oid) AS blks_hit \
     FROM pg_database D;
 
+CREATE VIEW pg_locks_result AS \
+	SELECT \
+			''::oid AS relation, \
+			''::oid AS database, \
+			''::int4 AS backendpid, \
+			''::text AS mode, \
+			NULL::bool AS isgranted;
+
+UPDATE pg_proc SET \
+	prorettype = (SELECT oid FROM pg_type \
+		WHERE typname = 'pg_locks_result') \
+	WHERE proname = 'pg_lock_status';
+
+CREATE VIEW pg_locks AS SELECT * FROM pg_lock_status();
+
 CREATE VIEW pg_settings AS \
     SELECT \
             A.name, \
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 62c73a4b617..979bc6ed033 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: catversion.h,v 1.149 2002/08/16 23:01:20 tgl Exp $
+ * $Id: catversion.h,v 1.150 2002/08/17 13:04:15 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200208161
+#define CATALOG_VERSION_NO	200208171
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 2e71bfb07f5..2213fe7228c 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pg_proc.h,v 1.255 2002/08/16 23:01:20 tgl Exp $
+ * $Id: pg_proc.h,v 1.256 2002/08/17 13:04:15 momjian Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -2676,6 +2676,8 @@ DESCR("unary plus");
 DATA(insert OID = 1915 (  numeric_uplus    PGNSP PGUID 12 f f t f i 1 1700 "1700"  numeric_uplus - _null_ ));
 DESCR("unary plus");
 
+DATA(insert OID = 1920 (  pg_lock_status   PGNSP PGUID 12 f f f t v 0 0 "0" lock_status_srf - _null_ ));
+DESCR("view system lock information");
 DATA(insert OID = 1922 (  has_table_privilege		   PGNSP PGUID 12 f f t f s 3 16 "19 25 25"  has_table_privilege_name_name - _null_ ));
 DESCR("user privilege on relation by username, rel name");
 DATA(insert OID = 1923 (  has_table_privilege		   PGNSP PGUID 12 f f t f s 3 16 "19 26 25"  has_table_privilege_name_id - _null_ ));
diff --git a/src/include/storage/lock.h b/src/include/storage/lock.h
index c59c1cad01d..bedf6d7faf7 100644
--- a/src/include/storage/lock.h
+++ b/src/include/storage/lock.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: lock.h,v 1.64 2002/08/01 05:18:34 momjian Exp $
+ * $Id: lock.h,v 1.65 2002/08/17 13:04:18 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -203,6 +203,21 @@ typedef struct PROCLOCK
 #define PROCLOCK_LOCKMETHOD(holder) \
 		(((LOCK *) MAKE_PTR((holder).tag.lock))->tag.lockmethod)
 
+/*
+ * This struct is used to encapsulate information passed from lmgr
+ * internals to the lock listing statistical functions (lockfuncs.c).
+ * It's just a convenient bundle of other lock.h structures. All
+ * the information at a given index (holders[i], procs[i], locks[i])
+ * is related.
+ */
+typedef struct
+{
+	int		  nelements;	/* The length of holders, procs, & locks */
+	int		  currIdx;		/* Current element being examined */
+	PGPROC	 *procs;
+	LOCK	 *locks;
+	PROCLOCK *holders;
+} LockData;
 
 /*
  * function prototypes
@@ -227,6 +242,8 @@ extern void RemoveFromWaitQueue(PGPROC *proc);
 extern int	LockShmemSize(int maxBackends);
 extern bool DeadLockCheck(PGPROC *proc);
 extern void InitDeadLockChecking(void);
+extern void GetLockStatusData(LockData *data);
+extern char *GetLockmodeName(LOCKMODE mode);
 
 #ifdef LOCK_DEBUG
 extern void DumpLocks(void);
diff --git a/src/include/storage/shmem.h b/src/include/storage/shmem.h
index 835a1a3faf7..0f5e23f2635 100644
--- a/src/include/storage/shmem.h
+++ b/src/include/storage/shmem.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: shmem.h,v 1.37 2002/06/20 20:29:52 momjian Exp $
+ * $Id: shmem.h,v 1.38 2002/08/17 13:04:19 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,8 +53,7 @@ extern DLLIMPORT SHMEM_OFFSET ShmemBase;
 #define SHM_OFFSET_VALID(xx_offs)\
   (((xx_offs) != 0) && ((xx_offs) != INVALID_OFFSET))
 
-
-/* shmemqueue.c */
+/* shmqueue.c */
 typedef struct SHM_QUEUE
 {
 	SHMEM_OFFSET prev;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b8a76bb94d6..439f05ed8ce 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1268,6 +1268,8 @@ SELECT viewname, definition FROM pg_views ORDER BY viewname;
 --------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  iexit                    | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
  pg_indexes               | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid) AS indexdef FROM (((pg_index x JOIN pg_class c 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 = 'r'::"char") AND (i.relkind = 'i'::"char"));
+ pg_locks                 | SELECT pg_lock_status.relation, pg_lock_status."database", pg_lock_status.backendpid, pg_lock_status."mode", pg_lock_status.isgranted FROM pg_lock_status();
+ pg_locks_result          | SELECT 0::oid AS relation, 0::oid AS "database", 0 AS backendpid, ''::text AS "mode", NULL::boolean AS isgranted;
  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 FROM pg_show_all_settings() a;
  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.usename, pg_stat_get_backend_activity(s.backendid) AS current_query FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.usesysid));
@@ -1305,7 +1307,7 @@ SELECT viewname, definition FROM pg_views ORDER BY viewname;
  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;
-(39 rows)
+(41 rows)
 
 SELECT tablename, rulename, definition FROM pg_rules 
 	ORDER BY tablename, rulename;