Addition of pgxc_prepared_xacts

This new system view is based on a plpgsql function that uses
EXECUTE DIRECT to scan GIDs of prepared transactions on each node
and gather results back to the client.
pgxc_prepared_xacts returns a distinct list of GIDs.

pg_prepared_xacts is kept the same as the original one in PostgreSQL.
As a consequence, schema scanning is not used anymore when looking
for prepared transaction list.
Now users should use pgxc_prepared_xacts to recover the list related
to the whole cluster. pg_prepared_xacts can only be used on a local node.

14 files changed, 177 insertions, 124 deletions

diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index d373452073..41e92992de 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -53,9 +53,6 @@
 #include "utils/rel.h"
 #include "utils/syscache.h"
 
-#ifdef PGXC
-#include "pgxc/pgxc.h"
-#endif
 
 /*
  * The namespace search path is a possibly-empty list of namespace OIDs.
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 4e45a71832..325d4523e6 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -166,17 +166,7 @@ CREATE VIEW pg_available_extension_versions AS
            LEFT JOIN pg_extension AS X
              ON E.name = X.extname AND E.version = X.extversion;
 
-CREATE SCHEMA __pgxc_coordinator_schema__;
-CREATE SCHEMA __pgxc_datanode_schema__;
-
-create table __pgxc_coordinator_schema__.pg_prepared_xacts
-    ( transaction xid, gid text, prepared timestamptz, owner name, database name );
-
-INSERT INTO pgxc_class VALUES
-    ((SELECT oid FROM pg_class
-      WHERE relkind = 'r' AND relname = 'pg_prepared_xacts'), 'N', 0,0,0);
-
-CREATE VIEW __pgxc_datanode_schema__.pg_prepared_xacts AS
+CREATE VIEW pg_prepared_xacts AS
     SELECT P.transaction, P.gid, P.prepared,
            U.rolname AS owner, D.datname AS database
     FROM pg_prepared_xact() AS P
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 3b26d4a68e..ed630d0e7c 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -977,6 +977,21 @@ _copyPlanInvalItem(PlanInvalItem *from)
 
 #ifdef PGXC
 /*
+ * _copyExecDirect
+ */
+static ExecDirectStmt*
+_copyExecDirect(ExecDirectStmt *from)
+{
+	ExecDirectStmt *newnode = makeNode(ExecDirectStmt);
+
+	COPY_SCALAR_FIELD(coordinator);
+	COPY_NODE_FIELD(nodes);
+	COPY_STRING_FIELD(query);
+
+	return newnode;
+}
+
+/*
  * _copyRemoteQuery
  */
 static RemoteQuery *
@@ -993,6 +1008,7 @@ _copyRemoteQuery(RemoteQuery *from)
 	 * copy remainder of node
 	 */
 	COPY_SCALAR_FIELD(is_single_step);
+	COPY_SCALAR_FIELD(exec_direct_type);
 	COPY_STRING_FIELD(sql_statement);
 	COPY_NODE_FIELD(exec_nodes);
 	COPY_SCALAR_FIELD(combine_type);
@@ -1006,6 +1022,7 @@ _copyRemoteQuery(RemoteQuery *from)
 	COPY_POINTER_FIELD(param_types,
 					   sizeof(from->param_types[0]) * from->num_params);
 	COPY_SCALAR_FIELD(exec_type);
+	COPY_SCALAR_FIELD(is_temp);
 	COPY_SCALAR_FIELD(paramval_data);
 	COPY_SCALAR_FIELD(paramval_len);
 
@@ -4113,6 +4130,9 @@ copyObject(void *from)
 			/*
 			 * PGXC SPECIFIC NODES
 			 */
+		case T_ExecDirectStmt:
+			retval = _copyExecDirect(from);
+			break;
 		case T_RemoteQuery:
 			retval = _copyRemoteQuery(from);
 			break;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index f212ee1c09..29b85a1bad 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -2353,7 +2353,7 @@ transformExecDirectStmt(ParseState *pstate, ExecDirectStmt *stmt)
 	/* Default list of parameters to set */
 	step->is_single_step = true;
 	step->sql_statement = NULL;
-	step->exec_nodes = NULL;
+	step->exec_nodes = makeNode(ExecNodes);
 	step->combine_type = COMBINE_TYPE_NONE;
 	step->sort = NULL;
 	step->distinct = NULL;
@@ -2380,10 +2380,6 @@ transformExecDirectStmt(ParseState *pstate, ExecDirectStmt *stmt)
 	step->join_condition = NULL;
 
 	/* Change the list of nodes that will be executed for the query and others */
-	step->exec_nodes = (ExecNodes *) palloc(sizeof(ExecNodes));
-	step->exec_nodes->primarynodelist = NIL;
-	step->exec_nodes->nodelist = NIL;
-	step->exec_nodes->en_expr = NULL;
 	step->force_autocommit = false;
 	step->combine_type = COMBINE_TYPE_SAME;
 	step->read_only = true;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1cbb5e9314..e7fe3ef004 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -8070,7 +8070,6 @@ ExecDirectStmt: EXECUTE DIRECT ON COORDINATOR coord_list DirectStmt
 				{
 					ExecDirectStmt *n = makeNode(ExecDirectStmt);
 					n->coordinator = TRUE;
-					n->nodes = NIL;
 					n->nodes = $5;
 					n->query = $6;
 					$$ = (Node *)n;
diff --git a/src/backend/pgxc/plan/planner.c b/src/backend/pgxc/plan/planner.c
index 67a2a8c32f..3c0ee4c03b 100644
--- a/src/backend/pgxc/plan/planner.c
+++ b/src/backend/pgxc/plan/planner.c
@@ -2741,6 +2741,7 @@ pgxc_planner(Query *query, int cursorOptions, ParamListInfo boundParams)
 	PlannerInfo *root;
 	RemoteQuery *query_step;
 	StringInfoData buf;
+	bool exec_dir_catalog = false;
 
 	/*
 	 * Set up global state for this planner invocation.  This data is needed
@@ -2793,6 +2794,9 @@ pgxc_planner(Query *query, int cursorOptions, ParamListInfo boundParams)
 			query_step = stmt;
 			query->utilityStmt = NULL;
 			result->utilityStmt = NULL;
+
+			/* Force execution on nodes if query is only used for catalogs */
+			exec_dir_catalog = contains_only_pg_catalog(query->rtable);
 		}
 		else
 		{
@@ -2837,7 +2841,7 @@ pgxc_planner(Query *query, int cursorOptions, ParamListInfo boundParams)
 	if (query->commandType != CMD_SELECT)
 		result->resultRelations = list_make1_int(query->resultRelation);
 
-	if (contains_only_pg_catalog(query->rtable))
+	if (contains_only_pg_catalog(query->rtable) && !exec_dir_catalog)
 	{
 		result = standard_planner(query, cursorOptions, boundParams);
 		return result;
diff --git a/src/backend/pgxc/pool/execRemote.c b/src/backend/pgxc/pool/execRemote.c
index e9d2d17232..df92bf3678 100644
--- a/src/backend/pgxc/pool/execRemote.c
+++ b/src/backend/pgxc/pool/execRemote.c
@@ -1472,6 +1472,12 @@ PGXCNodeSetBeginQuery(char *query_string)
 }
 
 /*
+ * Error messages for PREPARE
+ */
+#define ERROR_DATANODES_PREPARE	-1
+#define ERROR_ALREADY_PREPARE	-2
+
+/*
  * Prepare transaction on Datanodes and Coordinators involved in current transaction.
  * GXID associated to current transaction has to be committed on GTM.
  */
@@ -1544,12 +1550,15 @@ finish:
 					(errcode(ERRCODE_INTERNAL_ERROR),
 					 errmsg("cannot PREPARE a transaction that has operated on temporary tables")));
 		}
+
+		if (res == ERROR_ALREADY_PREPARE)
+			ereport(ERROR,
+					(errcode(ERRCODE_INTERNAL_ERROR),
+					 errmsg("transaction identifier \"%s\" is already in use", gid)));
 		else
-		{
 			ereport(ERROR,
 					(errcode(ERRCODE_INTERNAL_ERROR),
 					 errmsg("Could not prepare transaction on data nodes")));
-		}
 	}
 
 	/* Reset temporary object flag */
@@ -1683,10 +1692,10 @@ finish:
 		 */
 		if (!gtm_error)
 			if (pgxc_all_handles_send_gxid(pgxc_handles, rollback_xid, false))
-				result = EOF;
+				result = ERROR_DATANODES_PREPARE;
 
 		if (pgxc_all_handles_send_query(pgxc_handles, buffer, false))
-			result = EOF;
+			result = ERROR_DATANODES_PREPARE;
 
 		result = pgxc_node_receive_and_validate(dn_conn_count, pgxc_handles->datanode_handles, false);
 		result |= pgxc_node_receive_and_validate(co_conn_count, pgxc_handles->coord_handles, false);
@@ -1698,7 +1707,10 @@ finish:
 		if (!gtm_error)
 			CommitPreparedTranGTM(gxid, rollback_xid);
 
-		return EOF;
+		if (gtm_error)
+			return ERROR_ALREADY_PREPARE;
+		else
+			return ERROR_DATANODES_PREPARE;
 	}
 
 	return result;
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 28a2471495..c4a8119735 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -539,26 +539,6 @@ PostmasterMain(int argc, char *argv[])
 	/* Initialize paths to installation files */
 	getInstallationPaths(argv[0]);
 
-#ifdef PGXC
-	/* Decide whether coordinator or data node before setting GUC variables */
-	while ((opt = getopt(argc, argv, "A:B:Cc:D:d:EeFf:h:ijk:lN:nOo:Pp:r:S:sTt:W:X-:")) != -1)
-	{
-		switch (opt)
-		{
-			case 'C':
-				isPGXCCoordinator = true;
-				break;
-			case 'X':
-				isPGXCDataNode = true;
-				break;
-			default:
-				break;
-		}
-	}
-	/* Reset getopt for parsing again */
-	optind = 1;
-#endif 
-
 	/*
 	 * Options setup
 	 */
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 04ac4c45ad..3ef8068d57 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -902,7 +902,7 @@ RelationBuildDesc(Oid targetRelId, bool insertIt)
 		relation->trigdesc = NULL;
 
 #ifdef PGXC
-	if (IS_PGXC_COORDINATOR && relation->rd_id >= FirstBootstrapObjectId)
+	if (IS_PGXC_COORDINATOR && relation->rd_id >= FirstNormalObjectId)
 		RelationBuildLocator(relation);
 #endif
 	/*
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 9f5cb5aa46..9f2dbe374c 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2532,10 +2532,6 @@ static struct config_int ConfigureNamesInt[] =
 	}
 };
 
-#ifdef PGXC
-/* Variable to store search path */
-static char boot_search_path[255];
-#endif
 
 static struct config_real ConfigureNamesReal[] =
 {
@@ -2892,13 +2888,8 @@ static struct config_string ConfigureNamesString[] =
 			GUC_LIST_INPUT | GUC_LIST_QUOTE
 		},
 		&namespace_search_path,
-#ifdef PGXC
-		boot_search_path,
-		check_search_path, assign_search_path, NULL
-#else
 		"\"$user\",public",
 		check_search_path, assign_search_path, NULL
-#endif
 	},
 
 	{
@@ -3757,14 +3748,6 @@ build_guc_variables(void)
 	struct config_generic **guc_vars;
 	int			i;
 
-#ifdef PGXC
-	strcpy(boot_search_path, "\"$user\",public, ");
-	if (IS_PGXC_DATANODE)
-		strcat(boot_search_path, PGXC_DATA_NODE_SCHEMA);
-	else
-		strcat(boot_search_path, PGXC_COORDINATOR_SCHEMA);
-#endif
-
 	for (i = 0; ConfigureNamesBool[i].gen.name; i++)
 	{
 		struct config_bool *conf = &ConfigureNamesBool[i];
diff --git a/src/include/pgxc/locator.h b/src/include/pgxc/locator.h
index 0c584da1f0..80922605c2 100644
--- a/src/include/pgxc/locator.h
+++ b/src/include/pgxc/locator.h
@@ -28,10 +28,6 @@
 
 #define IsReplicated(x) (x->locatorType == LOCATOR_TYPE_REPLICATED)
 
-#define PGXC_COORDINATOR_SCHEMA	"__pgxc_coordinator_schema__"
-#define PGXC_DATA_NODE_SCHEMA	"__pgxc_datanode_schema__"
-#define PREPARED_XACTS_TABLE	"pg_prepared_xacts"
-
 #include "nodes/primnodes.h"
 #include "utils/relcache.h"
 
diff --git a/src/pl/plpgsql/src/plpgsql--1.0.sql b/src/pl/plpgsql/src/plpgsql--1.0.sql
index 514562d70f..546598d89e 100644
--- a/src/pl/plpgsql/src/plpgsql--1.0.sql
+++ b/src/pl/plpgsql/src/plpgsql--1.0.sql
@@ -7,3 +7,35 @@
  */
 
 CREATE PROCEDURAL LANGUAGE plpgsql;
+
+/*
+ * PGXC system view to look for prepared transaction GID list in a cluster
+ */
+CREATE FUNCTION pgxc_prepared_xact()
+RETURNS setof text
+AS $$
+DECLARE
+	num_nodes integer;
+	i integer;
+	num_nodes_text text;
+	text_output text;
+	row_data record;
+	query_str text;
+	BEGIN
+		--Get total number of nodes
+		SELECT INTO num_nodes_text setting FROM pg_settings WHERE name = 'num_data_nodes';
+		num_nodes = num_nodes_text::integer;
+		i := 1;
+		WHILE i <= num_nodes LOOP
+			query_str := 'EXECUTE DIRECT ON NODE ' || i || ' ''SELECT gid FROM pg_prepared_xact()''';
+			FOR row_data IN EXECUTE(query_str) LOOP
+				return next row_data.gid;
+			END LOOP;
+			i := i + 1;
+		END LOOP;
+		return;
+	END; $$
+LANGUAGE 'plpgsql';
+
+CREATE VIEW pgxc_prepared_xacts AS
+    SELECT DISTINCT * from pgxc_prepared_xact();
diff --git a/src/test/regress/expected/prepared_xacts_2.out b/src/test/regress/expected/prepared_xacts_2.out
index 307ffada93..b23e21a5d4 100644
--- a/src/test/regress/expected/prepared_xacts_2.out
+++ b/src/test/regress/expected/prepared_xacts_2.out
@@ -9,7 +9,7 @@
 CREATE TABLE pxtest1 (foobar VARCHAR(10)) distribute by replication;
 INSERT INTO pxtest1 VALUES ('aaa');
 -- Test PREPARE TRANSACTION
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 UPDATE pxtest1 SET foobar = 'bbb' WHERE foobar = 'aaa';
 SELECT * FROM pxtest1 ORDER BY foobar;
  foobar 
@@ -25,9 +25,15 @@ SELECT * FROM pxtest1 ORDER BY foobar;
 (1 row)
 
 -- Test pg_prepared_xacts system view
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
- gid  
-------
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
+ gid 
+-----
+(0 rows)
+
+-- Test pgxc_prepared_xacts system view
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
+ pgxc_prepared_xact 
+--------------------
  foo1
 (1 row)
 
@@ -39,13 +45,20 @@ SELECT * FROM pxtest1  ORDER BY foobar;
  aaa
 (1 row)
 
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
+-- Check prepared transactions on Coordinator
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
  gid 
 -----
 (0 rows)
 
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
+ pgxc_prepared_xact 
+--------------------
+(0 rows)
+
 -- Test COMMIT PREPARED
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 INSERT INTO pxtest1 VALUES ('ddd');
 SELECT * FROM pxtest1 ORDER BY foobar;
  foobar 
@@ -70,7 +83,7 @@ SELECT * FROM pxtest1  ORDER BY foobar;
 (2 rows)
 
 -- Test duplicate gids
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
 SELECT * FROM pxtest1  ORDER BY foobar;
  foobar 
@@ -80,13 +93,20 @@ SELECT * FROM pxtest1  ORDER BY foobar;
 (2 rows)
 
 PREPARE TRANSACTION 'foo3';
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
- gid  
-------
+-- Check prepared transactions on Coordinator
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
+ gid 
+-----
+(0 rows)
+
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
+ pgxc_prepared_xact 
+--------------------
  foo3
 (1 row)
 
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 INSERT INTO pxtest1 VALUES ('fff');
 SELECT * FROM pxtest1  ORDER BY foobar;
  foobar 
@@ -98,7 +118,8 @@ SELECT * FROM pxtest1  ORDER BY foobar;
 
 -- This should fail, because the gid foo3 is already in use
 PREPARE TRANSACTION 'foo3';
-ERROR:  Could not prepare transaction on data nodes
+ERROR:  transaction identifier "foo3" is already in use
+-- Rollback on all the nodes
 ROLLBACK;
 SELECT * FROM pxtest1  ORDER BY foobar;
  foobar 
@@ -118,7 +139,7 @@ SELECT * FROM pxtest1  ORDER BY foobar;
 -- Clean up
 DROP TABLE pxtest1;
 -- Test subtransactions
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   CREATE TABLE pxtest2 (a int);
   INSERT INTO pxtest2 VALUES (1);
   SAVEPOINT a;
@@ -132,14 +153,9 @@ ERROR:  current transaction is aborted, commands ignored until end of transactio
   INSERT INTO pxtest2 VALUES (3);
 ERROR:  current transaction is aborted, commands ignored until end of transaction block
 PREPARE TRANSACTION 'regress-one';
-BEGIN;
-  CREATE TABLE pxtest2 (a int);
-  INSERT INTO pxtest2 VALUES (1);
-  INSERT INTO pxtest2 VALUES (3);
-PREPARE TRANSACTION 'regress-one';
 CREATE TABLE pxtest3(fff int);
 -- Test shared invalidation
-BEGIN;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   DROP TABLE pxtest3;
   CREATE TABLE pxtest4 (a int);
   INSERT INTO pxtest4 VALUES (1);
@@ -149,7 +165,7 @@ BEGIN;
   FETCH 1 FROM foo;
  a 
 ---
- 1
+ 2
 (1 row)
 
 PREPARE TRANSACTION 'regress-two';
@@ -162,12 +178,18 @@ ERROR:  relation "pxtest2" does not exist
 LINE 1: SELECT * FROM pxtest2;
                       ^
 -- There should be two prepared transactions
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
      gid     
 -------------
- regress-one
  regress-two
-(2 rows)
+(1 row)
+
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
+ pgxc_prepared_xact 
+--------------------
+ regress-two
+(1 row)
 
 -- pxtest3 should be locked because of the pending DROP
 set statement_timeout to 2000;
@@ -177,12 +199,18 @@ reset statement_timeout;
 -- Disconnect, we will continue testing in a different backend
 \c -
 -- There should still be two prepared transactions
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
      gid     
 -------------
- regress-one
  regress-two
-(2 rows)
+(1 row)
+
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
+ pgxc_prepared_xact 
+--------------------
+ regress-two
+(1 row)
 
 -- pxtest3 should still be locked because of the pending DROP
 set statement_timeout to 2000;
@@ -191,26 +219,26 @@ ERROR:  canceling statement due to statement timeout
 reset statement_timeout;
 -- Commit table creation
 COMMIT PREPARED 'regress-one';
+ERROR:  prepared transaction with identifier "regress-one" does not exist
 \d pxtest2
-    Table "public.pxtest2"
- Column |  Type   | Modifiers 
---------+---------+-----------
- a      | integer | 
-
 SELECT * FROM pxtest2;
- a 
----
- 1
- 3
-(2 rows)
-
+ERROR:  relation "pxtest2" does not exist
+LINE 1: SELECT * FROM pxtest2;
+                      ^
 -- There should be one prepared transaction
-SELECT DISTINCT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts ORDER BY 1;
      gid     
 -------------
  regress-two
 (1 row)
 
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
+ pgxc_prepared_xact 
+--------------------
+ regress-two
+(1 row)
+
 -- Commit table drop
 COMMIT PREPARED 'regress-two';
 SELECT * FROM pxtest3;
@@ -218,13 +246,20 @@ ERROR:  relation "pxtest3" does not exist
 LINE 1: SELECT * FROM pxtest3;
                       ^
 -- There should be no prepared transactions
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
  gid 
 -----
 (0 rows)
 
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
+ pgxc_prepared_xact 
+--------------------
+(0 rows)
+
 -- Clean up
 DROP TABLE pxtest2;
+ERROR:  table "pxtest2" does not exist
 DROP TABLE pxtest3;  -- will still be there if prepared xacts are disabled
 ERROR:  table "pxtest3" does not exist
 DROP TABLE pxtest4;
diff --git a/src/test/regress/sql/prepared_xacts.sql b/src/test/regress/sql/prepared_xacts.sql
index 76553c9001..36d03bbb10 100644
--- a/src/test/regress/sql/prepared_xacts.sql
+++ b/src/test/regress/sql/prepared_xacts.sql
@@ -22,15 +22,19 @@ PREPARE TRANSACTION 'foo1';
 SELECT * FROM pxtest1 ORDER BY foobar;
 
 -- Test pg_prepared_xacts system view
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
+-- Test pgxc_prepared_xacts system view
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
 
 -- Test ROLLBACK PREPARED
 ROLLBACK PREPARED 'foo1';
 
 SELECT * FROM pxtest1  ORDER BY foobar;
 
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
-
+-- Check prepared transactions on Coordinator
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
 
 -- Test COMMIT PREPARED
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
@@ -50,7 +54,10 @@ UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
 SELECT * FROM pxtest1  ORDER BY foobar;
 PREPARE TRANSACTION 'foo3';
 
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
+-- Check prepared transactions on Coordinator
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
 
 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 INSERT INTO pxtest1 VALUES ('fff');
@@ -58,9 +65,11 @@ SELECT * FROM pxtest1  ORDER BY foobar;
 
 -- This should fail, because the gid foo3 is already in use
 PREPARE TRANSACTION 'foo3';
-
+-- Rollback on all the nodes
 ROLLBACK;
+
 SELECT * FROM pxtest1  ORDER BY foobar;
+
 ROLLBACK PREPARED 'foo3';
 
 SELECT * FROM pxtest1  ORDER BY foobar;
@@ -79,13 +88,6 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   INSERT INTO pxtest2 VALUES (3);
 PREPARE TRANSACTION 'regress-one';
 
-BEGIN;
-  CREATE TABLE pxtest2 (a int);
-  INSERT INTO pxtest2 VALUES (1);
-  INSERT INTO pxtest2 VALUES (3);
-PREPARE TRANSACTION 'regress-one';
-
-
 CREATE TABLE pxtest3(fff int);
 
 -- Test shared invalidation
@@ -106,7 +108,9 @@ FETCH 1 FROM foo;
 SELECT * FROM pxtest2;
 
 -- There should be two prepared transactions
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
 
 -- pxtest3 should be locked because of the pending DROP
 set statement_timeout to 2000;
@@ -117,7 +121,9 @@ reset statement_timeout;
 \c -
 
 -- There should still be two prepared transactions
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
 
 -- pxtest3 should still be locked because of the pending DROP
 set statement_timeout to 2000;
@@ -130,17 +136,20 @@ COMMIT PREPARED 'regress-one';
 SELECT * FROM pxtest2;
 
 -- There should be one prepared transaction
-SELECT DISTINCT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts ORDER BY 1;
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
 
 -- Commit table drop
 COMMIT PREPARED 'regress-two';
 SELECT * FROM pxtest3;
 
 -- There should be no prepared transactions
-SELECT DISTINCT gid FROM pg_prepared_xacts ORDER BY gid;
+SELECT gid FROM pg_prepared_xacts ORDER BY gid;
+-- Check prepared transactions in the cluster
+SELECT pgxc_prepared_xact FROM pgxc_prepared_xacts ORDER by 1;
 
 -- Clean up
 DROP TABLE pxtest2;
 DROP TABLE pxtest3;  -- will still be there if prepared xacts are disabled
 DROP TABLE pxtest4;
-