On Thu, Jul 18, 2002 at 02:35:42PM -0400, Neil Conway wrote:
> I've been working on the TODO list item "Add SHOW command to display locks". The
> code is basically finished, but I'd like to make sure the user interface is okay
> with everyone before I send it in to -patches (if you're interested, the patch
> is attached).
Woops, forgot to 'cvs add' a newly created file. (Thanks to Joe Conway
for letting me know.)
A fixed patch is attached.
Cheers,
Neil
--
Neil Conway <[EMAIL PROTECTED]>
PGP Key ID: DB3C29FC
Index: src/backend/storage/lmgr/lmgr.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/storage/lmgr/lmgr.c,v
retrieving revision 1.53
diff -c -r1.53 lmgr.c
*** src/backend/storage/lmgr/lmgr.c 20 Jun 2002 20:29:35 -0000 1.53
--- src/backend/storage/lmgr/lmgr.c 18 Jul 2002 20:27:31 -0000
***************
*** 104,118 ****
if (!(LockTableId))
elog(ERROR, "InitLockTable: couldn't initialize lock table");
- #ifdef USER_LOCKS
-
/*
* Allocate another tableId for long-term locks
*/
LongTermTableId = LockMethodTableRename(LockTableId);
if (!(LongTermTableId))
elog(ERROR, "InitLockTable: couldn't rename long-term lock table");
- #endif
return LockTableId;
}
--- 104,115 ----
Index: src/backend/storage/lmgr/lock.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/storage/lmgr/lock.c,v
retrieving revision 1.108
diff -c -r1.108 lock.c
*** src/backend/storage/lmgr/lock.c 20 Jun 2002 20:29:35 -0000 1.108
--- src/backend/storage/lmgr/lock.c 18 Jul 2002 20:27:31 -0000
***************
*** 1053,1063 ****
if (!holder)
{
LWLockRelease(masterLock);
- #ifdef USER_LOCKS
if (lockmethod == USER_LOCKMETHOD)
elog(WARNING, "LockRelease: no lock with this tag");
else
- #endif
elog(WARNING, "LockRelease: holder table corrupted");
return FALSE;
}
--- 1053,1061 ----
***************
*** 1373,1378 ****
--- 1371,1442 ----
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;
+ HOLDER *holder;
+ int i = 0;
+ HASH_SEQ_STATUS seqstat;
+
+ data->currIdx = 0;
+
+ LWLockAcquire(LockMgrLock, LW_EXCLUSIVE);
+
+ holderTable = LockMethodTable[DEFAULT_LOCKMETHOD]->holderHash;
+
+ data->nelements = holderTable->hctl->nentries;
+
+ data->holders = (HOLDER **) palloc(sizeof(HOLDER *) * data->nelements);
+ data->procs = (PGPROC **) palloc(sizeof(PGPROC *) * data->nelements);
+ data->locks = (LOCK **) palloc(sizeof(LOCK *) * data->nelements);
+
+ hash_seq_init(&seqstat, holderTable);
+
+ while ( (holder = hash_seq_search(&seqstat)) )
+ {
+ PGPROC *proc;
+ LOCK *lock;
+
+ data->holders[i] = (HOLDER *) palloc(sizeof(HOLDER));
+ data->procs[i] = (PGPROC *) palloc(sizeof(PGPROC));
+ data->locks[i] = (LOCK *) palloc(sizeof(LOCK));
+
+ /* Only do a shallow copy */
+ memcpy(data->holders[i], holder, sizeof(HOLDER));
+
+ proc = (PGPROC *) MAKE_PTR(holder->tag.proc);
+
+ memcpy(data->procs[i], proc, sizeof(PGPROC));
+
+ lock = (LOCK *) MAKE_PTR(holder->tag.lock);
+
+ memcpy(data->locks[i], lock, sizeof(LOCK));
+
+ i++;
+ }
+
+ Assert(i == data->nelements);
+
+ LWLockRelease(LockMgrLock);
+ }
+
+ char *
+ GetLockmodeName(LOCKMODE mode)
+ {
+ return lock_mode_names[mode];
+ }
#ifdef LOCK_DEBUG
/*
Index: src/backend/storage/lmgr/proc.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/storage/lmgr/proc.c,v
retrieving revision 1.122
diff -c -r1.122 proc.c
*** src/backend/storage/lmgr/proc.c 13 Jul 2002 01:02:14 -0000 1.122
--- src/backend/storage/lmgr/proc.c 18 Jul 2002 20:27:31 -0000
***************
*** 397,406 ****
/* Remove from the standard lock table */
LockReleaseAll(DEFAULT_LOCKMETHOD, MyProc, true, InvalidTransactionId);
- #ifdef USER_LOCKS
/* Remove from the user lock table */
LockReleaseAll(USER_LOCKMETHOD, MyProc, true, InvalidTransactionId);
- #endif
SpinLockAcquire(ProcStructLock);
--- 397,404 ----
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.163
diff -c -r1.163 utility.c
*** src/backend/tcop/utility.c 18 Jul 2002 16:47:25 -0000 1.163
--- src/backend/tcop/utility.c 18 Jul 2002 20:27:31 -0000
***************
*** 217,224 ****
break;
/*
! * ******************************** portal manipulation
********************************
! *
*/
case T_ClosePortalStmt:
{
--- 217,223 ----
break;
/*
! * ************************* portal manipulation
***************************
*/
case T_ClosePortalStmt:
{
Index: src/backend/utils/adt/Makefile
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/utils/adt/Makefile,v
retrieving revision 1.51
diff -c -r1.51 Makefile
*** src/backend/utils/adt/Makefile 4 Oct 2001 04:13:40 -0000 1.51
--- src/backend/utils/adt/Makefile 18 Jul 2002 20:27:31 -0000
***************
*** 17,23 ****
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 \
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 \
--- 17,23 ----
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 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 \
Index: src/backend/utils/adt/lockfuncs.c
===================================================================
RCS file: src/backend/utils/adt/lockfuncs.c
diff -N src/backend/utils/adt/lockfuncs.c
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/backend/utils/adt/lockfuncs.c 18 Jul 2002 20:27:31 -0000
***************
*** 0 ****
--- 1,160 ----
+ /*
+ * lockfuncs.c
+ * Set-returning functions to view the state of locks within the DB.
+ *
+ * Copyright (c) 2002, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * $Header$
+ */
+
+ #include "postgres.h"
+ #include "fmgr.h"
+ #include "funcapi.h"
+ #include "storage/lmgr.h"
+ #include "storage/lock.h"
+ #include "storage/lwlock.h"
+ #include "storage/proc.h"
+
+ Datum show_locks_srf(PG_FUNCTION_ARGS);
+
+ static int next_lock(int locks[]);
+
+ Datum
+ show_locks_srf(PG_FUNCTION_ARGS)
+ {
+ FuncCallContext *funccxt;
+ LockData *lockData;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ TupleDesc tupdesc;
+
+ funccxt = SRF_FIRSTCALL_INIT();
+
+ tupdesc = RelationNameGetTupleDesc("__show_locks_result");
+
+ funccxt->slot = TupleDescGetSlot(tupdesc);
+
+ funccxt->attinmeta = TupleDescGetAttInMetadata(tupdesc);
+
+ /*
+ * Preload all the locking information that we will eventually format
+ * and send out as a result set. This is palloc'ed, but since the
+ * MemoryContext is reset when the SRF finishes, we don't need to
+ * free it ourselves.
+ */
+ funccxt->user_fctx = (LockData *) palloc(sizeof(LockData));
+
+ GetLockStatusData(funccxt->user_fctx);
+ }
+
+ funccxt = SRF_PERCALL_SETUP();
+ lockData = (LockData *) funccxt->user_fctx;
+
+ top:
+ /* If the list is empty, we're done */
+ if (lockData->currIdx >= lockData->nelements)
+ SRF_RETURN_DONE(funccxt);
+ else
+ {
+ HOLDER *holder;
+ LOCK *lock;
+ PGPROC *proc;
+ TupleTableSlot *slot;
+ AttInMetadata *metadata;
+ HeapTuple tuple;
+ Datum result;
+ char **values;
+ LOCKMODE mode;
+ int i;
+ int currIdx = lockData->currIdx;
+
+ #define NUM_ATTRS 5
+
+ slot = funccxt->slot;
+ metadata = funccxt->attinmeta;
+ holder = lockData->holders[currIdx];
+ lock = lockData->locks[currIdx];
+ proc = lockData->procs[currIdx];
+
+ values = (char **) palloc(sizeof(*values) * NUM_ATTRS);
+
+ for (i = 0; i < NUM_ATTRS; i++)
+ values[i] = (char *) palloc(32);
+
+ /* The OID of the locked relation */
+ snprintf(values[0], 16, "%d", lock->tag.relId);
+ /* The database the relation is in */
+ snprintf(values[1], 16, "%d", lock->tag.dbId);
+ /* The PID of the backend holding or waiting for the lock */
+ snprintf(values[2], 16, "%d", proc->pid);
+
+ /*
+ * We need to report both the locks held (i.e. successfully acquired)
+ * by this holder, as well as the locks upon which it is still
+ * waiting, if any. Since a single HOLDER struct may contain
+ * multiple locks, we may need to loop several times before we
+ * advance the array index and continue on.
+ */
+ if (holder->nHolding > 0)
+ {
+ /* Already held locks */
+ mode = next_lock(holder->holding);
+ holder->holding[mode]--;
+ holder->nHolding--;
+
+ snprintf(values[4], 16, "%c", 't');
+ }
+ else if (proc->waitLock != NULL)
+ {
+ /* Lock that is still being waited on */
+ mode = proc->waitLockMode;
+ proc->waitLock = NULL;
+ proc->waitLockMode = NoLock;
+
+ snprintf(values[4], 16, "%c", 'f');
+ }
+ else
+ {
+ /*
+ * Okay, we've displayed all the lock's belonging to this
+HOLDER,
+ * procede to the next one. (Note: "Go To Statement Considered
+ * Harmful" notwithstanding, GOTO is appropriate here IMHO)
+ */
+ pfree(holder);
+ pfree(lock);
+ pfree(proc);
+ lockData->currIdx++;
+ goto top;
+ }
+
+ strncpy(values[3], GetLockmodeName(mode), 32);
+
+ tuple = BuildTupleFromCStrings(metadata, values);
+
+ result = TupleGetDatum(slot, tuple);
+
+ /* Cleanup and return next tuple in result set */
+ for (i = 0; i < NUM_ATTRS; i++)
+ pfree(values[i]);
+ pfree(values);
+ SRF_RETURN_NEXT(funccxt, result);
+ }
+ }
+
+ static LOCKMODE
+ next_lock(int locks[])
+ {
+ LOCKMODE i;
+
+ for (i = 0; i < MAX_LOCKMODES; i++)
+ {
+ if (locks[i] != 0)
+ return i;
+ }
+
+ /* No locks found: this should not occur */
+ Assert(false);
+ return -1;
+ }
Index: src/bin/initdb/initdb.sh
===================================================================
RCS file: /var/lib/cvs/pgsql/src/bin/initdb/initdb.sh,v
retrieving revision 1.160
diff -c -r1.160 initdb.sh
*** src/bin/initdb/initdb.sh 18 Jul 2002 16:47:25 -0000 1.160
--- src/bin/initdb/initdb.sh 18 Jul 2002 20:27:31 -0000
***************
*** 763,770 ****
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 \
C.relname AS tablename, \
--- 763,768 ----
***************
*** 969,974 ****
--- 967,985 ----
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;
+
+ CREATE VIEW __show_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 = '__show_locks_result') \
+ WHERE proname = 'show_locks';
EOF
if [ "$?" -ne 0 ]; then
Index: src/include/pg_config.h.in
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/pg_config.h.in,v
retrieving revision 1.24
diff -c -r1.24 pg_config.h.in
*** src/include/pg_config.h.in 5 May 2002 00:03:29 -0000 1.24
--- src/include/pg_config.h.in 18 Jul 2002 20:27:31 -0000
***************
*** 180,194 ****
/* #define TCL_ARRAYS */
/*
- * User locks are handled totally on the application side as long term
- * cooperative locks which extend beyond the normal transaction boundaries.
- * Their purpose is to indicate to an application that someone is `working'
- * on an item. Define this flag to enable user locks. You will need the
- * loadable module user-locks.c to use this feature.
- */
- #define USER_LOCKS
-
- /*
* Define this if you want psql to _always_ ask for a username and a password
* for password authentication.
*/
--- 180,185 ----
Index: src/include/catalog/catversion.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/catalog/catversion.h,v
retrieving revision 1.140
diff -c -r1.140 catversion.h
*** src/include/catalog/catversion.h 15 Jul 2002 16:33:31 -0000 1.140
--- src/include/catalog/catversion.h 18 Jul 2002 20:27:31 -0000
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200207141
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200207181
#endif
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.243
diff -c -r1.243 pg_proc.h
*** src/include/catalog/pg_proc.h 20 Jun 2002 20:29:44 -0000 1.243
--- src/include/catalog/pg_proc.h 18 Jul 2002 20:27:31 -0000
***************
*** 2681,2686 ****
--- 2681,2689 ----
DATA(insert OID = 1915 ( numeric_uplus PGNSP PGUID 12 f f f t f i 1 1700 "1700"
100 0 0 100 numeric_uplus - _null_ ));
DESCR("unary plus");
+ DATA(insert OID = 1920 ( show_locks PGNSP PGUID 12 f f f t t v 0 0 "0" 100 0 0
+100 show_locks_srf - _null_ ));
+ DESCR("view system lock information");
+
DATA(insert OID = 1922 ( has_table_privilege PGNSP PGUID 12 f f f t f s
3 16 "19 25 25" 100 0 0 100 has_table_privilege_name_name - _null_ ));
DESCR("user privilege on relation by username, relname");
DATA(insert OID = 1923 ( has_table_privilege PGNSP PGUID 12 f f f t f s
3 16 "19 26 25" 100 0 0 100 has_table_privilege_name_id - _null_ ));
Index: src/include/storage/lock.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/storage/lock.h,v
retrieving revision 1.61
diff -c -r1.61 lock.h
*** src/include/storage/lock.h 20 Jun 2002 20:29:52 -0000 1.61
--- src/include/storage/lock.h 18 Jul 2002 20:27:31 -0000
***************
*** 59,66 ****
#define USER_LOCKMETHOD 2
/*
! * There is normally only one lock method, the default one.
! * If user locks are enabled, an additional lock method is present.
*
* LOCKMETHODCTL and LOCKMETHODTABLE are split because the first lives
* in shared memory. (There isn't any really good reason for the split.)
--- 59,66 ----
#define USER_LOCKMETHOD 2
/*
! * There are currently two lock methods: the default method, and the method
! * used for user locks.
*
* LOCKMETHODCTL and LOCKMETHODTABLE are split because the first lives
* in shared memory. (There isn't any really good reason for the split.)
***************
*** 222,227 ****
--- 222,242 ----
#define HOLDER_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 */
+ HOLDER **holders;
+ PGPROC **procs;
+ LOCK **locks;
+ } LockData;
/*
* function prototypes
***************
*** 246,251 ****
--- 261,268 ----
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);
Index: src/include/storage/shmem.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/storage/shmem.h,v
retrieving revision 1.37
diff -c -r1.37 shmem.h
*** src/include/storage/shmem.h 20 Jun 2002 20:29:52 -0000 1.37
--- src/include/storage/shmem.h 18 Jul 2002 20:27:31 -0000
***************
*** 53,60 ****
#define SHM_OFFSET_VALID(xx_offs)\
(((xx_offs) != 0) && ((xx_offs) != INVALID_OFFSET))
!
! /* shmemqueue.c */
typedef struct SHM_QUEUE
{
SHMEM_OFFSET prev;
--- 53,59 ----
#define SHM_OFFSET_VALID(xx_offs)\
(((xx_offs) != 0) && ((xx_offs) != INVALID_OFFSET))
! /* shmqueue.c */
typedef struct SHM_QUEUE
{
SHMEM_OFFSET prev;
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.53
diff -c -r1.53 rules.out
*** src/test/regress/expected/rules.out 3 May 2002 00:32:19 -0000 1.53
--- src/test/regress/expected/rules.out 18 Jul 2002 20:27:31 -0000
***************
*** 1266,1271 ****
--- 1266,1272 ----
SELECT viewname, definition FROM pg_views ORDER BY viewname;
viewname |
definition
--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ __show_locks_result | SELECT 0::oid AS relation, 0::oid AS "database", 0 AS
+backendpid, ''::text AS "mode", NULL::boolean AS isgranted;
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 c.relname AS tablename, i.relname AS indexname,
pg_get_indexdef(x.indexrelid) AS indexdef FROM pg_index x, pg_class c, pg_class i
WHERE ((((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")) AND (c.oid =
x.indrelid)) AND (i.oid = x.indexrelid));
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);
***************
*** 1304,1310 ****
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;
! (38 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
--- 1305,1311 ----
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)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
