Send down comamnd ID even before an utility statement is sent

We have seen corner cases where an utility statement on a datanode will
increment local command ID counter (say, because of cascade drop), but the same
is conveyed back to the remote coordinator. Later when coordinator sends down a
lower numbered command ID, catalog access fails to see an object created in the
same transaction.

This fixes the case reported by Aaron Jackson. But we would do further
refactoring on the command ID management to ensure all corner cases are handled
properly.

3 files changed, 59 insertions, 1 deletions

diff --git a/src/backend/pgxc/pool/execRemote.c b/src/backend/pgxc/pool/execRemote.c
index 830cc812ef..cc2922cf8f 100644
--- a/src/backend/pgxc/pool/execRemote.c
+++ b/src/backend/pgxc/pool/execRemote.c
@@ -5820,6 +5820,9 @@ ExecRemoteUtility(RemoteQuery *node)
 	bool		need_tran_block;
 	ExecDirectType		exec_direct_type = node->exec_direct_type;
 	int			i;
+#ifdef XCP
+	CommandId	cid = GetCurrentCommandId(false);	
+#endif	
 
 	if (!force_autocommit)
 		RegisterTransactionLocalNode(true);
@@ -5895,8 +5898,15 @@ ExecRemoteUtility(RemoteQuery *node)
 			{
 				ereport(ERROR,
 						(errcode(ERRCODE_INTERNAL_ERROR),
-						 errmsg("Failed to send command to Datanodes")));
+						 errmsg("Failed to send snapshot to Datanodes")));
+			}
+			if (pgxc_node_send_cmd_id(conn, cid) < 0)
+			{
+				ereport(ERROR,
+						(errcode(ERRCODE_INTERNAL_ERROR),
+						 errmsg("Failed to send command ID to Datanodes")));
 			}
+
 			if (pgxc_node_send_query(conn, node->sql_statement) != 0)
 			{
 				ereport(ERROR,
@@ -5924,6 +5934,13 @@ ExecRemoteUtility(RemoteQuery *node)
 						(errcode(ERRCODE_INTERNAL_ERROR),
 						 errmsg("Failed to send command to coordinators")));
 			}
+			if (pgxc_node_send_cmd_id(pgxc_connections->coord_handles[i], cid) < 0)
+			{
+				ereport(ERROR,
+						(errcode(ERRCODE_INTERNAL_ERROR),
+						 errmsg("Failed to send command ID to Datanodes")));
+			}
+
 			if (pgxc_node_send_query(pgxc_connections->coord_handles[i], node->sql_statement) != 0)
 			{
 				ereport(ERROR,
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 497248d921..a0159cd352 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -4809,6 +4809,42 @@ SELECT TestJoinTempTable();
  
 (1 row)
 
+-- Multiple invokations of the function showed interesting issues with command
+-- passdown. So add that to the test case
+SELECT TestJoinTempTable();
+NOTICE:  relation "realtable" already exists, skipping
+CONTEXT:  SQL statement "CREATE TABLE IF NOT EXISTS RealTable(ProductId int, ScenarioId int)"
+PL/pgSQL function testjointemptable() line 3 at SQL statement
+NOTICE:  relation "tmpbar" already exists, skipping
+CONTEXT:  SQL statement "CREATE TEMPORARY TABLE IF NOT EXISTS TmpBar(NodeId int)
+                DISTRIBUTE BY REPLICATION"
+PL/pgSQL function testjointemptable() line 6 at SQL statement
+NOTICE:  relation "tmpfoo" already exists, skipping
+CONTEXT:  SQL statement "CREATE TEMPORARY TABLE IF NOT EXISTS TmpFoo(TempId int)
+                DISTRIBUTE BY REPLICATION"
+PL/pgSQL function testjointemptable() line 8 at SQL statement
+ testjointemptable 
+-------------------
+ 
+(1 row)
+
+SELECT TestJoinTempTable();
+NOTICE:  relation "realtable" already exists, skipping
+CONTEXT:  SQL statement "CREATE TABLE IF NOT EXISTS RealTable(ProductId int, ScenarioId int)"
+PL/pgSQL function testjointemptable() line 3 at SQL statement
+NOTICE:  relation "tmpbar" already exists, skipping
+CONTEXT:  SQL statement "CREATE TEMPORARY TABLE IF NOT EXISTS TmpBar(NodeId int)
+                DISTRIBUTE BY REPLICATION"
+PL/pgSQL function testjointemptable() line 6 at SQL statement
+NOTICE:  relation "tmpfoo" already exists, skipping
+CONTEXT:  SQL statement "CREATE TEMPORARY TABLE IF NOT EXISTS TmpFoo(TempId int)
+                DISTRIBUTE BY REPLICATION"
+PL/pgSQL function testjointemptable() line 8 at SQL statement
+ testjointemptable 
+-------------------
+ 
+(1 row)
+
 DROP TABLE RealTable;
 DROP TABLE TmpBar;
 DROP TABLE TmpFoo;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index b62766effa..270feb058f 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -3803,6 +3803,11 @@ $$ LANGUAGE plpgsql;
 
 SELECT TestJoinTempTable();
 
+-- Multiple invokations of the function showed interesting issues with command
+-- passdown. So add that to the test case
+SELECT TestJoinTempTable();
+SELECT TestJoinTempTable();
+
 DROP TABLE RealTable;
 DROP TABLE TmpBar;
 DROP TABLE TmpFoo;