Re: obtaining row locking information

> Should this functionality be moved into the backend?  When?

Since feature freeze for 8.1 has been already made, I think this
should be into 8.2 or later if necessary.

BTW, I have modified pgrowlocks so that it shows pids:

test=# select * from pgrowlocks('t1');
 locked_row | lock_type | locker | multi |   xids    |    pids     
------------+-----------+--------+-------+-----------+-------------
      (0,1) | Shared    |     13 | t     | {751,754} | {2259,2261}
      (0,4) | Exclusive |    747 | f     | {747}     | {2255}
(2 rows)

To accomplish this I need to add following function into
storage/ipc/procarray.c. This is similar to BackendPidGetProc() except
that it accepts xid as an argument. Any objection?
--
Tatsuo Ishii

/*
 * BackendXidGetProc -- get a backend's PGPROC given its XID
 *
 * Returns NULL if not found.  Note that it is up to the caller to be
 * sure that the question remains meaningful for long enough for the
 * answer to be used ...
 */
PGPROC *
BackendXidGetProc(TransactionId xid)
{
	PGPROC	   *result = NULL;
	ProcArrayStruct *arrayP = procArray;
	int			index;

	if (xid == 0)				/* never match dummy PGPROCs */
		return NULL;

	LWLockAcquire(ProcArrayLock, LW_SHARED);

	for (index = 0; index < arrayP->numProcs; index++)
	{
		PGPROC	   *proc = arrayP->procs[index];

		if (proc->xid == xid)
		{
			result = proc;
			break;
		}
	}

	LWLockRelease(ProcArrayLock);

	return result;
}


> ---------------------------------------------------------------------------
> 
> Tatsuo Ishii wrote:
> > > On Fri, Aug 12, 2005 at 02:08:29PM +0900, Tatsuo Ishii wrote:
> > > > > On Fri, Aug 12, 2005 at 12:27:25PM +0900, Tatsuo Ishii wrote:
> > > > > 
> > > > > > However even one of transactions, for example 647 commits, still it
> > > > > > shows as if 647 is a member of muitixid 3.
> > > > > > 
> > > > > > test=# select * from pgrowlocks('t1');
> > > > > >  locked_row | lock_type | locker | multi |   xids    
> > > > > > ------------+-----------+--------+-------+-----------
> > > > > >       (0,1) | Shared    |      3 | t     | {646,647}
> > > > > > (1 row)
> > > > > > 
> > > > > > Am I missing something?
> > > > > 
> > > > > By design, a MultiXactId does not change its membership, that is, no
> > > > > members are added nor deleted.  When this has to happen (for example a
> > > > > row is locked by another backend), a new MultiXactId is generated.  The
> > > > > caller is expected to check whether the member transactions are still
> > > > > running.
> > > > 
> > > > But it seems when members are deleted, new multixid is not
> > > > generated. i.e. I see "locker" column does not change. Is this an
> > > > expected behavior?
> > > 
> > > Yes.  Members are never deleted.  This is for two reasons: first, the
> > > transaction could theoretically hold millions of MultiXactId, and we
> > > can't expect it to remember them all; so we don't have a way to find out
> > > which ones it should clean up when it finishes (a process which would be
> > > slow and cumbersome anyway).  Second, because the implementation does
> > > not really allow for shrinking (nor enlarging) an array.  Once created,
> > > the array is immutable.
> > > 
> > > If you locked a tuple with transactions B and C; then transaction B
> > > committed; then transaction D locked the tuple again, you would see a
> > > new MultiXactId comprising transactions C and D.
> > 
> > Ok, here is the new version of the function which now checks if the
> > transactions are still running.
> > 
> > BTW, I think it would be helpfull if the function returns the process
> > id which runs the transaction. I couldn't find any existing function
> > which converts an xid to a process id so far, and think inventing
> > someting like BackendPidGetProc(int pid) would be the way I should
> > go. Any suggestion?
> > --
> > Tatsuo Ishii
> 
> > /*
> >  * $PostgreSQL$
> >  *
> >  * Copyright (c) 2005	Tatsuo Ishii
> >  *
> >  * Permission to use, copy, modify, and distribute this software and
> >  * its documentation for any purpose, without fee, and without a
> >  * written agreement is hereby granted, provided that the above
> >  * copyright notice and this paragraph and the following two
> >  * paragraphs appear in all copies.
> >  *
> >  * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
> >  * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
> >  * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
> >  * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
> >  * OF THE POSSIBILITY OF SUCH DAMAGE.
> >  *
> >  * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
> >  * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
> >  * A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS
> >  * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
> >  * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
> >  */
> > 
> > #include "postgres.h"
> > 
> > #include "funcapi.h"
> > #include "access/heapam.h"
> > #include "access/multixact.h"
> > #include "access/transam.h"
> > #include "catalog/namespace.h"
> > #include "catalog/pg_type.h"
> > #include "storage/procarray.h"
> > #include "utils/builtins.h"
> > 
> > 
> > PG_FUNCTION_INFO_V1(pgrowlocks);
> > 
> > extern Datum pgrowlocks(PG_FUNCTION_ARGS);
> > 
> > /* ----------
> >  * pgrowlocks:
> >  * returns tids of rows being locked
> >  *
> >  * C FUNCTION definition
> >  * pgrowlocks(text) returns set of pgrowlocks_type
> >  * see pgrowlocks.sql for pgrowlocks_type
> >  * ----------
> >  */
> > 
> > #define DUMMY_TUPLE "public.pgrowlocks_type"
> > #define NCHARS 32
> > 
> > /*
> >  * define this if makeRangeVarFromNameList() has two arguments. As far
> >  * as I know, this only happens in 8.0.x.
> >  */
> > #undef MAKERANGEVARFROMNAMELIST_HAS_TWO_ARGS
> > 
> > typedef struct {
> > 	HeapScanDesc scan;
> > 	int ncolumns;
> > } MyData;
> > 
> > Datum
> > pgrowlocks(PG_FUNCTION_ARGS)
> > {
> > 	FuncCallContext *funcctx;
> > 	HeapScanDesc scan;
> > 	HeapTuple	tuple;
> > 	TupleDesc	tupdesc;
> > 	AttInMetadata *attinmeta;
> > 	Datum		result;
> > 	MyData *mydata;
> > 	Relation	rel;
> > 
> > 	if (SRF_IS_FIRSTCALL())
> > 	{
> > 		text	   *relname;
> > 		RangeVar   *relrv;
> > 		MemoryContext oldcontext;
> > 
> > 		funcctx = SRF_FIRSTCALL_INIT();
> > 		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
> > 
> > 		tupdesc = RelationNameGetTupleDesc(DUMMY_TUPLE);
> > 		attinmeta = TupleDescGetAttInMetadata(tupdesc);
> > 		funcctx->attinmeta = attinmeta;
> > 
> > 		relname = PG_GETARG_TEXT_P(0);
> > #ifdef MAKERANGEVARFROMNAMELIST_HAS_TWO_ARGS
> > 		relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname,												 "pgrowlocks"));
> > 
> > #else
> > 		relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
> > #endif
> > 		rel = heap_openrv(relrv, AccessShareLock);
> > 		scan = heap_beginscan(rel, SnapshotNow, 0, NULL);
> > 		mydata = palloc(sizeof(*mydata));
> > 		mydata->scan = scan;
> > 		mydata->ncolumns = tupdesc->natts;
> > 		funcctx->user_fctx = mydata;
> > 
> > 		MemoryContextSwitchTo(oldcontext);
> > 	}
> > 
> > 	funcctx = SRF_PERCALL_SETUP();
> > 	attinmeta = funcctx->attinmeta;
> > 	mydata = (MyData *)funcctx->user_fctx;
> > 	scan = mydata->scan;
> > 
> > 	/* scan the relation */
> > 	while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
> > 	{
> > 		/* must hold a buffer lock to call HeapTupleSatisfiesUpdate */
> > 		LockBuffer(scan->rs_cbuf, BUFFER_LOCK_SHARE);
> > 
> > 		if (HeapTupleSatisfiesUpdate(tuple->t_data, GetCurrentCommandId(), scan->rs_cbuf)
> > 		    == HeapTupleBeingUpdated)
> > 		{
> > 
> > 			char **values;
> > 			int i;
> > 
> > 			values = (char **) palloc(mydata->ncolumns * sizeof(char *));
> > 
> > 			i = 0;
> > 			values[i++] = (char *)DirectFunctionCall1(tidout, PointerGetDatum(&tuple->t_self));
> > 
> > #ifdef HEAP_XMAX_SHARED_LOCK
> > 			if (tuple->t_data->t_infomask & HEAP_XMAX_SHARED_LOCK)
> > 				values[i++] = pstrdup("Shared");
> > 			else
> > 				values[i++] = pstrdup("Exclusive");
> > #else
> > 			values[i++] = pstrdup("Exclusive");
> > #endif
> > 			values[i] = palloc(NCHARS*sizeof(char));
> > 			snprintf(values[i++], NCHARS, "%d", HeapTupleHeaderGetXmax(tuple->t_data));
> > #ifdef HEAP_XMAX_SHARED_LOCK
> > 			if (tuple->t_data->t_infomask & HEAP_XMAX_IS_MULTI)
> > 			{
> > 				TransactionId *xids;
> > 				int nxids;
> > 				int j;
> > 				int isValidXid = 0;		/* any valid xid ever exists? */
> > 
> > 				values[i++] = pstrdup("true");
> > 				nxids = GetMultiXactIdMembers(HeapTupleHeaderGetXmax(tuple->t_data), &xids);
> > 				if (nxids == -1)
> > 				{
> > 					elog(ERROR, "GetMultiXactIdMembers returns error");
> > 				}
> > 
> > 				values[i] = palloc(NCHARS*nxids);
> > 				strcpy(values[i], "{");
> > 
> > 				for (j=0;j<nxids;j++)
> > 				{
> > 					char buf[NCHARS];
> > 
> > 					if (TransactionIdIsInProgress(xids[j]))
> > 					{
> > 						if (isValidXid)
> > 						{
> > 							strcat(values[i], ",");
> > 						}
> > 						snprintf(buf, NCHARS, "%d", xids[j]);
> > 						strcat(values[i], buf);
> > 						isValidXid = 1;
> > 					}
> > 				}
> > 
> > 				strcat(values[i], "}");
> > 				i++;
> > 			}
> > 			else
> > 			{
> > 				values[i++] = pstrdup("false");
> > 				values[i++] = pstrdup("{}");
> > 			}
> > #else
> > 			values[i++] = pstrdup("false");
> > 			values[i++] = pstrdup("{}");
> > #endif
> > 
> > 			LockBuffer(scan->rs_cbuf, BUFFER_LOCK_UNLOCK);
> > 
> > 			/* build a tuple */
> > 			tuple = BuildTupleFromCStrings(attinmeta, values);
> > 
> > 			/* make the tuple into a datum */
> > 			result = HeapTupleGetDatum(tuple);
> > 
> > 			/* Clean up */
> > 			for (i = 0; i < mydata->ncolumns; i++)
> > 				pfree(values[i]);
> > 			pfree(values);
> > 
> > 			SRF_RETURN_NEXT(funcctx, result);
> > 		}
> > 		else
> > 		{
> > 			LockBuffer(scan->rs_cbuf, BUFFER_LOCK_UNLOCK);
> > 		}
> > 	}
> > 
> > 	heap_endscan(scan);
> > 	heap_close(scan->rs_rd, AccessShareLock);
> > 
> > 	SRF_RETURN_DONE(funcctx);
> > }
> 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo@postgresql.org so that your
> >        message can get through to the mailing list cleanly
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
>