Skip to content

Commit 82119a6

Browse files
bmomjianbmomjian
committed
[ 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
1 parent f0ed431 commit 82119a6

File tree

11 files changed

+248
-20
lines changed

11 files changed

+248
-20
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 129 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.10 2002/08/04 19:51:30 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.11 2002/08/17 13:04:14 momjian Exp $
33
-->
44

55
<chapter id="monitoring">
@@ -543,6 +543,134 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S;
543543

544544
</sect2>
545545
</sect1>
546+
547+
<sect1 id="monitoring-locks">
548+
<title>Viewing Locks</title>
549+
550+
<para>
551+
Another useful tool for monitoring database activity is the
552+
<literal>pg_locks</literal> system catalog. This allows the
553+
database administrator to view information about the outstanding
554+
locks in the lock manager. For example, this capability can be
555+
used to:
556+
<itemizedlist>
557+
<listitem>
558+
<para>
559+
View all the locks currently outstanding, all the locks on
560+
relations in a particular database, all the locks on a
561+
particular relation, or all the locks held by a particular
562+
<productname>PostgreSQL</productname> backend.
563+
</para>
564+
</listitem>
565+
<listitem>
566+
<para>
567+
View the relation in the current database with the most
568+
un-granted locks (which might be a source of contention among
569+
database clients).
570+
</para>
571+
</listitem>
572+
<listitem>
573+
<para>
574+
Determine the effect of lock contention on overall database
575+
performance, as well as the extent to which contention varies
576+
with overall database traffic.
577+
</para>
578+
</listitem>
579+
</itemizedlist>
580+
581+
For more information on locking and managing concurrency with
582+
<productname>PostgreSQL</productname>, refer to the
583+
<citetitle>Administrator's Guide</citetitle>.
584+
</para>
585+
586+
<note>
587+
<para>
588+
When the <literal>pg_locks</literal> view is accessed, an
589+
exclusive lock on an internal lock manager data structure must be
590+
acquired to ensure that the data produced by the view is
591+
consistent. The lock held on this structure conflicts with normal
592+
database operations, and can therefore have an effect on overall
593+
database performance. Nevertheless, the performance impact of
594+
accessing this view should be minimal in most situations.
595+
</para>
596+
</note>
597+
598+
<para>
599+
The <literal>pg_locks</literal> view contains one row per
600+
lock. This means that if there are multiple locks on a single
601+
relation (which may or may not conflict with one another), a
602+
single relation may show up many times. Furthermore, only
603+
table-level locks are displayed (not row-level ones).
604+
</para>
605+
606+
<table>
607+
<title>Lock Status System View</title>
608+
609+
<tgroup cols="3">
610+
<thead>
611+
<row>
612+
<entry>Column Name</entry>
613+
<entry>Type</entry>
614+
<entry>Description</entry>
615+
</row>
616+
</thead>
617+
618+
<tbody>
619+
<row>
620+
<entry><structfield>relation</structfield></entry>
621+
<entry><type>oid</type></entry>
622+
<entry>The OID of the locked relation. When querying
623+
<literal>pg_locks</literal>, this column can be joined with the
624+
<literal>pg_class</literal> system catalog to get more
625+
information on the locked relation.</entry>
626+
</row>
627+
628+
<row>
629+
<entry><structfield>database</structfield></entry>
630+
<entry><type>oid</type></entry>
631+
<entry>The OID of the database in which the locked relation
632+
exists. If the lock is on a globally-shared object, this value
633+
will be 0. When querying <literal>pg_locks</literal>, this
634+
column can be joined with the <literal>pg_database</literal>
635+
system catalog to get more information on the locked object's
636+
database.</entry>
637+
</row>
638+
639+
<row>
640+
<entry><structfield>backendpid</structfield></entry>
641+
<entry><type>int4</type></entry>
642+
<entry>The process ID of the
643+
<productname>PostgreSQL</productname> backend that has
644+
acquired or is attempting to acquire the lock. If you have
645+
enabled the statistics collector, this column can be joined
646+
with the <literal>pg_stat_activity</literal> view to access
647+
more information on the backend holding or waiting to hold the
648+
lock.</entry>
649+
</row>
650+
651+
<row>
652+
<entry><structfield>mode</structfield></entry>
653+
<entry><type>text</type></entry>
654+
<entry>The mode of the lock. For more information on the
655+
different lock modes available in
656+
<productname>PostgreSQL</productname>, refer to the
657+
<citetitle>User's Guide</citetitle>.</entry>
658+
</row>
659+
660+
<row>
661+
<entry><structfield>isgranted</structfield></entry>
662+
<entry><type>text</type></entry>
663+
<entry>A boolean column indicating whether or not this
664+
particular lock has been granted. If the lock has not been
665+
granted, the backend atempting to acquire it will sleep until
666+
the lock is released (or a deadlock situation is detected). A
667+
single backend can be waiting to acquire at most one lock at
668+
any given time.</entry>
669+
</row>
670+
</tbody>
671+
</tgroup>
672+
</table>
673+
</sect1>
546674
</chapter>
547675

548676
<!-- Keep this comment at the end of the file

‎doc/src/sgml/mvcc.sgml

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.26 2002/08/05 19:43:31 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.27 2002/08/17 13:04:14 momjian Exp $
33
-->
44

55
<chapter id="mvcc">
@@ -408,7 +408,7 @@ ERROR: Can't serialize access due to concurrent update
408408
modes on the same table at the same time. (However, a transaction
409409
never conflicts with itself --- for example, it may acquire
410410
<literal>ACCESS EXCLUSIVE</literal> lock and later acquire
411-
<literal>ACCESS SHARE</literal> lock on the same table.) Nonconflicting
411+
<literal>ACCESS SHARE</literal> lock on the same table.) Non-conflicting
412412
lock modes may be held concurrently by many transactions. Notice in
413413
particular that some lock modes are self-conflicting (for example,
414414
<literal>ACCESS EXCLUSIVE</literal> cannot be held by more than one
@@ -417,6 +417,14 @@ ERROR: Can't serialize access due to concurrent update
417417
Once acquired, a lock mode is held till end of transaction.
418418
</para>
419419

420+
<para>
421+
To examine a list of the currently outstanding locks in a
422+
database server, use the <literal>pg_locks</literal> system
423+
view. For more information on monitoring the status of the lock
424+
manager subsystem, refer to the <citetitle>Administrator's
425+
Guide</citetitle>.
426+
</para>
427+
420428
<variablelist>
421429
<title>Table-level lock modes</title>
422430
<varlistentry>

‎src/backend/storage/lmgr/lock.c

Lines changed: 61 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/storage/lmgr/lock.c,v 1.111 2002/08/01 05:18:33 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/storage/lmgr/lock.c,v 1.112 2002/08/17 13:04:14 momjian Exp $
1212
*
1313
* NOTES
1414
* Outside modules can create a lock table and acquire/release
@@ -1359,6 +1359,66 @@ LockShmemSize(int maxBackends)
13591359
return size;
13601360
}
13611361

1362+
/*
1363+
* GetLockStatusData - Return a summary of the lock manager's internal
1364+
* status, for use in a user-level statistical reporting function.
1365+
*
1366+
* This function should be passed a pointer to a LockData struct. It fills
1367+
* the structure with the appropriate information and returns. The goal
1368+
* is to hold the LockMgrLock for as short a time as possible; thus, the
1369+
* function simply makes a copy of the necessary data and releases the
1370+
* lock, allowing the caller to contemplate and format the data for
1371+
* as long as it pleases.
1372+
*/
1373+
void
1374+
GetLockStatusData(LockData *data)
1375+
{
1376+
HTAB *holderTable;
1377+
PROCLOCK *holder;
1378+
HASH_SEQ_STATUS seqstat;
1379+
int i = 0;
1380+
1381+
data->currIdx = 0;
1382+
1383+
LWLockAcquire(LockMgrLock, LW_EXCLUSIVE);
1384+
1385+
holderTable = LockMethodTable[DEFAULT_LOCKMETHOD]->holderHash;
1386+
1387+
data->nelements = holderTable->hctl->nentries;
1388+
1389+
data->procs = (PGPROC *) palloc(sizeof(PGPROC) * data->nelements);
1390+
data->locks = (LOCK *) palloc(sizeof(LOCK) * data->nelements);
1391+
data->holders = (PROCLOCK *) palloc(sizeof(PROCLOCK) * data->nelements);
1392+
1393+
hash_seq_init(&seqstat, holderTable);
1394+
1395+
while ( (holder = hash_seq_search(&seqstat)) )
1396+
{
1397+
PGPROC *proc;
1398+
LOCK *lock;
1399+
1400+
/* Only do a shallow copy */
1401+
proc = (PGPROC *) MAKE_PTR(holder->tag.proc);
1402+
lock = (LOCK *) MAKE_PTR(holder->tag.lock);
1403+
1404+
memcpy(&(data->procs[i]), proc, sizeof(PGPROC));
1405+
memcpy(&(data->locks[i]), lock, sizeof(LOCK));
1406+
memcpy(&(data->holders[i]), holder, sizeof(PROCLOCK));
1407+
1408+
i++;
1409+
}
1410+
1411+
Assert(i == data->nelements);
1412+
1413+
LWLockRelease(LockMgrLock);
1414+
}
1415+
1416+
char *
1417+
GetLockmodeName(LOCKMODE mode)
1418+
{
1419+
Assert(mode <= MAX_LOCKMODES);
1420+
return lock_mode_names[mode];
1421+
}
13621422

13631423
#ifdef LOCK_DEBUG
13641424
/*

‎src/backend/tcop/utility.c

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.171 2002/08/17 12:15:49 momjian Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.172 2002/08/17 13:04:15 momjian Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -239,8 +239,7 @@ ProcessUtility(Node *parsetree,
239239
break;
240240

241241
/*
242-
* ******************************** portal manipulation ********************************
243-
*
242+
* ************************* portal manipulation ***************************
244243
*/
245244
case T_ClosePortalStmt:
246245
{

‎src/backend/utils/adt/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
#
22
# Makefile for utils/adt
33
#
4-
# $Header: /cvsroot/pgsql/src/backend/utils/adt/Makefile,v 1.51 2001/10/04 04:13:40 ishii Exp $
4+
# $Header: /cvsroot/pgsql/src/backend/utils/adt/Makefile,v 1.52 2002/08/17 13:04:15 momjian Exp $
55
#
66

77
subdir = src/backend/utils/adt
@@ -17,7 +17,7 @@ endif
1717

1818
OBJS = acl.o arrayfuncs.o arrayutils.o bool.o cash.o char.o \
1919
date.o datetime.o datum.o float.o format_type.o \
20-
geo_ops.o geo_selfuncs.o int.o int8.o like.o \
20+
geo_ops.o geo_selfuncs.o int.o int8.o like.o lockfuncs.o \
2121
misc.o nabstime.o name.o not_in.o numeric.o numutils.o \
2222
oid.o oracle_compat.o \
2323
regexp.o regproc.o ruleutils.o selfuncs.o sets.o \

‎src/bin/initdb/initdb.sh

Lines changed: 16 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,7 @@
2727
# Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
2828
# Portions Copyright (c) 1994, Regents of the University of California
2929
#
30-
# $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.166 2002/08/15 02:51:26 momjian Exp $
30+
# $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.167 2002/08/17 13:04:15 momjian Exp $
3131
#
3232
#-------------------------------------------------------------------------
3333

@@ -799,8 +799,6 @@ CREATE VIEW pg_views AS \
799799
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
800800
WHERE C.relkind = 'v';
801801
802-
-- XXX why does pg_tables include sequences?
803-
804802
CREATE VIEW pg_tables AS \
805803
SELECT \
806804
N.nspname AS schemaname, \
@@ -1015,6 +1013,21 @@ CREATE VIEW pg_stat_database AS \
10151013
pg_stat_get_db_blocks_hit(D.oid) AS blks_hit \
10161014
FROM pg_database D;
10171015
1016+
CREATE VIEW pg_locks_result AS \
1017+
SELECT \
1018+
''::oid AS relation, \
1019+
''::oid AS database, \
1020+
''::int4 AS backendpid, \
1021+
''::text AS mode, \
1022+
NULL::bool AS isgranted;
1023+
1024+
UPDATE pg_proc SET \
1025+
prorettype = (SELECT oid FROM pg_type \
1026+
WHERE typname = 'pg_locks_result') \
1027+
WHERE proname = 'pg_lock_status';
1028+
1029+
CREATE VIEW pg_locks AS SELECT * FROM pg_lock_status();
1030+
10181031
CREATE VIEW pg_settings AS \
10191032
SELECT \
10201033
A.name, \

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $Id: catversion.h,v 1.149 2002/08/16 23:01:20 tgl Exp $
40+
* $Id: catversion.h,v 1.150 2002/08/17 13:04:15 momjian Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200208161
56+
#define CATALOG_VERSION_NO 200208171
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: pg_proc.h,v 1.255 2002/08/16 23:01:20 tgl Exp $
10+
* $Id: pg_proc.h,v 1.256 2002/08/17 13:04:15 momjian Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2676,6 +2676,8 @@ DESCR("unary plus");
26762676
DATA(insert OID = 1915 ( numeric_uplus PGNSP PGUID 12 f f t f i 1 1700 "1700" numeric_uplus - _null_ ));
26772677
DESCR("unary plus");
26782678

2679+
DATA(insert OID = 1920 ( pg_lock_status PGNSP PGUID 12 f f f t v 0 0 "0" lock_status_srf - _null_ ));
2680+
DESCR("view system lock information");
26792681
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_ ));
26802682
DESCR("user privilege on relation by username, rel name");
26812683
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_ ));

0 commit comments

Comments
 (0)