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.

4 files changed, 64 insertions, 37 deletions

diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c
index 388d9352ce9..30bc21b5244 100644
--- a/src/backend/access/transam/twophase.c
+++ b/src/backend/access/transam/twophase.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *		$PostgreSQL: pgsql/src/backend/access/transam/twophase.c,v 1.2 2005/06/18 05:21:09 tgl Exp $
+ *		$PostgreSQL: pgsql/src/backend/access/transam/twophase.c,v 1.3 2005/06/18 19:33:41 tgl Exp $
  *
  * NOTES
  *		Each global transaction is associated with a global transaction
@@ -104,6 +104,7 @@ int max_prepared_xacts = 50;
 typedef struct GlobalTransactionData
 {
 	PGPROC		proc;			/* dummy proc */
+	TimestampTz	prepared_at;	/* time of preparation */
 	AclId		owner;			/* ID of user that executed the xact */
 	TransactionId locking_xid;	/* top-level XID of backend working on xact */
 	bool		valid;			/* TRUE if fully prepared */
@@ -202,7 +203,8 @@ TwoPhaseShmemInit(void)
  * assuming that we can use very much backend context.
  */
 GlobalTransaction
-MarkAsPreparing(TransactionId xid, Oid databaseid, char *gid, AclId owner)
+MarkAsPreparing(TransactionId xid, const char *gid,
+				TimestampTz prepared_at, AclId owner, Oid databaseid)
 {
 	GlobalTransaction	gxact;
 	int i;
@@ -278,6 +280,7 @@ MarkAsPreparing(TransactionId xid, Oid databaseid, char *gid, AclId owner)
 	gxact->proc.subxids.overflowed = false;
 	gxact->proc.subxids.nxids = 0;
 
+	gxact->prepared_at = prepared_at;
 	gxact->owner = owner;
 	gxact->locking_xid = xid;
 	gxact->valid = false;
@@ -342,7 +345,7 @@ MarkAsPrepared(GlobalTransaction gxact)
  *		Locate the prepared transaction and mark it busy for COMMIT or PREPARE.
  */
 static GlobalTransaction
-LockGXact(char *gid, AclId user)
+LockGXact(const char *gid, AclId user)
 {
 	int i;
 
@@ -509,14 +512,16 @@ pg_prepared_xact(PG_FUNCTION_ARGS)
 
 		/* build tupdesc for result tuples */
 		/* this had better match pg_prepared_xacts view in system_views.sql */
-		tupdesc = CreateTemplateTupleDesc(4, false);
+		tupdesc = CreateTemplateTupleDesc(5, false);
 		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "transaction",
 						   XIDOID, -1, 0);
 		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "gid",
 						   TEXTOID, -1, 0);
-		TupleDescInitEntry(tupdesc, (AttrNumber) 3, "ownerid",
+		TupleDescInitEntry(tupdesc, (AttrNumber) 3, "prepared",
+						   TIMESTAMPTZOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 4, "ownerid",
 						   INT4OID, -1, 0);
-		TupleDescInitEntry(tupdesc, (AttrNumber) 4, "dbid",
+		TupleDescInitEntry(tupdesc, (AttrNumber) 5, "dbid",
 						   OIDOID, -1, 0);
 
 		funcctx->tuple_desc = BlessTupleDesc(tupdesc);
@@ -540,8 +545,8 @@ pg_prepared_xact(PG_FUNCTION_ARGS)
 	while (status->array != NULL && status->currIdx < status->ngxacts)
 	{
 		GlobalTransaction gxact = &status->array[status->currIdx++];
-		Datum		values[4];
-		bool		nulls[4];
+		Datum		values[5];
+		bool		nulls[5];
 		HeapTuple	tuple;
 		Datum		result;
 
@@ -556,8 +561,9 @@ pg_prepared_xact(PG_FUNCTION_ARGS)
 
 		values[0] = TransactionIdGetDatum(gxact->proc.xid);
 		values[1] = DirectFunctionCall1(textin, CStringGetDatum(gxact->gid));
-		values[2] = Int32GetDatum(gxact->owner);
-		values[3] = ObjectIdGetDatum(gxact->proc.databaseId);
+		values[2] = TimestampTzGetDatum(gxact->prepared_at);
+		values[3] = Int32GetDatum(gxact->owner);
+		values[4] = ObjectIdGetDatum(gxact->proc.databaseId);
 
 		tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
 		result = HeapTupleGetDatum(tuple);
@@ -636,7 +642,7 @@ TwoPhaseGetDummyProc(TransactionId xid)
 /*
  * Header for a 2PC state file
  */
-#define TWOPHASE_MAGIC	0x57F94530		/* format identifier */
+#define TWOPHASE_MAGIC	0x57F94531		/* format identifier */
 
 typedef struct TwoPhaseFileHeader
 {
@@ -644,6 +650,7 @@ typedef struct TwoPhaseFileHeader
 	uint32			total_len;			/* actual file length */
 	TransactionId	xid;				/* original transaction XID */
 	Oid				database;			/* OID of database it was in */
+	TimestampTz		prepared_at;		/* time of preparation */
 	AclId			owner;				/* user running the transaction */
 	int32			nsubxacts;			/* number of following subxact XIDs */
 	int32			ncommitrels;		/* number of delete-on-commit rels */
@@ -741,8 +748,9 @@ StartPrepare(GlobalTransaction gxact)
 	hdr.magic = TWOPHASE_MAGIC;
 	hdr.total_len = 0;			/* EndPrepare will fill this in */
 	hdr.xid = xid;
-	hdr.database = MyDatabaseId;
-	hdr.owner = GetUserId();
+	hdr.database = gxact->proc.databaseId;
+	hdr.prepared_at = gxact->prepared_at;
+	hdr.owner = gxact->owner;
 	hdr.nsubxacts = xactGetCommittedChildren(&children);
 	hdr.ncommitrels = smgrGetPendingDeletes(true, &commitrels);
 	hdr.nabortrels = smgrGetPendingDeletes(false, &abortrels);
@@ -1046,7 +1054,7 @@ ReadTwoPhaseFile(TransactionId xid)
  * FinishPreparedTransaction: execute COMMIT PREPARED or ROLLBACK PREPARED
  */
 void
-FinishPreparedTransaction(char *gid, bool isCommit)
+FinishPreparedTransaction(const char *gid, bool isCommit)
 {
 	GlobalTransaction gxact;
 	TransactionId xid;
@@ -1474,7 +1482,10 @@ RecoverPreparedTransactions(void)
 
 			/*
 			 * Reconstruct subtrans state for the transaction --- needed
-			 * because pg_subtrans is not preserved over a restart
+			 * because pg_subtrans is not preserved over a restart.  Note
+			 * that we are linking all the subtransactions directly to the
+			 * top-level XID; there may originally have been a more complex
+			 * hierarchy, but there's no need to restore that exactly.
 			 */
 			for (i = 0; i < hdr->nsubxacts; i++)
 				SubTransSetParent(subxids[i], xid);
@@ -1482,7 +1493,9 @@ RecoverPreparedTransactions(void)
 			/*
 			 * Recreate its GXACT and dummy PGPROC
 			 */
-			gxact = MarkAsPreparing(xid, hdr->database, hdr->gid, hdr->owner);
+			gxact = MarkAsPreparing(xid, hdr->gid,
+									hdr->prepared_at,
+									hdr->owner, hdr->database);
 			GXactLoadSubxactData(gxact, hdr->nsubxacts, subxids);
 			MarkAsPrepared(gxact);
 
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 74163b7f576..2f73ee10c06 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.205 2005/06/17 22:32:42 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.206 2005/06/18 19:33:41 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1630,6 +1630,9 @@ PrepareTransaction(void)
 	TransactionState	s = CurrentTransactionState;
 	TransactionId		xid = GetCurrentTransactionId();
 	GlobalTransaction	gxact;
+	TimestampTz			prepared_at;
+	AbsoluteTime		PreparedSec;	/* integer part */
+	int			PreparedUSec;	/* microsecond part */
 
 	ShowTransactionState("PrepareTransaction");
 
@@ -1692,6 +1695,9 @@ PrepareTransaction(void)
 	 */
 	s->state = TRANS_PREPARE;
 
+	PreparedSec = GetCurrentAbsoluteTimeUsec(&PreparedUSec);
+	prepared_at = AbsoluteTimeUsecToTimestampTz(PreparedSec, PreparedUSec);
+
 	/* Tell bufmgr and smgr to prepare for commit */
 	BufmgrCommit();
 
@@ -1699,7 +1705,8 @@ PrepareTransaction(void)
 	 * Reserve the GID for this transaction. This could fail if the
 	 * requested GID is invalid or already in use.
 	 */
-	gxact = MarkAsPreparing(xid, MyDatabaseId, prepareGID, GetUserId());
+	gxact = MarkAsPreparing(xid, prepareGID, prepared_at,
+							GetUserId(), MyDatabaseId);
 	prepareGID = NULL;
 
 	/*
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 2e9a3ecf7c7..f757e21039d 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 1996-2005, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.14 2005/06/17 22:32:43 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.15 2005/06/18 19:33:42 tgl Exp $
  */
 
 CREATE VIEW pg_user AS 
@@ -106,15 +106,16 @@ CREATE VIEW pg_locks AS
     SELECT * 
     FROM pg_lock_status() AS L
     (locktype text, database oid, relation oid, page int4, tuple int2,
-     transaction xid, classid oid, objid oid, objsubid int2,
-     pid int4, mode text, granted boolean);
+     transactionid xid, classid oid, objid oid, objsubid int2,
+     transaction xid, pid int4, mode text, granted boolean);
 
 CREATE VIEW pg_prepared_xacts AS
-    SELECT P.transaction, P.gid, U.usename AS owner, D.datname AS database
+    SELECT P.transaction, P.gid, P.prepared,
+           U.usename AS owner, D.datname AS database
     FROM pg_prepared_xact() AS P
-    (transaction xid, gid text, ownerid int4, dbid oid)
-         LEFT JOIN pg_database D ON P.dbid = D.oid
-         LEFT JOIN pg_shadow U ON P.ownerid = U.usesysid;
+    (transaction xid, gid text, prepared timestamptz, ownerid int4, dbid oid)
+         LEFT JOIN pg_shadow U ON P.ownerid = U.usesysid
+         LEFT JOIN pg_database D ON P.dbid = D.oid;
 
 CREATE VIEW pg_settings AS 
     SELECT * 
diff --git a/src/backend/utils/adt/lockfuncs.c b/src/backend/utils/adt/lockfuncs.c
index e941ec2d3d8..0bdf918e475 100644
--- a/src/backend/utils/adt/lockfuncs.c
+++ b/src/backend/utils/adt/lockfuncs.c
@@ -6,7 +6,7 @@
  * Copyright (c) 2002-2005, PostgreSQL Global Development Group
  *
  * IDENTIFICATION
- *		$PostgreSQL: pgsql/src/backend/utils/adt/lockfuncs.c,v 1.18 2005/05/17 21:46:10 tgl Exp $
+ *		$PostgreSQL: pgsql/src/backend/utils/adt/lockfuncs.c,v 1.19 2005/06/18 19:33:42 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -26,7 +26,7 @@ static const char * const LockTagTypeNames[] = {
 	"extend",
 	"page",
 	"tuple",
-	"transaction",
+	"transactionid",
 	"object",
 	"userlock"
 };
@@ -64,7 +64,7 @@ pg_lock_status(PG_FUNCTION_ARGS)
 
 		/* build tupdesc for result tuples */
 		/* this had better match pg_locks view in system_views.sql */
-		tupdesc = CreateTemplateTupleDesc(12, false);
+		tupdesc = CreateTemplateTupleDesc(13, false);
 		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "locktype",
 						   TEXTOID, -1, 0);
 		TupleDescInitEntry(tupdesc, (AttrNumber) 2, "database",
@@ -75,7 +75,7 @@ pg_lock_status(PG_FUNCTION_ARGS)
 						   INT4OID, -1, 0);
 		TupleDescInitEntry(tupdesc, (AttrNumber) 5, "tuple",
 						   INT2OID, -1, 0);
-		TupleDescInitEntry(tupdesc, (AttrNumber) 6, "transaction",
+		TupleDescInitEntry(tupdesc, (AttrNumber) 6, "transactionid",
 						   XIDOID, -1, 0);
 		TupleDescInitEntry(tupdesc, (AttrNumber) 7, "classid",
 						   OIDOID, -1, 0);
@@ -83,11 +83,13 @@ pg_lock_status(PG_FUNCTION_ARGS)
 						   OIDOID, -1, 0);
 		TupleDescInitEntry(tupdesc, (AttrNumber) 9, "objsubid",
 						   INT2OID, -1, 0);
-		TupleDescInitEntry(tupdesc, (AttrNumber) 10, "pid",
+		TupleDescInitEntry(tupdesc, (AttrNumber) 10, "transaction",
+						   XIDOID, -1, 0);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 11, "pid",
 						   INT4OID, -1, 0);
-		TupleDescInitEntry(tupdesc, (AttrNumber) 11, "mode",
+		TupleDescInitEntry(tupdesc, (AttrNumber) 12, "mode",
 						   TEXTOID, -1, 0);
-		TupleDescInitEntry(tupdesc, (AttrNumber) 12, "granted",
+		TupleDescInitEntry(tupdesc, (AttrNumber) 13, "granted",
 						   BOOLOID, -1, 0);
 
 		funcctx->tuple_desc = BlessTupleDesc(tupdesc);
@@ -118,8 +120,8 @@ pg_lock_status(PG_FUNCTION_ARGS)
 		LOCKMODE	mode = 0;
 		const char *locktypename;
 		char		tnbuf[32];
-		Datum		values[12];
-		char		nulls[12];
+		Datum		values[13];
+		char		nulls[13];
 		HeapTuple	tuple;
 		Datum		result;
 
@@ -249,10 +251,14 @@ pg_lock_status(PG_FUNCTION_ARGS)
 				break;
 		}
 
-		values[9] = Int32GetDatum(proc->pid);
-		values[10] = DirectFunctionCall1(textin,
+		values[9] = TransactionIdGetDatum(proc->xid);
+		if (proc->pid != 0)
+			values[10] = Int32GetDatum(proc->pid);
+		else
+			nulls[10] = 'n';
+		values[11] = DirectFunctionCall1(textin,
 										 CStringGetDatum(GetLockmodeName(mode)));
-		values[11] = BoolGetDatum(granted);
+		values[12] = BoolGetDatum(granted);
 
 		tuple = heap_formtuple(funcctx->tuple_desc, values, nulls);
 		result = HeapTupleGetDatum(tuple);