Re: obtaining row locking information

Should this functionality be moved into the backend?  When?

---------------------------------------------------------------------------

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,
Pennsylvania19073