Block creation of SQL functions containing utility statements

To support these functions we need to first implement a deparser
for all utility statements and then use the deparsed utility statement
while adding remote query node in ProcessUtility function.
Currently we are using "queryString" which can contain multiple
statements in case of SQL functions, some of which can be utility
and the last one has to be a select. Having such a combination in
a single remote query node creates problems while processing results.

Since that deparser is not there yet, we have to block creation of such SQL functions.

By setting the GUC check_function_bodies of OFF, one can by-pass the code
to block the creation of such functions. This patch makes the necessary
changes to make sure that such SQL functions do not produce any crash,
although they still won't run successfully.

The documentation is changed to let the users know of this limitation.

A few test cases are also added.

6 files changed, 127 insertions, 9 deletions

diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 704f9d277d..a3c6bca2d3 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -866,6 +866,11 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
 				/* Check if the list of queries contains temporary objects */
 				if (IS_PGXC_COORDINATOR && !IsConnFromCoord())
 				{
+					if (pgxc_query_contains_utility(querytree_sublist))
+						ereport(ERROR,
+								(errcode(ERRCODE_SYNTAX_ERROR),
+								errmsg("In XC, SQL functions cannot contain utility statements")));
+
 					if (pgxc_query_contains_temp_tables(querytree_sublist))
 						ExecSetTempObjectIncluded();
 				}
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 8ca9458012..9f4503dcc1 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -309,15 +309,18 @@ init_execution_state(List *queryTree_list,
 #ifdef PGXC
 			if (IS_PGXC_COORDINATOR && !IsConnFromCoord())
 			{
-				/*
-				 * The parameterised queries in RemoteQuery nodes will be prepared
-				 * on the datanode, and need parameter types for the same. Set the
-				 * parameter types and their number in all RemoteQuery nodes in the
-				 * plan
-				 */
-				SetRemoteStatementName(((PlannedStmt *)stmt)->planTree, NULL,
-										fcache->pinfo->nargs,
-										fcache->pinfo->argtypes, 0);
+				if (queryTree->commandType != CMD_UTILITY)
+				{
+					/*
+					* The parameterised queries in RemoteQuery nodes will be prepared
+					* on the datanode, and need parameter types for the same. Set the
+					* parameter types and their number in all RemoteQuery nodes in the
+					* plan
+					*/
+					SetRemoteStatementName(((PlannedStmt *)stmt)->planTree, NULL,
+											fcache->pinfo->nargs,
+											fcache->pinfo->argtypes, 0);
+				}
 			}
 #endif /* PGXC */
 
diff --git a/src/backend/pgxc/plan/planner.c b/src/backend/pgxc/plan/planner.c
index c753e05f11..d3f57944c2 100644
--- a/src/backend/pgxc/plan/planner.c
+++ b/src/backend/pgxc/plan/planner.c
@@ -2157,3 +2157,27 @@ pgxc_query_contains_temp_tables(List *queries)
 
 	return false;
 }
+
+/*
+ * pgxc_query_contains_utility
+ *
+ * Check if there is any utility statement in given list of queries.
+ */
+bool
+pgxc_query_contains_utility(List *queries)
+{
+	ListCell   *elt;
+
+	foreach(elt, queries)
+	{
+		Query *query = (Query *) lfirst(elt);
+
+		if (!query)
+			continue;
+
+		if (query->commandType == CMD_UTILITY)
+			return true;
+	}
+
+	return false;
+}
diff --git a/src/include/pgxc/planner.h b/src/include/pgxc/planner.h
index db2ee3046a..c9310b4b0e 100644
--- a/src/include/pgxc/planner.h
+++ b/src/include/pgxc/planner.h
@@ -172,6 +172,7 @@ extern ExecNodes *IsJoinReducible(RemoteQuery *innernode, RemoteQuery *outernode
 extern List *AddRemoteQueryNode(List *stmts, const char *queryString,
 								RemoteQueryExecType remoteExecType, bool is_temp);
 extern bool pgxc_query_contains_temp_tables(List *queries);
+extern bool pgxc_query_contains_utility(List *queries);
 extern Expr *pgxc_find_distcol_expr(Index varno, PartAttrNumber partAttrNum,
 												Node *quals);
 
diff --git a/src/test/regress/expected/xc_misc.out b/src/test/regress/expected/xc_misc.out
index 3e56f3913b..e714e8d299 100644
--- a/src/test/regress/expected/xc_misc.out
+++ b/src/test/regress/expected/xc_misc.out
@@ -101,3 +101,43 @@ ERROR:  column name "xc_node_id" conflicts with a system column name
 create table t2(a int , b int) distribute by modulo(xc_node_id);
 ERROR:  Column xc_node_id is not modulo distributable data type
 drop table t1;
+-- Test an SQL function with multiple statements in it including a utility statement.
+create table my_tab1 (a int);
+insert into my_tab1 values(1);
+create function f1 () returns setof my_tab1 as $$ create table my_tab2 (a int); select * from my_tab1; $$ language sql;
+ERROR:  In XC, SQL functions cannot contain utility statements
+CONTEXT:  SQL function "f1"
+SET check_function_bodies = false;
+create function f1 () returns setof my_tab1 as $$ create table my_tab2 (a int); select * from my_tab1; $$ language sql;
+select f1();
+ERROR:  Unexpected response from the data nodes for 'T' message, current request type 1
+CONTEXT:  SQL function "f1" statement 1
+SET check_function_bodies = true;
+drop function f1();
+-- Test pl-pgsql functions containing utility statements
+CREATE OR REPLACE FUNCTION test_fun_2() RETURNS SETOF my_tab1 AS '
+DECLARE
+   t1 my_tab1;
+   occ RECORD;
+BEGIN
+   CREATE TABLE tab4(a int);
+   CREATE TABLE tab5(a int);
+
+   FOR occ IN SELECT * FROM my_tab1
+   LOOP
+     t1.a := occ.a;
+     RETURN NEXT t1;
+   END LOOP;
+
+   RETURN;
+END;' LANGUAGE 'plpgsql';
+select test_fun_2();
+ test_fun_2 
+------------
+ (1)
+(1 row)
+
+drop function test_fun_2();
+drop table tab4;
+drop table tab5;
+drop table my_tab1;
diff --git a/src/test/regress/sql/xc_misc.sql b/src/test/regress/sql/xc_misc.sql
index 84f97d569d..2dfcf599f2 100644
--- a/src/test/regress/sql/xc_misc.sql
+++ b/src/test/regress/sql/xc_misc.sql
@@ -72,3 +72,48 @@ create table t2(a int , b int) distribute by modulo(xc_node_id);
 
 drop table t1;
 
+-- Test an SQL function with multiple statements in it including a utility statement.
+
+create table my_tab1 (a int);
+
+insert into my_tab1 values(1);
+
+create function f1 () returns setof my_tab1 as $$ create table my_tab2 (a int); select * from my_tab1; $$ language sql;
+
+SET check_function_bodies = false;
+
+create function f1 () returns setof my_tab1 as $$ create table my_tab2 (a int); select * from my_tab1; $$ language sql;
+
+select f1();
+
+SET check_function_bodies = true;
+
+drop function f1();
+
+-- Test pl-pgsql functions containing utility statements
+
+CREATE OR REPLACE FUNCTION test_fun_2() RETURNS SETOF my_tab1 AS '
+DECLARE
+   t1 my_tab1;
+   occ RECORD;
+BEGIN
+   CREATE TABLE tab4(a int);
+   CREATE TABLE tab5(a int);
+
+   FOR occ IN SELECT * FROM my_tab1
+   LOOP
+     t1.a := occ.a;
+     RETURN NEXT t1;
+   END LOOP;
+
+   RETURN;
+END;' LANGUAGE 'plpgsql';
+
+select test_fun_2();
+
+drop function test_fun_2();
+
+drop table tab4;
+drop table tab5;
+drop table my_tab1;
+