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