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(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>