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
callerto 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
>