Thread: obtaining row locking information
Hi,
With a help from Bruce, I wrote a small function which returns row
locking information(see attached file if you are interested). Here is
a sample result:
test=# select * from pgrowlocks('t1');locked_row | lock_type | locker | multi
------------+-----------+--------+------- (0,1) | Shared | 1 | t (0,3) | Exclusive | 575 | f
(2 rows)
I think it will be more usefull if actual xids are shown in the case
"locker" is a multixid. It seems GetMultiXactIdMembers() does the
job. Unfortunately that is a static funtcion, however. Is there any
chance GetMultiXactIdMembers() becomes public funtion?
--
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
theabove* copyright notice and this paragraph and the following two* paragraphs appear in all copies.** IN NO EVENT
SHALLTHE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,* INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING*
LOSTPROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS* DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS
BEENADVISED* OF THE POSSIBILITY OF SUCH DAMAGE.** THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT*
LIMITEDTO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR* A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED
HEREUNDERIS 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/transam.h"
#include "catalog/namespace.h"
#include "catalog/pg_type.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
lockto 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) values[i++] =
pstrdup("true"); else values[i++] = pstrdup("false");
#else values[i++] = pstrdup("false");
#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);
}
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> With a help from Bruce, I wrote a small function which returns row
> locking information(see attached file if you are interested).
Scanning the whole table seems a bit slow :-(
There is another possibility: in CVS tip, anyone who is actually blocked
on a row lock will be holding a tuple lock that shows exactly what they
are waiting for. For example:
Session 1:
regression=# begin;
BEGIN
regression=# select * from int4_tbl where f1 = 123456 for update; f1
--------123456
(1 row)
Session 2:
<< same as above, leaving session 2 blocked >
Session 1:
regression=# select * from pg_locks; locktype | database | relation | page | tuple | transactionid | classid |
objid| objsubid | transaction | pid | mode | granted
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+-----------------+---------transactionid
| | | | | 14575 | | | | 14576 | 2501 | ShareLock
|ftuple | 48344 | 48369 | 0 | 2 | | | | | 14576 | 2501
|ExclusiveLock | trelation | 48344 | 48369 | | | | | | |
14576 | 2501 | AccessShareLock | trelation | 48344 | 48369 | | | | |
| | 14576 | 2501 | RowShareLock | ttransactionid | | | | |
14576| | | | 14576 | 2501 | ExclusiveLock | trelation | 48344 | 10339 |
| | | | | | 14575 | 2503 | AccessShareLock | trelation | 48344
| 48369 | | | | | | | 14575 | 2503 | AccessShareLock |
trelation | 48344 | 48369 | | | | | | | 14575 | 2503 |
RowShareLock | ttransactionid | | | | | 14575 | | | |
14575 | 2503 | ExclusiveLock | t
(9 rows)
Session 2 (XID 14576) is blocked on session 1 (XID 14575) according to
the first row of this output. The second row shows the exact tuple
that it is after.
This isn't an amazingly user-friendly way of displaying things, of
course, but maybe somebody could make a function that would show it
better using pg_locks as input.
> I think it will be more usefull if actual xids are shown in the case
> "locker" is a multixid. It seems GetMultiXactIdMembers() does the
> job. Unfortunately that is a static funtcion, however. Is there any
> chance GetMultiXactIdMembers() becomes public funtion?
No particular objection here.
regards, tom lane
> Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > With a help from Bruce, I wrote a small function which returns row > > locking information(see attached file if you are interested). > > Scanning the whole table seems a bit slow :-( Yes, but I couldn't find any other way. > There is another possibility: in CVS tip, anyone who is actually blocked > on a row lock will be holding a tuple lock that shows exactly what they > are waiting for. For example: > > Session 1: > > regression=# begin; > BEGIN > regression=# select * from int4_tbl where f1 = 123456 for update; > f1 > -------- > 123456 > (1 row) > > Session 2: > > << same as above, leaving session 2 blocked > > > Session 1: > > regression=# select * from pg_locks; > locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted > ---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+-----------------+--------- > transactionid | | | | | 14575 | | | | 14576 | 2501 |ShareLock | f > tuple | 48344 | 48369 | 0 | 2 | | | | | 14576 | 2501 |ExclusiveLock | t > relation | 48344 | 48369 | | | | | | | 14576 | 2501 |AccessShareLock | t > relation | 48344 | 48369 | | | | | | | 14576 | 2501 |RowShareLock | t > transactionid | | | | | 14576 | | | | 14576 | 2501 |ExclusiveLock | t > relation | 48344 | 10339 | | | | | | | 14575 | 2503 |AccessShareLock | t > relation | 48344 | 48369 | | | | | | | 14575 | 2503 |AccessShareLock | t > relation | 48344 | 48369 | | | | | | | 14575 | 2503 |RowShareLock | t > transactionid | | | | | 14575 | | | | 14575 | 2503 |ExclusiveLock | t > (9 rows) > > Session 2 (XID 14576) is blocked on session 1 (XID 14575) according to > the first row of this output. The second row shows the exact tuple > that it is after. > > This isn't an amazingly user-friendly way of displaying things, of > course, but maybe somebody could make a function that would show it > better using pg_locks as input. If I understand correctly, it seems the above method does show a locked row's TID which does not block someone else. That is a little bit different from what I expcted. > > I think it will be more usefull if actual xids are shown in the case > > "locker" is a multixid. It seems GetMultiXactIdMembers() does the > > job. Unfortunately that is a static funtcion, however. Is there any > > chance GetMultiXactIdMembers() becomes public funtion? > > No particular objection here. -- Tatsuo Ishii
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> If I understand correctly, it seems the above method does show a
> locked row's TID which does not block someone else. That is a little
> bit different from what I expcted.
Well, it *could* be blocking someone else. If there were more than one
waiter for the same tuple, one of them would be holding the tuple lock
(and blocked on the transaction ID of the actual holder of the tuple),
and the other ones would be blocked on the first waiter's tuple lock.
We put this in so that the full lock manager rules would be used to
arbitrate conflicts between shared and exclusive lockers of a tuple.
The tuple lock is being used to manage the grant order and ensure that
a would-be exclusive locker doesn't get "starved out" indefinitely if
there is a continuous chain of shared-lock requests. See the notes in
heap_lock_tuple().
regards, tom lane
> Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > If I understand correctly, it seems the above method does show a > > locked row's TID which does not block someone else. That is a little > > bit different from what I expcted. > > Well, it *could* be blocking someone else. If there were more than one > waiter for the same tuple, one of them would be holding the tuple lock > (and blocked on the transaction ID of the actual holder of the tuple), > and the other ones would be blocked on the first waiter's tuple lock. > We put this in so that the full lock manager rules would be used to > arbitrate conflicts between shared and exclusive lockers of a tuple. > The tuple lock is being used to manage the grant order and ensure that > a would-be exclusive locker doesn't get "starved out" indefinitely if > there is a continuous chain of shared-lock requests. See the notes in > heap_lock_tuple(). > > regards, tom lane Sorry, I meant: If I understand correctly, it seems the above method does *not* show a locked row's TID which does not block someone else. That is a little bit different from what I expcted. -- Tatsuo Ishii
On Mon, Aug 08, 2005 at 10:26:12AM -0400, Tom Lane wrote: > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > If I understand correctly, it seems the above method does show a > > locked row's TID which does not block someone else. That is a little > > bit different from what I expcted. > > Well, it *could* be blocking someone else. If there were more than one > waiter for the same tuple, one of them would be holding the tuple lock > (and blocked on the transaction ID of the actual holder of the tuple), > and the other ones would be blocked on the first waiter's tuple lock. All in all, Tatsuo is right in that there's no way to know what tuples are locked without scanning the whole table. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "Those who use electric razors are infidels destined to burn in hell while we drink from rivers of beer, download free vids and mingle with naked well shaved babes." (http://slashdot.org/comments.pl?sid=44793&cid=4647152)
On Sun, Aug 07, 2005 at 09:46:08PM +0900, Tatsuo Ishii wrote: > With a help from Bruce, I wrote a small function which returns row > locking information(see attached file if you are interested). A quick note: it's easier to build tuples using heap_form_tuple instead of BuildTupleFromCStrings. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "Endurecerse, pero jamás perder la ternura" (E. Guevara)
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> All in all, Tatsuo is right in that there's no way to know what tuples
> are locked without scanning the whole table.
Sure. I was just questioning how much one needs to know that. It seems
to me that the only time you really care is when you are investigating
a hangup, and then you can get useful info from pg_locks.
regards, tom lane
> Hi,
>
> With a help from Bruce, I wrote a small function which returns row
> locking information(see attached file if you are interested). Here is
> a sample result:
>
> test=# select * from pgrowlocks('t1');
> locked_row | lock_type | locker | multi
> ------------+-----------+--------+-------
> (0,1) | Shared | 1 | t
> (0,3) | Exclusive | 575 | f
> (2 rows)
>
> I think it will be more usefull if actual xids are shown in the case
> "locker" is a multixid. It seems GetMultiXactIdMembers() does the
> job. Unfortunately that is a static funtcion, however. Is there any
> chance GetMultiXactIdMembers() becomes public funtion?
I enhanced pgrowlocks() to use GetMultiXactIdMembers() so that it
displays each xid belonging to particular multi xid (see attached
source code).
test=# select * from pgrowlocks('t1');locked_row | lock_type | locker | multi | xids
------------+-----------+--------+-------+----------- (0,1) | Shared | 3 | t | {646,647}
(1 row)
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?
--
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
theabove* copyright notice and this paragraph and the following two* paragraphs appear in all copies.** IN NO EVENT
SHALLTHE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,* INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING*
LOSTPROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS* DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS
BEENADVISED* OF THE POSSIBILITY OF SUCH DAMAGE.** THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT*
LIMITEDTO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR* A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED
HEREUNDERIS 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 "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
lockto 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;
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];
snprintf(buf, NCHARS, "%d", xids[j]); strcat(values[i], buf); if ((j+1) != nxids)
{ strcat(values[i], ","); } } 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);
}
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.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"El que vive para el futuro es un iluso, y el que vive para el pasado,
un imbécil" (Luis Adler, "Los tripulantes de la noche")
> 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?
--
Tatsuo Ishii
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.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Thou shalt not follow the NULL pointer, for chaos and madness await
thee at its end." (2nd Commandment for C programmers)
> 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
theabove* copyright notice and this paragraph and the following two* paragraphs appear in all copies.** IN NO EVENT
SHALLTHE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,* INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING*
LOSTPROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS* DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS
BEENADVISED* OF THE POSSIBILITY OF SUCH DAMAGE.** THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT*
LIMITEDTO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR* A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED
HEREUNDERIS 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
lockto 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);
}
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
> 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
>
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> 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?
> if (xid == 0) /* never match dummy PGPROCs */
> return NULL;
I think this test should be against InvalidTransactionId, not "0", and
the comment is wrong (you are suppressing matches against idle PGPROCs).
Also note the comment at the top of the function: once you release
ProcArrayLock you have no guarantee that the result means anything at
all; and unlike ProcSendSignal, you have no reason to think that the
target backend can't quit before you get another cycle. It might be
better to return the pid directly rather than assuming it'll still be
meaningful to indirect through a returned pointer.
Also, what are you going to do about prepared transactions? They can
hold locks but they don't have PIDs. On the whole, I'm not sure this
is a good idea at all, because of that.
regards, tom lane
> Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > 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? > > > if (xid == 0) /* never match dummy PGPROCs */ > > return NULL; > > I think this test should be against InvalidTransactionId, not "0", and > the comment is wrong (you are suppressing matches against idle PGPROCs). > > Also note the comment at the top of the function: once you release > ProcArrayLock you have no guarantee that the result means anything at > all; and unlike ProcSendSignal, you have no reason to think that the > target backend can't quit before you get another cycle. It might be > better to return the pid directly rather than assuming it'll still be > meaningful to indirect through a returned pointer. Agreed. > Also, what are you going to do about prepared transactions? They can > hold locks but they don't have PIDs. On the whole, I'm not sure this > is a good idea at all, because of that. For prepared transactions, just showing "0" pids are enough, I think. Assuming that in practice most transactions are not prepared ones, I think the function is not perfect, but is usefull enough for most DBAs. -- Tatsuo Ishii
On Fri, Aug 19, 2005 at 09:19:24PM +0900, Tatsuo Ishii wrote:
> > Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > > 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?
I don't think it is critical for it to return valid answers for
subtransactions, but maybe you should add a note in the commentary about
it:
> /*
> * BackendXidGetPid -- get a backend's pid given its XID
> *
> * Returns 0 if not found or it's a prepared transaction. 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 ... * * Only main transaction Ids are considered. This
functionis mainly * useful for determining what backend owns a lock.
> */
> int
> BackendXidGetPid(TransactionId xid)
> {
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"The problem with the facetime model is not just that it's demoralizing, but
that the people pretending to work interrupt the ones actually working."
(Paul Graham)
> On Fri, Aug 19, 2005 at 09:19:24PM +0900, Tatsuo Ishii wrote:
> > > Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > > > 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?
>
> I don't think it is critical for it to return valid answers for
> subtransactions, but maybe you should add a note in the commentary about
> it:
>
> > /*
> > * BackendXidGetPid -- get a backend's pid given its XID
> > *
> > * Returns 0 if not found or it's a prepared transaction. 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 ...
> *
> * Only main transaction Ids are considered. This function is mainly
> * useful for determining what backend owns a lock.
> > */
> > int
> > BackendXidGetPid(TransactionId xid)
> > {
Good point. I will add your comments.
--
Tatsuo Ishii
Tatsuo, have you developed a new version of this for 8.2? --------------------------------------------------------------------------- Tatsuo Ishii wrote: > > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > > 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? > > > > > if (xid == 0) /* never match dummy PGPROCs */ > > > return NULL; > > > > I think this test should be against InvalidTransactionId, not "0", and > > the comment is wrong (you are suppressing matches against idle PGPROCs). > > > > Also note the comment at the top of the function: once you release > > ProcArrayLock you have no guarantee that the result means anything at > > all; and unlike ProcSendSignal, you have no reason to think that the > > target backend can't quit before you get another cycle. It might be > > better to return the pid directly rather than assuming it'll still be > > meaningful to indirect through a returned pointer. > > Agreed. > > > Also, what are you going to do about prepared transactions? They can > > hold locks but they don't have PIDs. On the whole, I'm not sure this > > is a good idea at all, because of that. > > For prepared transactions, just showing "0" pids are enough, I > think. Assuming that in practice most transactions are not prepared > ones, I think the function is not perfect, but is usefull enough for > most DBAs. > -- > Tatsuo Ishii > -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. +
I have attached pgrowlocks tested under current. -- Tatsuo Ishii SRA OSS, Inc. Japan > Tatsuo, have you developed a new version of this for 8.2? > > --------------------------------------------------------------------------- > > Tatsuo Ishii wrote: > > > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > > > 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? > > > > > > > if (xid == 0) /* never match dummy PGPROCs */ > > > > return NULL; > > > > > > I think this test should be against InvalidTransactionId, not "0", and > > > the comment is wrong (you are suppressing matches against idle PGPROCs). > > > > > > Also note the comment at the top of the function: once you release > > > ProcArrayLock you have no guarantee that the result means anything at > > > all; and unlike ProcSendSignal, you have no reason to think that the > > > target backend can't quit before you get another cycle. It might be > > > better to return the pid directly rather than assuming it'll still be > > > meaningful to indirect through a returned pointer. > > > > Agreed. > > > > > Also, what are you going to do about prepared transactions? They can > > > hold locks but they don't have PIDs. On the whole, I'm not sure this > > > is a good idea at all, because of that. > > > > For prepared transactions, just showing "0" pids are enough, I > > think. Assuming that in practice most transactions are not prepared > > ones, I think the function is not perfect, but is usefull enough for > > most DBAs. > > -- > > Tatsuo Ishii > > > > -- > Bruce Momjian http://candle.pha.pa.us > SRA OSS, Inc. http://www.sraoss.com > > + If your life is a hard drive, Christ can be your backup. + >
Tatsuo, are you planning to add this to CVS HEAD? --------------------------------------------------------------------------- Tatsuo Ishii wrote: > I have attached pgrowlocks tested under current. > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > > > Tatsuo, have you developed a new version of this for 8.2? > > > > --------------------------------------------------------------------------- > > > > Tatsuo Ishii wrote: > > > > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > > > > 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? > > > > > > > > > if (xid == 0) /* never match dummy PGPROCs */ > > > > > return NULL; > > > > > > > > I think this test should be against InvalidTransactionId, not "0", and > > > > the comment is wrong (you are suppressing matches against idle PGPROCs). > > > > > > > > Also note the comment at the top of the function: once you release > > > > ProcArrayLock you have no guarantee that the result means anything at > > > > all; and unlike ProcSendSignal, you have no reason to think that the > > > > target backend can't quit before you get another cycle. It might be > > > > better to return the pid directly rather than assuming it'll still be > > > > meaningful to indirect through a returned pointer. > > > > > > Agreed. > > > > > > > Also, what are you going to do about prepared transactions? They can > > > > hold locks but they don't have PIDs. On the whole, I'm not sure this > > > > is a good idea at all, because of that. > > > > > > For prepared transactions, just showing "0" pids are enough, I > > > think. Assuming that in practice most transactions are not prepared > > > ones, I think the function is not perfect, but is usefull enough for > > > most DBAs. > > > -- > > > Tatsuo Ishii > > > > > > > -- > > Bruce Momjian http://candle.pha.pa.us > > SRA OSS, Inc. http://www.sraoss.com > > > > + If your life is a hard drive, Christ can be your backup. + > > [ Attachment, skipping... ] -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> Tatsuo, are you planning to add this to CVS HEAD? Yes, I would like to add if there's no objection. -- Tatsuo Ishii SRA OSS, Inc. Japan > --------------------------------------------------------------------------- > > Tatsuo Ishii wrote: > > I have attached pgrowlocks tested under current. > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > > > Tatsuo, have you developed a new version of this for 8.2? > > > > > > --------------------------------------------------------------------------- > > > > > > Tatsuo Ishii wrote: > > > > > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > > > > > 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? > > > > > > > > > > > if (xid == 0) /* never match dummy PGPROCs */ > > > > > > return NULL; > > > > > > > > > > I think this test should be against InvalidTransactionId, not "0", and > > > > > the comment is wrong (you are suppressing matches against idle PGPROCs). > > > > > > > > > > Also note the comment at the top of the function: once you release > > > > > ProcArrayLock you have no guarantee that the result means anything at > > > > > all; and unlike ProcSendSignal, you have no reason to think that the > > > > > target backend can't quit before you get another cycle. It might be > > > > > better to return the pid directly rather than assuming it'll still be > > > > > meaningful to indirect through a returned pointer. > > > > > > > > Agreed. > > > > > > > > > Also, what are you going to do about prepared transactions? They can > > > > > hold locks but they don't have PIDs. On the whole, I'm not sure this > > > > > is a good idea at all, because of that. > > > > > > > > For prepared transactions, just showing "0" pids are enough, I > > > > think. Assuming that in practice most transactions are not prepared > > > > ones, I think the function is not perfect, but is usefull enough for > > > > most DBAs. > > > > -- > > > > Tatsuo Ishii > > > > > > > > > > -- > > > Bruce Momjian http://candle.pha.pa.us > > > SRA OSS, Inc. http://www.sraoss.com > > > > > > + If your life is a hard drive, Christ can be your backup. + > > > > > [ Attachment, skipping... ] > > -- > Bruce Momjian http://candle.pha.pa.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + >
Tatsuo Ishii wrote: > > Tatsuo, are you planning to add this to CVS HEAD? > > Yes, I would like to add if there's no objection. I have heard no comment, and it looks useful, so please add it at your convenience. --------------------------------------------------------------------------- > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > > > --------------------------------------------------------------------------- > > > > Tatsuo Ishii wrote: > > > I have attached pgrowlocks tested under current. > > > -- > > > Tatsuo Ishii > > > SRA OSS, Inc. Japan > > > > > > > Tatsuo, have you developed a new version of this for 8.2? > > > > > > > > --------------------------------------------------------------------------- > > > > > > > > Tatsuo Ishii wrote: > > > > > > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > > > > > > 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? > > > > > > > > > > > > > if (xid == 0) /* never match dummy PGPROCs */ > > > > > > > return NULL; > > > > > > > > > > > > I think this test should be against InvalidTransactionId, not "0", and > > > > > > the comment is wrong (you are suppressing matches against idle PGPROCs). > > > > > > > > > > > > Also note the comment at the top of the function: once you release > > > > > > ProcArrayLock you have no guarantee that the result means anything at > > > > > > all; and unlike ProcSendSignal, you have no reason to think that the > > > > > > target backend can't quit before you get another cycle. It might be > > > > > > better to return the pid directly rather than assuming it'll still be > > > > > > meaningful to indirect through a returned pointer. > > > > > > > > > > Agreed. > > > > > > > > > > > Also, what are you going to do about prepared transactions? They can > > > > > > hold locks but they don't have PIDs. On the whole, I'm not sure this > > > > > > is a good idea at all, because of that. > > > > > > > > > > For prepared transactions, just showing "0" pids are enough, I > > > > > think. Assuming that in practice most transactions are not prepared > > > > > ones, I think the function is not perfect, but is usefull enough for > > > > > most DBAs. > > > > > -- > > > > > Tatsuo Ishii > > > > > > > > > > > > > -- > > > > Bruce Momjian http://candle.pha.pa.us > > > > SRA OSS, Inc. http://www.sraoss.com > > > > > > > > + If your life is a hard drive, Christ can be your backup. + > > > > > > > > [ Attachment, skipping... ] > > > > -- > > Bruce Momjian http://candle.pha.pa.us > > EnterpriseDB http://www.enterprisedb.com > > > > + If your life is a hard drive, Christ can be your backup. + > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> Tatsuo Ishii wrote: > > > Tatsuo, are you planning to add this to CVS HEAD? > > > > Yes, I would like to add if there's no objection. > > I have heard no comment, and it looks useful, so please add it at your > convenience. Ok, done. -- Tatsuo Ishii SRA OSS, Inc. Japan > --------------------------------------------------------------------------- > > > > -- > > Tatsuo Ishii > > SRA OSS, Inc. Japan > > > > > --------------------------------------------------------------------------- > > > > > > Tatsuo Ishii wrote: > > > > I have attached pgrowlocks tested under current. > > > > -- > > > > Tatsuo Ishii > > > > SRA OSS, Inc. Japan > > > > > > > > > Tatsuo, have you developed a new version of this for 8.2? > > > > > > > > > > --------------------------------------------------------------------------- > > > > > > > > > > Tatsuo Ishii wrote: > > > > > > > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > > > > > > > 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? > > > > > > > > > > > > > > > if (xid == 0) /* never match dummy PGPROCs */ > > > > > > > > return NULL; > > > > > > > > > > > > > > I think this test should be against InvalidTransactionId, not "0", and > > > > > > > the comment is wrong (you are suppressing matches against idle PGPROCs). > > > > > > > > > > > > > > Also note the comment at the top of the function: once you release > > > > > > > ProcArrayLock you have no guarantee that the result means anything at > > > > > > > all; and unlike ProcSendSignal, you have no reason to think that the > > > > > > > target backend can't quit before you get another cycle. It might be > > > > > > > better to return the pid directly rather than assuming it'll still be > > > > > > > meaningful to indirect through a returned pointer. > > > > > > > > > > > > Agreed. > > > > > > > > > > > > > Also, what are you going to do about prepared transactions? They can > > > > > > > hold locks but they don't have PIDs. On the whole, I'm not sure this > > > > > > > is a good idea at all, because of that. > > > > > > > > > > > > For prepared transactions, just showing "0" pids are enough, I > > > > > > think. Assuming that in practice most transactions are not prepared > > > > > > ones, I think the function is not perfect, but is usefull enough for > > > > > > most DBAs. > > > > > > -- > > > > > > Tatsuo Ishii > > > > > > > > > > > > > > > > -- > > > > > Bruce Momjian http://candle.pha.pa.us > > > > > SRA OSS, Inc. http://www.sraoss.com > > > > > > > > > > + If your life is a hard drive, Christ can be your backup. + > > > > > > > > > > > [ Attachment, skipping... ] > > > > > > -- > > > Bruce Momjian http://candle.pha.pa.us > > > EnterpriseDB http://www.enterprisedb.com > > > > > > + If your life is a hard drive, Christ can be your backup. + > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > -- > Bruce Momjian http://candle.pha.pa.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >