Skip to content

Commit a8d1075

Browse files
tglsfdctglsfdc
committed
Add a time-of-preparation column to the pg_prepared_xacts view, per an
old suggestion by Oliver Jowett. Also, add a transaction column to the pg_locks view to show the xid of each transaction holding or awaiting locks; this allows prepared transactions to be properly associated with the locks they own. There was already a column named 'transaction', and I chose to rename it to 'transactionid' --- since this column is new in the current devel cycle there should be no backwards compatibility issue to worry about.
1 parent 66b0984 commit a8d1075

File tree

8 files changed

+106
-54
lines changed

8 files changed

+106
-54
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 32 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
<!--
22
Documentation of the system catalogs, directed toward PostgreSQL developers
3-
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.104 2005/06/17 22:32:41 tgl Exp $
3+
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.105 2005/06/18 19:33:41 tgl Exp $
44
-->
55

66
<chapter id="catalogs">
@@ -4090,7 +4090,7 @@
40904090
<literal>extend</>,
40914091
<literal>page</>,
40924092
<literal>tuple</>,
4093-
<literal>transaction</>,
4093+
<literal>transactionid</>,
40944094
<literal>object</>, or
40954095
<literal>userlock</>
40964096
</entry>
@@ -4132,7 +4132,7 @@
41324132
</entry>
41334133
</row>
41344134
<row>
4135-
<entry><structfield>transaction</structfield></entry>
4135+
<entry><structfield>transactionid</structfield></entry>
41364136
<entry><type>xid</type></entry>
41374137
<entry></entry>
41384138
<entry>
@@ -4168,13 +4168,21 @@
41684168
zero. NULL if the object is not a general database object
41694169
</entry>
41704170
</row>
4171+
<row>
4172+
<entry><structfield>transaction</structfield></entry>
4173+
<entry><type>xid</type></entry>
4174+
<entry></entry>
4175+
<entry>
4176+
ID of the transaction that is holding or awaiting this lock.
4177+
</entry>
4178+
</row>
41714179
<row>
41724180
<entry><structfield>pid</structfield></entry>
41734181
<entry><type>integer</type></entry>
41744182
<entry></entry>
41754183
<entry>
41764184
Process ID of the server process holding or awaiting this
4177-
lock. Zero if the lock is held by a prepared transaction.
4185+
lock. Null if the lock is held by a prepared transaction.
41784186
</entry>
41794187
</row>
41804188
<row>
@@ -4196,12 +4204,12 @@
41964204

41974205
<para>
41984206
<structfield>granted</structfield> is true in a row representing a lock
4199-
held by the indicated session. False indicates that this session is
4207+
held by the indicated transaction. False indicates that this transaction is
42004208
currently waiting to acquire this lock, which implies that some other
4201-
session is holding a conflicting lock mode on the same lockable object.
4202-
The waiting session will sleep until the other lock is released (or a
4203-
deadlock situation is detected). A single session can be waiting to acquire
4204-
at most one lock at a time.
4209+
transaction is holding a conflicting lock mode on the same lockable object.
4210+
The waiting transaction will sleep until the other lock is released (or a
4211+
deadlock situation is detected). A single transaction can be waiting to
4212+
acquire at most one lock at a time.
42054213
</para>
42064214

42074215
<para>
@@ -4253,6 +4261,13 @@
42534261
<structfield>procpid</structfield> column of the
42544262
<structname>pg_stat_activity</structname> view to get more
42554263
information on the session holding or waiting to hold the lock.
4264+
Also, if you are using prepared transactions, the
4265+
<structfield>transaction</> column can be joined to the
4266+
<structfield>transaction</structfield> column of the
4267+
<structname>pg_prepared_xacts</structname> view to get more
4268+
information on prepared transactions that hold locks.
4269+
(A prepared transaction can never be waiting for a lock,
4270+
but it continues to hold the locks it acquired while running.)
42564271
</para>
42574272

42584273
</sect1>
@@ -4306,6 +4321,14 @@
43064321
Global transaction identifier that was assigned to the transaction
43074322
</entry>
43084323
</row>
4324+
<row>
4325+
<entry><structfield>prepared</structfield></entry>
4326+
<entry><type>timestamp with time zone</type></entry>
4327+
<entry></entry>
4328+
<entry>
4329+
Time at which the transaction was prepared for commit
4330+
</entry>
4331+
</row>
43094332
<row>
43104333
<entry><structfield>owner</structfield></entry>
43114334
<entry><type>name</type></entry>

‎src/backend/access/transam/twophase.c

Lines changed: 29 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/access/transam/twophase.c,v 1.2 2005/06/18 05:21:09 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/access/transam/twophase.c,v 1.3 2005/06/18 19:33:41 tgl Exp $
1111
*
1212
* NOTES
1313
* Each global transaction is associated with a global transaction
@@ -104,6 +104,7 @@ int max_prepared_xacts = 50;
104104
typedef struct GlobalTransactionData
105105
{
106106
PGPROC proc; /* dummy proc */
107+
TimestampTz prepared_at; /* time of preparation */
107108
AclId owner; /* ID of user that executed the xact */
108109
TransactionId locking_xid; /* top-level XID of backend working on xact */
109110
bool valid; /* TRUE if fully prepared */
@@ -202,7 +203,8 @@ TwoPhaseShmemInit(void)
202203
* assuming that we can use very much backend context.
203204
*/
204205
GlobalTransaction
205-
MarkAsPreparing(TransactionId xid, Oid databaseid, char *gid, AclId owner)
206+
MarkAsPreparing(TransactionId xid, const char *gid,
207+
TimestampTz prepared_at, AclId owner, Oid databaseid)
206208
{
207209
GlobalTransaction gxact;
208210
int i;
@@ -278,6 +280,7 @@ MarkAsPreparing(TransactionId xid, Oid databaseid, char *gid, AclId owner)
278280
gxact->proc.subxids.overflowed = false;
279281
gxact->proc.subxids.nxids = 0;
280282

283+
gxact->prepared_at = prepared_at;
281284
gxact->owner = owner;
282285
gxact->locking_xid = xid;
283286
gxact->valid = false;
@@ -342,7 +345,7 @@ MarkAsPrepared(GlobalTransaction gxact)
342345
* Locate the prepared transaction and mark it busy for COMMIT or PREPARE.
343346
*/
344347
static GlobalTransaction
345-
LockGXact(char *gid, AclId user)
348+
LockGXact(const char *gid, AclId user)
346349
{
347350
int i;
348351

@@ -509,14 +512,16 @@ pg_prepared_xact(PG_FUNCTION_ARGS)
509512

510513
/* build tupdesc for result tuples */
511514
/* this had better match pg_prepared_xacts view in system_views.sql */
512-
tupdesc = CreateTemplateTupleDesc(4, false);
515+
tupdesc = CreateTemplateTupleDesc(5, false);
513516
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "transaction",
514517
XIDOID, -1, 0);
515518
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "gid",
516519
TEXTOID, -1, 0);
517-
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "ownerid",
520+
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "prepared",
521+
TIMESTAMPTZOID, -1, 0);
522+
TupleDescInitEntry(tupdesc, (AttrNumber) 4, "ownerid",
518523
INT4OID, -1, 0);
519-
TupleDescInitEntry(tupdesc, (AttrNumber) 4, "dbid",
524+
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "dbid",
520525
OIDOID, -1, 0);
521526

522527
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
@@ -540,8 +545,8 @@ pg_prepared_xact(PG_FUNCTION_ARGS)
540545
while (status->array != NULL && status->currIdx < status->ngxacts)
541546
{
542547
GlobalTransaction gxact = &status->array[status->currIdx++];
543-
Datum values[4];
544-
bool nulls[4];
548+
Datum values[5];
549+
bool nulls[5];
545550
HeapTuple tuple;
546551
Datum result;
547552

@@ -556,8 +561,9 @@ pg_prepared_xact(PG_FUNCTION_ARGS)
556561

557562
values[0] = TransactionIdGetDatum(gxact->proc.xid);
558563
values[1] = DirectFunctionCall1(textin, CStringGetDatum(gxact->gid));
559-
values[2] = Int32GetDatum(gxact->owner);
560-
values[3] = ObjectIdGetDatum(gxact->proc.databaseId);
564+
values[2] = TimestampTzGetDatum(gxact->prepared_at);
565+
values[3] = Int32GetDatum(gxact->owner);
566+
values[4] = ObjectIdGetDatum(gxact->proc.databaseId);
561567

562568
tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
563569
result = HeapTupleGetDatum(tuple);
@@ -636,14 +642,15 @@ TwoPhaseGetDummyProc(TransactionId xid)
636642
/*
637643
* Header for a 2PC state file
638644
*/
639-
#define TWOPHASE_MAGIC 0x57F94530 /* format identifier */
645+
#define TWOPHASE_MAGIC 0x57F94531 /* format identifier */
640646

641647
typedef struct TwoPhaseFileHeader
642648
{
643649
uint32 magic; /* format identifier */
644650
uint32 total_len; /* actual file length */
645651
TransactionId xid; /* original transaction XID */
646652
Oid database; /* OID of database it was in */
653+
TimestampTz prepared_at; /* time of preparation */
647654
AclId owner; /* user running the transaction */
648655
int32 nsubxacts; /* number of following subxact XIDs */
649656
int32 ncommitrels; /* number of delete-on-commit rels */
@@ -741,8 +748,9 @@ StartPrepare(GlobalTransaction gxact)
741748
hdr.magic = TWOPHASE_MAGIC;
742749
hdr.total_len = 0; /* EndPrepare will fill this in */
743750
hdr.xid = xid;
744-
hdr.database = MyDatabaseId;
745-
hdr.owner = GetUserId();
751+
hdr.database = gxact->proc.databaseId;
752+
hdr.prepared_at = gxact->prepared_at;
753+
hdr.owner = gxact->owner;
746754
hdr.nsubxacts = xactGetCommittedChildren(&children);
747755
hdr.ncommitrels = smgrGetPendingDeletes(true, &commitrels);
748756
hdr.nabortrels = smgrGetPendingDeletes(false, &abortrels);
@@ -1046,7 +1054,7 @@ ReadTwoPhaseFile(TransactionId xid)
10461054
* FinishPreparedTransaction: execute COMMIT PREPARED or ROLLBACK PREPARED
10471055
*/
10481056
void
1049-
FinishPreparedTransaction(char *gid, bool isCommit)
1057+
FinishPreparedTransaction(const char *gid, bool isCommit)
10501058
{
10511059
GlobalTransaction gxact;
10521060
TransactionId xid;
@@ -1474,15 +1482,20 @@ RecoverPreparedTransactions(void)
14741482

14751483
/*
14761484
* Reconstruct subtrans state for the transaction --- needed
1477-
* because pg_subtrans is not preserved over a restart
1485+
* because pg_subtrans is not preserved over a restart. Note
1486+
* that we are linking all the subtransactions directly to the
1487+
* top-level XID; there may originally have been a more complex
1488+
* hierarchy, but there's no need to restore that exactly.
14781489
*/
14791490
for (i = 0; i < hdr->nsubxacts; i++)
14801491
SubTransSetParent(subxids[i], xid);
14811492

14821493
/*
14831494
* Recreate its GXACT and dummy PGPROC
14841495
*/
1485-
gxact = MarkAsPreparing(xid, hdr->database, hdr->gid, hdr->owner);
1496+
gxact = MarkAsPreparing(xid, hdr->gid,
1497+
hdr->prepared_at,
1498+
hdr->owner, hdr->database);
14861499
GXactLoadSubxactData(gxact, hdr->nsubxacts, subxids);
14871500
MarkAsPrepared(gxact);
14881501

‎src/backend/access/transam/xact.c

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.205 2005/06/17 22:32:42 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.206 2005/06/18 19:33:41 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -1630,6 +1630,9 @@ PrepareTransaction(void)
16301630
TransactionState s = CurrentTransactionState;
16311631
TransactionId xid = GetCurrentTransactionId();
16321632
GlobalTransaction gxact;
1633+
TimestampTz prepared_at;
1634+
AbsoluteTime PreparedSec; /* integer part */
1635+
int PreparedUSec; /* microsecond part */
16331636

16341637
ShowTransactionState("PrepareTransaction");
16351638

@@ -1692,14 +1695,18 @@ PrepareTransaction(void)
16921695
*/
16931696
s->state = TRANS_PREPARE;
16941697

1698+
PreparedSec = GetCurrentAbsoluteTimeUsec(&PreparedUSec);
1699+
prepared_at = AbsoluteTimeUsecToTimestampTz(PreparedSec, PreparedUSec);
1700+
16951701
/* Tell bufmgr and smgr to prepare for commit */
16961702
BufmgrCommit();
16971703

16981704
/*
16991705
* Reserve the GID for this transaction. This could fail if the
17001706
* requested GID is invalid or already in use.
17011707
*/
1702-
gxact = MarkAsPreparing(xid, MyDatabaseId, prepareGID, GetUserId());
1708+
gxact = MarkAsPreparing(xid, prepareGID, prepared_at,
1709+
GetUserId(), MyDatabaseId);
17031710
prepareGID = NULL;
17041711

17051712
/*

‎src/backend/catalog/system_views.sql

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2005, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.14 2005/06/17 22:32:43 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.15 2005/06/18 19:33:42 tgl Exp $
77
*/
88

99
CREATE VIEW pg_user AS
@@ -106,15 +106,16 @@ CREATE VIEW pg_locks AS
106106
SELECT *
107107
FROM pg_lock_status() AS L
108108
(locktype text, database oid, relation oid, page int4, tuple int2,
109-
transaction xid, classid oid, objid oid, objsubid int2,
110-
pid int4, mode text, granted boolean);
109+
transactionid xid, classid oid, objid oid, objsubid int2,
110+
transaction xid, pid int4, mode text, granted boolean);
111111

112112
CREATE VIEW pg_prepared_xacts AS
113-
SELECT P.transaction, P.gid, U.usename AS owner, D.datname AS database
113+
SELECT P.transaction, P.gid, P.prepared,
114+
U.usename AS owner, D.datname AS database
114115
FROM pg_prepared_xact() AS P
115-
(transaction xid, gid text, ownerid int4, dbid oid)
116-
LEFT JOIN pg_database D ON P.dbid = D.oid
117-
LEFT JOIN pg_shadow U ON P.ownerid = U.usesysid;
116+
(transaction xid, gid text, prepared timestamptz, ownerid int4, dbid oid)
117+
LEFT JOIN pg_shadow U ON P.ownerid = U.usesysid
118+
LEFT JOIN pg_database D ON P.dbid = D.oid;
118119

119120
CREATE VIEW pg_settings AS
120121
SELECT *

‎src/backend/utils/adt/lockfuncs.c

Lines changed: 18 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Copyright (c) 2002-2005, PostgreSQL Global Development Group
77
*
88
* IDENTIFICATION
9-
* $PostgreSQL: pgsql/src/backend/utils/adt/lockfuncs.c,v 1.18 2005/05/17 21:46:10 tgl Exp $
9+
* $PostgreSQL: pgsql/src/backend/utils/adt/lockfuncs.c,v 1.19 2005/06/18 19:33:42 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -26,7 +26,7 @@ static const char * const LockTagTypeNames[] = {
2626
"extend",
2727
"page",
2828
"tuple",
29-
"transaction",
29+
"transactionid",
3030
"object",
3131
"userlock"
3232
};
@@ -64,7 +64,7 @@ pg_lock_status(PG_FUNCTION_ARGS)
6464

6565
/* build tupdesc for result tuples */
6666
/* this had better match pg_locks view in system_views.sql */
67-
tupdesc = CreateTemplateTupleDesc(12, false);
67+
tupdesc = CreateTemplateTupleDesc(13, false);
6868
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "locktype",
6969
TEXTOID, -1, 0);
7070
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "database",
@@ -75,19 +75,21 @@ pg_lock_status(PG_FUNCTION_ARGS)
7575
INT4OID, -1, 0);
7676
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "tuple",
7777
INT2OID, -1, 0);
78-
TupleDescInitEntry(tupdesc, (AttrNumber) 6, "transaction",
78+
TupleDescInitEntry(tupdesc, (AttrNumber) 6, "transactionid",
7979
XIDOID, -1, 0);
8080
TupleDescInitEntry(tupdesc, (AttrNumber) 7, "classid",
8181
OIDOID, -1, 0);
8282
TupleDescInitEntry(tupdesc, (AttrNumber) 8, "objid",
8383
OIDOID, -1, 0);
8484
TupleDescInitEntry(tupdesc, (AttrNumber) 9, "objsubid",
8585
INT2OID, -1, 0);
86-
TupleDescInitEntry(tupdesc, (AttrNumber) 10, "pid",
86+
TupleDescInitEntry(tupdesc, (AttrNumber) 10, "transaction",
87+
XIDOID, -1, 0);
88+
TupleDescInitEntry(tupdesc, (AttrNumber) 11, "pid",
8789
INT4OID, -1, 0);
88-
TupleDescInitEntry(tupdesc, (AttrNumber) 11, "mode",
90+
TupleDescInitEntry(tupdesc, (AttrNumber) 12, "mode",
8991
TEXTOID, -1, 0);
90-
TupleDescInitEntry(tupdesc, (AttrNumber) 12, "granted",
92+
TupleDescInitEntry(tupdesc, (AttrNumber) 13, "granted",
9193
BOOLOID, -1, 0);
9294

9395
funcctx->tuple_desc = BlessTupleDesc(tupdesc);
@@ -118,8 +120,8 @@ pg_lock_status(PG_FUNCTION_ARGS)
118120
LOCKMODE mode = 0;
119121
const char *locktypename;
120122
char tnbuf[32];
121-
Datum values[12];
122-
char nulls[12];
123+
Datum values[13];
124+
char nulls[13];
123125
HeapTuple tuple;
124126
Datum result;
125127

@@ -249,10 +251,14 @@ pg_lock_status(PG_FUNCTION_ARGS)
249251
break;
250252
}
251253

252-
values[9] = Int32GetDatum(proc->pid);
253-
values[10] = DirectFunctionCall1(textin,
254+
values[9] = TransactionIdGetDatum(proc->xid);
255+
if (proc->pid != 0)
256+
values[10] = Int32GetDatum(proc->pid);
257+
else
258+
nulls[10] = 'n';
259+
values[11] = DirectFunctionCall1(textin,
254260
CStringGetDatum(GetLockmodeName(mode)));
255-
values[11] = BoolGetDatum(granted);
261+
values[12] = BoolGetDatum(granted);
256262

257263
tuple = heap_formtuple(funcctx->tuple_desc, values, nulls);
258264
result = HeapTupleGetDatum(tuple);

0 commit comments

Comments
 (0)