Fix a problem in explicitly PREPARED TRANSACTIONS.

Problem:
  If a transaction contains a SELECT FOR UPDATE/SHARE,
  it has to be prepared on the data nodes,
  otherwise the tables are not locked on the data nodes.

Solution:
  We have added another member in RemoteQuery
  called has_row_marks. This member has the same value as
  hasForUpdate of the Query structure.
  This member is used to make sure that although
  a SELECT FOR UPDATE/SHARE is treated as a write
  still describe message needs to be sent to data nodes
  because this command would result in rows.
  The meaning of the read_only member of RemoteQuery
  is also changed to mean SELECTS without row marks.

Test cases are added to make sure the prepared transactions
are working as expected.

8 files changed, 1626 insertions, 12 deletions

diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 7fbb9b3dd7..d2be80f184 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1035,6 +1035,7 @@ _copyRemoteQuery(RemoteQuery *from)
 	COPY_STRING_FIELD(inner_statement);
 	COPY_STRING_FIELD(outer_statement);
 	COPY_STRING_FIELD(join_condition);
+	COPY_SCALAR_FIELD(has_row_marks);
 
 	return newnode;
 }
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 6ada84d93e..9d4970d675 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -477,6 +477,7 @@ _outRemoteQuery(StringInfo str, RemoteQuery *node)
 	WRITE_INT_FIELD(num_params);
 	WRITE_ENUM_FIELD(exec_type, RemoteQueryExecType);
 	WRITE_BOOL_FIELD(is_temp);
+	WRITE_BOOL_FIELD(has_row_marks);
 }
 
 static void
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 2014a73b9e..31bdd7ea26 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2773,7 +2773,8 @@ create_remotequery_plan(PlannerInfo *root, Path *best_path,
 	/* Track if the remote query involves a temporary object */
 	scan_plan->is_temp = IsTempTable(rte->relid);
 
-	scan_plan->read_only = query->commandType == CMD_SELECT;
+	scan_plan->read_only = (query->commandType == CMD_SELECT && !query->hasForUpdate);
+	scan_plan->has_row_marks = query->hasForUpdate;
 
 	scan_plan->sql_statement = sql.data;
 	/*
@@ -4132,6 +4133,7 @@ make_remotequery(List *qptlist, List *qpqual, Index scanrelid)
 	plan->righttree = NULL;
 	node->scan.scanrelid = scanrelid;
 	node->read_only = true;
+	node->has_row_marks = false;
 
 	return node;
 }
@@ -6397,7 +6399,8 @@ create_remotegrouping_plan(PlannerInfo *root, Plan *local_plan)
 	remote_group->sql_statement = remote_sql_stmt->data;
 
 	/* set_plan_refs needs this later */
-	remote_group->read_only = query->commandType == CMD_SELECT;
+	remote_group->read_only = (query->commandType == CMD_SELECT && !query->hasForUpdate);
+	remote_group->has_row_marks = query->hasForUpdate;
 
 	/* we actually need not worry about costs since this is the final plan */
 	remote_group_plan->startup_cost	= remote_scan->scan.plan.startup_cost;
diff --git a/src/backend/pgxc/plan/planner.c b/src/backend/pgxc/plan/planner.c
index d3f57944c2..c2c159f8f1 100644
--- a/src/backend/pgxc/plan/planner.c
+++ b/src/backend/pgxc/plan/planner.c
@@ -661,7 +661,9 @@ pgxc_FQS_create_remote_plan(Query *query, ExecNodes *exec_nodes, bool is_exec_di
 	 */
 	query->qry_finalise_aggs = false;
 	/* Optimize multi-node handling */
-	query_step->read_only = query->commandType == CMD_SELECT;
+	query_step->read_only = (query->commandType == CMD_SELECT && !query->hasForUpdate);
+	query_step->has_row_marks = query->hasForUpdate;
+
 	/* Check if temporary tables are in use in query */
 	/* PGXC_FQS_TODO: scanning the rtable again for the queries should not be
 	 * needed. We should be able to find out if the query has a temporary object
diff --git a/src/backend/pgxc/pool/execRemote.c b/src/backend/pgxc/pool/execRemote.c
index bdf174440c..00b049a884 100644
--- a/src/backend/pgxc/pool/execRemote.c
+++ b/src/backend/pgxc/pool/execRemote.c
@@ -2849,7 +2849,7 @@ pgxc_start_command_on_connection(PGXCNodeHandle *connection,
 							step->param_types,
 							remotestate->paramval_len,
 							remotestate->paramval_data,
-							step->read_only,
+							step->has_row_marks ? true : step->read_only,
 							fetch) != 0)
 			return false;
 	}
diff --git a/src/include/pgxc/planner.h b/src/include/pgxc/planner.h
index 06f53e7ce8..62d401d3d0 100644
--- a/src/include/pgxc/planner.h
+++ b/src/include/pgxc/planner.h
@@ -110,6 +110,7 @@ typedef struct
 	char			*inner_statement;
 	char			*outer_statement;
 	char			*join_condition;
+	bool			has_row_marks;		/* Did SELECT had FOR UPDATE/SHARE? */
 } RemoteQuery;
 
 /*
diff --git a/src/test/regress/expected/xc_for_update.out b/src/test/regress/expected/xc_for_update.out
index d69ade5466..1561780c65 100644
--- a/src/test/regress/expected/xc_for_update.out
+++ b/src/test/regress/expected/xc_for_update.out
@@ -1,3 +1,41 @@
+-- A function to return data node name given a node number
+create or replace function get_xc_node_name(node_num int) returns varchar language plpgsql as $$
+declare
+	r pgxc_node%rowtype;
+	node int;
+	nodenames_query varchar;
+begin
+	nodenames_query := 'SELECT * FROM pgxc_node  WHERE node_type = ''D'' ORDER BY xc_node_id';
+
+	node := 1;
+	for r in execute nodenames_query loop
+		if node = node_num THEN
+			RETURN r.node_name;
+		end if;
+		node := node + 1;
+	end loop;
+	RETURN 'NODE_?';
+end;
+$$;
+-- A function to check whether a certain transaction was prepared on a specific data node given its number
+create or replace function is_prepared_on_node(txn_id varchar, nodenum int) returns bool language plpgsql as $$
+declare
+	nodename        varchar;
+	qry             varchar;
+	r               pg_prepared_xacts%rowtype;
+begin
+	nodename := (SELECT get_xc_node_name(nodenum));
+	qry := 'execute direct on ' || nodename || ' ' || chr(39) || 'select * from pg_prepared_xacts' || chr(39);
+
+	for r in execute qry loop
+		if r.gid = txn_id THEN
+			RETURN true;
+		end if;
+	end loop;
+	return false;
+end;
+$$;
+set enable_fast_query_shipping=true;
 -- create some tables
 create table t1(val int, val2 int);
 create table t2(val int, val2 int);
@@ -669,8 +707,6 @@ fetch 1 from c2;
 end;
 -- prepare a transaction that holds a ACCESS EXCLUSIVE (ROW SHARE) lock on a table
 begin;
--- This lock is here due to bug ID 3517977
-lock table mytab4 in ACCESS EXCLUSIVE mode nowait;
 declare c1 cursor for select * from mytab1 for update;
 fetch 1 from c1;
  val | val2 | val3 
@@ -816,8 +852,6 @@ fetch 1 from c2;
 end;
 -- prepare a transaction that holds a ACCESS SHARE (ROW SHARE) lock on a table
 begin;
--- This statement is here due to bug ID 3517977
-lock table mytab4 in ACCESS EXCLUSIVE mode nowait;
 declare c1 cursor for select * from mytab1 for share;
 fetch 1 from c1;
  val | val2 | val3 
@@ -947,3 +981,1029 @@ drop view v2;
 drop table mytab1 cascade;
 drop table mytab4 cascade;
 drop table mytab3 cascade;
+-- Test to make sure prepared transactions are working as expected
+-- If a transcation is preared and contains only a select with for share/update, it should be preapred on data nodes
+-- create some tables
+create table t1(val int, val2 int) DISTRIBUTE BY REPLICATION;
+create table t2(val int, val2 int) DISTRIBUTE BY REPLICATION;
+create table t3(val int, val2 int) DISTRIBUTE BY REPLICATION;
+create table p1(a int, b int) DISTRIBUTE BY REPLICATION;
+create table c1(d int, e int) inherits (p1) DISTRIBUTE BY REPLICATION;
+-- insert some rows in them
+insert into t1 values(1,11),(2,11);
+insert into t2 values(3,11),(4,11);
+insert into t3 values(5,11),(6,11);
+insert into p1 values(55,66),(77,88);
+insert into c1 values(111,222,333,444),(123,345,567,789);
+-- ****  
+begin;
+  select * from t1 order by 1 for update of t1 nowait;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1, t2, t3 order by 1 for update;
+ val | val2 | val | val2 | val | val2 
+-----+------+-----+------+-----+------
+   1 |   11 |   3 |   11 |   5 |   11
+   1 |   11 |   3 |   11 |   6 |   11
+   1 |   11 |   4 |   11 |   5 |   11
+   1 |   11 |   4 |   11 |   6 |   11
+   2 |   11 |   3 |   11 |   5 |   11
+   2 |   11 |   3 |   11 |   6 |   11
+   2 |   11 |   4 |   11 |   5 |   11
+   2 |   11 |   4 |   11 |   6 |   11
+(8 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1 FOR UPDATE) SELECT * FROM q1,t2 order by 1 FOR UPDATE;
+ val | val2 | val | val2 
+-----+------+-----+------
+   1 |   11 |   3 |   11
+   1 |   11 |   4 |   11
+   2 |   11 |   3 |   11
+   2 |   11 |   4 |   11
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1) SELECT * FROM q1 FOR UPDATE;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- false
+ is_prepared_on_node 
+---------------------
+ f
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1 FOR UPDATE) SELECT * FROM q1 FOR UPDATE;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1, t2 where t1.val = t2.val for share;
+ val | val2 | val | val2 
+-----+------+-----+------
+(0 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1, t2 for share of t2;
+ val | val2 | val | val2 
+-----+------+-----+------
+   1 |   11 |   3 |   11
+   1 |   11 |   4 |   11
+   2 |   11 |   3 |   11
+   2 |   11 |   4 |   11
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from (select * from t1 for update of t1 nowait) as foo;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 where val in (select val from t2 for update of t2 nowait) for update;
+ val | val2 
+-----+------
+(0 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 where val in (select val from t2 for update of t2 nowait);
+ val | val2 
+-----+------
+(0 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1, t2 for share of t2 for update of t1;
+ val | val2 | val | val2 
+-----+------+-----+------
+   1 |   11 |   3 |   11
+   1 |   11 |   4 |   11
+   2 |   11 |   3 |   11
+   2 |   11 |   4 |   11
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 for share of t1 for update of t1;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 for update of t1 for share of t1;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 for share of t1 for share of t1 for update of t1;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 for share of t1 for share of t1 for share of t1;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 for share of t1 for share of t1 nowait for update of t1;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 a,t1 b for share of a for update of b;
+ val | val2 | val | val2 
+-----+------+-----+------
+   1 |   11 |   1 |   11
+   1 |   11 |   2 |   11
+   2 |   11 |   1 |   11
+   2 |   11 |   2 |   11
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from p1 order by 1 for update;
+  a  |  b  
+-----+-----
+  55 |  66
+  77 |  88
+ 111 | 222
+ 123 | 345
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from p1 for update;
+  a  |  b  
+-----+-----
+  55 |  66
+  77 |  88
+ 111 | 222
+ 123 | 345
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); --true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from c1 order by 1 for update;
+  a  |  b  |  d  |  e  
+-----+-----+-----+-----
+ 111 | 222 | 333 | 444
+ 123 | 345 | 567 | 789
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from c1 for update;
+  a  |  b  |  d  |  e  
+-----+-----+-----+-----
+ 111 | 222 | 333 | 444
+ 123 | 345 | 567 | 789
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- **********************************
+-- repeat all tests with FQS disabled
+-- **********************************
+set enable_fast_query_shipping=false;
+-- ****  
+begin;
+  select * from t1 order by 1 for update of t1 nowait;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1, t2, t3 order by 1 for update;
+ val | val2 | val | val2 | val | val2 
+-----+------+-----+------+-----+------
+   1 |   11 |   3 |   11 |   5 |   11
+   1 |   11 |   3 |   11 |   6 |   11
+   1 |   11 |   4 |   11 |   5 |   11
+   1 |   11 |   4 |   11 |   6 |   11
+   2 |   11 |   3 |   11 |   5 |   11
+   2 |   11 |   3 |   11 |   6 |   11
+   2 |   11 |   4 |   11 |   5 |   11
+   2 |   11 |   4 |   11 |   6 |   11
+(8 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1 FOR UPDATE) SELECT * FROM q1,t2 order by 1 FOR UPDATE;
+ val | val2 | val | val2 
+-----+------+-----+------
+   1 |   11 |   3 |   11
+   1 |   11 |   4 |   11
+   2 |   11 |   3 |   11
+   2 |   11 |   4 |   11
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1) SELECT * FROM q1 FOR UPDATE;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- false
+ is_prepared_on_node 
+---------------------
+ f
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1 FOR UPDATE) SELECT * FROM q1 FOR UPDATE;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1, t2 where t1.val = t2.val for share;
+ val | val2 | val | val2 
+-----+------+-----+------
+(0 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1, t2 for share of t2;
+ val | val2 | val | val2 
+-----+------+-----+------
+   1 |   11 |   3 |   11
+   1 |   11 |   4 |   11
+   2 |   11 |   3 |   11
+   2 |   11 |   4 |   11
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from (select * from t1 for update of t1 nowait) as foo;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 where val in (select val from t2 for update of t2 nowait) for update;
+ val | val2 
+-----+------
+(0 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 where val in (select val from t2 for update of t2 nowait);
+ val | val2 
+-----+------
+(0 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1, t2 for share of t2 for update of t1;
+ val | val2 | val | val2 
+-----+------+-----+------
+   1 |   11 |   3 |   11
+   1 |   11 |   4 |   11
+   2 |   11 |   3 |   11
+   2 |   11 |   4 |   11
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 for share of t1 for update of t1;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 for update of t1 for share of t1;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 for share of t1 for share of t1 for update of t1;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 for share of t1 for share of t1 for share of t1;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 for share of t1 for share of t1 nowait for update of t1;
+ val | val2 
+-----+------
+   1 |   11
+   2 |   11
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from t1 a,t1 b for share of a for update of b;
+ val | val2 | val | val2 
+-----+------+-----+------
+   1 |   11 |   1 |   11
+   1 |   11 |   2 |   11
+   2 |   11 |   1 |   11
+   2 |   11 |   2 |   11
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from p1 order by 1 for update;
+  a  |  b  
+-----+-----
+  55 |  66
+  77 |  88
+ 111 | 222
+ 123 | 345
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from p1 for update;
+  a  |  b  
+-----+-----
+  55 |  66
+  77 |  88
+ 111 | 222
+ 123 | 345
+(4 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); --true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from c1 order by 1 for update;
+  a  |  b  |  d  |  e  
+-----+-----+-----+-----
+ 111 | 222 | 333 | 444
+ 123 | 345 | 567 | 789
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+begin;
+  select * from c1 for update;
+  a  |  b  |  d  |  e  
+-----+-----+-----+-----
+ 111 | 222 | 333 | 444
+ 123 | 345 | 567 | 789
+(2 rows)
+
+prepare transaction 'pt_1';
+select gid from pg_prepared_xacts where gid = 'pt_1';
+ gid  
+------
+ pt_1
+(1 row)
+
+select is_prepared_on_node('pt_1', 1); -- true
+ is_prepared_on_node 
+---------------------
+ t
+(1 row)
+
+commit prepared 'pt_1';
+-- ****  
+set enable_fast_query_shipping=true;
+-- ****  
+delete from t3 where val != 5;
+PREPARE my_plan(int) as select * from t3 for update;
+execute my_plan(1);
+ val | val2 
+-----+------
+   5 |   11
+(1 row)
+
+-- ****  
+-- drop objects created
+drop table c1;
+drop table p1;
+drop table t1;
+drop table t2;
+drop table t3;
diff --git a/src/test/regress/sql/xc_for_update.sql b/src/test/regress/sql/xc_for_update.sql
index d512dd7592..37169d8e92 100644
--- a/src/test/regress/sql/xc_for_update.sql
+++ b/src/test/regress/sql/xc_for_update.sql
@@ -1,3 +1,46 @@
+
+-- A function to return data node name given a node number
+create or replace function get_xc_node_name(node_num int) returns varchar language plpgsql as $$
+declare
+	r pgxc_node%rowtype;
+	node int;
+	nodenames_query varchar;
+begin
+	nodenames_query := 'SELECT * FROM pgxc_node  WHERE node_type = ''D'' ORDER BY xc_node_id';
+
+	node := 1;
+	for r in execute nodenames_query loop
+		if node = node_num THEN
+			RETURN r.node_name;
+		end if;
+		node := node + 1;
+	end loop;
+	RETURN 'NODE_?';
+end;
+$$;
+
+
+-- A function to check whether a certain transaction was prepared on a specific data node given its number
+create or replace function is_prepared_on_node(txn_id varchar, nodenum int) returns bool language plpgsql as $$
+declare
+	nodename        varchar;
+	qry             varchar;
+	r               pg_prepared_xacts%rowtype;
+begin
+	nodename := (SELECT get_xc_node_name(nodenum));
+	qry := 'execute direct on ' || nodename || ' ' || chr(39) || 'select * from pg_prepared_xacts' || chr(39);
+
+	for r in execute qry loop
+		if r.gid = txn_id THEN
+			RETURN true;
+		end if;
+	end loop;
+	return false;
+end;
+$$;
+
+set enable_fast_query_shipping=true;
+
 -- create some tables
 create table t1(val int, val2 int);
 create table t2(val int, val2 int);
@@ -133,8 +176,6 @@ end;
 
 -- prepare a transaction that holds a ACCESS EXCLUSIVE (ROW SHARE) lock on a table
 begin;
--- This lock is here due to bug ID 3517977
-lock table mytab4 in ACCESS EXCLUSIVE mode nowait;
 declare c1 cursor for select * from mytab1 for update;
 fetch 1 from c1;
 prepare transaction 'tbl_mytab1_locked';
@@ -254,8 +295,6 @@ end;
 
 -- prepare a transaction that holds a ACCESS SHARE (ROW SHARE) lock on a table
 begin;
--- This statement is here due to bug ID 3517977
-lock table mytab4 in ACCESS EXCLUSIVE mode nowait;
 declare c1 cursor for select * from mytab1 for share;
 fetch 1 from c1;
 prepare transaction 'tbl_mytab1_locked';
@@ -360,3 +399,510 @@ drop table mytab1 cascade;
 drop table mytab4 cascade;
 drop table mytab3 cascade;
 
+-- Test to make sure prepared transactions are working as expected
+-- If a transcation is preared and contains only a select with for share/update, it should be preapred on data nodes
+
+-- create some tables
+create table t1(val int, val2 int) DISTRIBUTE BY REPLICATION;
+create table t2(val int, val2 int) DISTRIBUTE BY REPLICATION;
+create table t3(val int, val2 int) DISTRIBUTE BY REPLICATION;
+
+create table p1(a int, b int) DISTRIBUTE BY REPLICATION;
+create table c1(d int, e int) inherits (p1) DISTRIBUTE BY REPLICATION;
+
+-- insert some rows in them
+insert into t1 values(1,11),(2,11);
+insert into t2 values(3,11),(4,11);
+insert into t3 values(5,11),(6,11);
+
+insert into p1 values(55,66),(77,88);
+insert into c1 values(111,222,333,444),(123,345,567,789);
+
+-- ****  
+
+begin;
+  select * from t1 order by 1 for update of t1 nowait;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1, t2, t3 order by 1 for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1 FOR UPDATE) SELECT * FROM q1,t2 order by 1 FOR UPDATE;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1) SELECT * FROM q1 FOR UPDATE;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- false
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1 FOR UPDATE) SELECT * FROM q1 FOR UPDATE;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1, t2 where t1.val = t2.val for share;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1, t2 for share of t2;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from (select * from t1 for update of t1 nowait) as foo;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 where val in (select val from t2 for update of t2 nowait) for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 where val in (select val from t2 for update of t2 nowait);
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1, t2 for share of t2 for update of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 for share of t1 for update of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 for update of t1 for share of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 for share of t1 for share of t1 for update of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 for share of t1 for share of t1 for share of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 for share of t1 for share of t1 nowait for update of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 a,t1 b for share of a for update of b;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from p1 order by 1 for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from p1 for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); --true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from c1 order by 1 for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from c1 for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- **********************************
+-- repeat all tests with FQS disabled
+-- **********************************
+
+set enable_fast_query_shipping=false;
+
+-- ****  
+
+begin;
+  select * from t1 order by 1 for update of t1 nowait;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1, t2, t3 order by 1 for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1 FOR UPDATE) SELECT * FROM q1,t2 order by 1 FOR UPDATE;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1) SELECT * FROM q1 FOR UPDATE;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- false
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  WITH q1 AS (SELECT * from t1 order by 1 FOR UPDATE) SELECT * FROM q1 FOR UPDATE;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1, t2 where t1.val = t2.val for share;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1, t2 for share of t2;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from (select * from t1 for update of t1 nowait) as foo;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 where val in (select val from t2 for update of t2 nowait) for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 where val in (select val from t2 for update of t2 nowait);
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1, t2 for share of t2 for update of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 for share of t1 for update of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 for update of t1 for share of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 for share of t1 for share of t1 for update of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 for share of t1 for share of t1 for share of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 for share of t1 for share of t1 nowait for update of t1;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from t1 a,t1 b for share of a for update of b;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from p1 order by 1 for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from p1 for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); --true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from c1 order by 1 for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+begin;
+  select * from c1 for update;
+prepare transaction 'pt_1';
+
+select gid from pg_prepared_xacts where gid = 'pt_1';
+select is_prepared_on_node('pt_1', 1); -- true
+
+commit prepared 'pt_1';
+
+-- ****  
+
+set enable_fast_query_shipping=true;
+
+-- ****  
+
+delete from t3 where val != 5;
+
+PREPARE my_plan(int) as select * from t3 for update;
+execute my_plan(1);
+
+-- ****  
+
+-- drop objects created
+drop table c1;
+drop table p1;
+drop table t1;
+drop table t2;
+drop table t3;
+