Add support for recursive CTE for replicated tables

12 files changed, 739 insertions, 109 deletions

diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index e49a07356d..dd9755b05e 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -32,6 +32,7 @@
 #include "optimizer/clauses.h"
 #include "optimizer/cost.h"
 #include "optimizer/paths.h"
+#include "optimizer/pathnode.h"
 #include "optimizer/placeholder.h"
 #include "optimizer/plancat.h"
 #include "optimizer/planmain.h"
@@ -1909,20 +1910,64 @@ create_remotescan_plan(PlannerInfo *root,
 }
 
 
-RemoteSubplan *
-find_push_down_plan(Plan *plan, bool force)
+static RemoteSubplan *
+find_push_down_plan_int(PlannerInfo *root, Plan *plan, bool force, Plan **parent)
 {
 	if (IsA(plan, RemoteSubplan) &&
 			(force || (list_length(((RemoteSubplan *) plan)->nodeList) > 1 &&
 					   ((RemoteSubplan *) plan)->execOnAll)))
+	{
+		if (parent)
+			*parent = plan->lefttree;
 		return (RemoteSubplan *) plan;
+	}
+
 	if (IsA(plan, Hash) ||
 			IsA(plan, Material) ||
 			IsA(plan, Unique) ||
 			IsA(plan, Limit))
-		return find_push_down_plan(plan->lefttree, force);
+		return find_push_down_plan_int(root, plan->lefttree, force, &plan->lefttree);
+
+	/*
+	 * If its a subquery scan then walk down the subplan to find a
+	 * RemoteSubplan
+	 */
+	if (IsA(plan, SubqueryScan))
+	{
+		Plan *subplan = ((SubqueryScan *)plan)->subplan;
+		Plan *remote_plan = find_push_down_plan_int(root, ((SubqueryScan *)plan)->subplan, force,
+				&((SubqueryScan *)plan)->subplan);
+
+		/*
+		 * If caller has asked for removing the RemoteSubplan and if its a
+		 * subquery plan, then we must also update the link stored in the
+		 * RelOptInfo corresponding to this subquery
+		 */
+		if ((remote_plan == subplan) && parent)
+		{
+			Assert(root);
+			RelOptInfo *rel = find_base_rel(root, ((SubqueryScan *)plan)->scan.scanrelid);
+			rel->subplan = ((SubqueryScan *)plan)->subplan;
+		}
+		return remote_plan;
+	}
 	return NULL;
 }
+
+RemoteSubplan *
+find_push_down_plan(Plan *plan, bool force)
+{
+	return find_push_down_plan_int(NULL, plan, force, NULL);
+}
+
+RemoteSubplan *
+find_delete_push_down_plan(PlannerInfo *root,
+		Plan *plan,
+		bool force,
+		Plan **parent)
+{
+	return find_push_down_plan_int(root, plan, force, parent);
+}
 #endif
 
 
@@ -4988,7 +5033,11 @@ make_append(List *appendplans, List *tlist)
 }
 
 RecursiveUnion *
-make_recursive_union(List *tlist,
+make_recursive_union(
+#ifdef XCP
+					 PlannerInfo *root,
+#endif					 
+					 List *tlist,
 					 Plan *lefttree,
 					 Plan *righttree,
 					 int wtParam,
@@ -4998,6 +5047,9 @@ make_recursive_union(List *tlist,
 	RecursiveUnion *node = makeNode(RecursiveUnion);
 	Plan	   *plan = &node->plan;
 	int			numCols = list_length(distinctList);
+#ifdef XCP	
+	RemoteSubplan *left_pushdown, *right_pushdown;
+#endif	
 
 	cost_recursive_union(plan, lefttree, righttree);
 
@@ -5038,6 +5090,44 @@ make_recursive_union(List *tlist,
 	}
 	node->numGroups = numGroups;
 
+#ifdef XCP	
+	/*
+	 * For recursive CTE, we have already checked that all tables involved in
+	 * the query are replicated tables (or coordinator local tables such as
+	 * catalog tables). So drill down the left and right plan trees and find
+	 * the corresponding remote subplan(s). If both sides contain a
+	 * RemoteSubplan then its possible that they are marked for execution on
+	 * different nodes, but that does not matter since tables are replicated
+	 * and nodes are picked randomly for replicated tables. So just reuse
+	 * either of the RemoteSubplan and pin the RecursiveUnion plan generated
+	 * above to the RemoteSubplan. They must have been already removed from the
+	 * subtree by find_delete_push_down_plan function
+	 *
+	 * XXX For tables replicated on different subsets of nodes, this may not
+	 * work. In fact, we probably can't support recursive queries for such
+	 * tables.
+	 */
+	left_pushdown = find_delete_push_down_plan(root, lefttree, true, &plan->lefttree);
+	right_pushdown = find_delete_push_down_plan(root, righttree, true, &plan->righttree);
+	if (left_pushdown || right_pushdown)
+	{
+		/* Pick either one */
+		if (!left_pushdown)
+			left_pushdown = right_pushdown;
+
+		/*
+		 * Push the RecursiveUnion to the remote node
+		 */
+		left_pushdown->scan.plan.lefttree = plan;
+
+		/*
+		 * The only caller for this function does not really care if the
+		 * returned node is RecursiveUnion or not. So we just return the
+		 * RemoteSubplan as it
+		 */
+		return (RecursiveUnion *) left_pushdown;
+	}
+#endif	
 	return node;
 }
 
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 79dc1dab43..9dca3a25cc 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -364,6 +364,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
 	List	   *newHaving;
 	bool		hasOuterJoins;
 	ListCell   *l;
+	bool recursiveOk = true;
 
 	/* Create a PlannerInfo data structure for this subquery */
 	root = makeNode(PlannerInfo);
@@ -378,6 +379,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
 	root->append_rel_list = NIL;
 	root->rowMarks = NIL;
 	root->hasInheritedTarget = false;
+	root->recursiveOk = true;
 
 #ifdef PGXC
 #ifndef XCP
@@ -687,52 +689,63 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
 	 * Temporarily block WITH RECURSIVE for most cases 
 	 * until we can fix. Allow for pg_catalog tables and replicated tables.
 	 */
-	if (root->hasRecursion)
 	{
 		int idx;
-		bool recursiveOk = true;
+		recursiveOk = true;
 
 		/* seems to start at 1... */
-		for (idx = 1; idx < root->simple_rel_array_size - 1; idx++)
+		for (idx = 1; idx < root->simple_rel_array_size - 1 && recursiveOk; idx++)
 		{
 			RangeTblEntry *rte;
 
-			rte = root->simple_rte_array[idx];
-
-			if (!rte || rte->rtekind == RTE_JOIN)
-			{
-				continue;
-			}
-			else if (rte->rtekind == RTE_RELATION)
+			rte = planner_rt_fetch(idx, root);
+			if (!rte)
+			   continue;
+		
+			switch (rte->rtekind)
 			{
-				char loc_type;
+				case RTE_JOIN:
+				case RTE_VALUES:
+				case RTE_CTE:
+					continue;
+				case RTE_RELATION:
+					{
+						char loc_type;
 
-				loc_type = GetRelationLocType(rte->relid);
+						loc_type = GetRelationLocType(rte->relid);
 
-				/* skip pg_catalog */
-				if (loc_type == LOCATOR_TYPE_NONE)
-					continue;
+						/* skip pg_catalog */
+						if (loc_type == LOCATOR_TYPE_NONE)
+							continue;
 
-				/* If replicated, allow */
-				if (IsLocatorReplicated(loc_type))
-				{
-					continue;
-				}
-				else
-				{
+						/* If replicated, allow */
+						if (IsLocatorReplicated(loc_type))
+							continue;
+						else
+							recursiveOk = false;
+						break;
+					} 
+				case RTE_SUBQUERY:
+					{
+						RelOptInfo *relOptInfo = root->simple_rel_array[idx];
+						if (relOptInfo && relOptInfo->subroot &&
+								!relOptInfo->subroot->recursiveOk)
+							recursiveOk = false;
+						break;
+					}
+				default:	
 					recursiveOk = false;
 					break;
-				}
-			} 
-			else  
-			{
-				recursiveOk = false;
-				break;
 			}
 		}
-		if (!recursiveOk)
-			elog(ERROR, "WITH RECURSIVE currently not supported on distributed tables.");
 	}
+
+	if (root->recursiveOk)
+		root->recursiveOk = recursiveOk;
+
+	if (root->hasRecursion && !root->recursiveOk)
+			elog(ERROR, "WITH RECURSIVE currently not supported on distributed tables.");
+
 	return plan;
 }
 
@@ -3726,6 +3739,7 @@ plan_cluster_use_sort(Oid tableOid, Oid indexOid)
 	root->query_level = 1;
 	root->planner_cxt = CurrentMemoryContext;
 	root->wt_param_id = -1;
+	root->recursiveOk = true;
 
 	/* Build a minimal RTE for the rel */
 	rte = makeNode(RangeTblEntry);
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index e4425efc8d..c5262fba26 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -764,6 +764,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
 	subroot->hasRecursion = false;
 	subroot->wt_param_id = -1;
 	subroot->non_recursive_plan = NULL;
+	subroot->recursiveOk = true;
 
 	/* No CTEs to worry about */
 	Assert(subquery->cteList == NIL);
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index a77b86d0e8..47e331cb59 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -263,6 +263,9 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
 		rel->subplan = subplan;
 		rel->subroot = subroot;
 
+		if (root->recursiveOk)	
+			root->recursiveOk = subroot->recursiveOk;
+
 		/*
 		 * Estimate number of groups if caller wants it.  If the subquery used
 		 * grouping or aggregation, its output is probably mostly unique
@@ -434,7 +437,7 @@ generate_recursion_plan(SetOperationStmt *setOp, PlannerInfo *root,
 	/*
 	 * And make the plan node.
 	 */
-	plan = (Plan *) make_recursive_union(tlist, lplan, rplan,
+	plan = (Plan *) make_recursive_union(root, tlist, lplan, rplan,
 										 root->wt_param_id,
 										 groupList, numGroups);
 
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 670d98c3a2..24ce14d56b 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -419,6 +419,7 @@ parseCheckAggregates(ParseState *pstate, Query *qry)
 		root->parse = qry;
 		root->planner_cxt = CurrentMemoryContext;
 		root->hasJoinRTEs = true;
+		root->recursiveOk = true;
 
 		groupClauses = (List *) flatten_join_alias_vars(root,
 													  (Node *) groupClauses);
diff --git a/src/backend/pgxc/plan/planner.c b/src/backend/pgxc/plan/planner.c
index 8fa92df5db..019f11257f 100644
--- a/src/backend/pgxc/plan/planner.c
+++ b/src/backend/pgxc/plan/planner.c
@@ -473,6 +473,7 @@ pgxc_handle_exec_direct(Query *query, int cursorOptions,
 			root->glob = glob;
 			root->query_level = 1;
 			root->planner_cxt = CurrentMemoryContext;
+			root->recursiveOk = true;
 			/* build the PlannedStmt result */
 			result = makeNode(PlannedStmt);
 			/* Try and set what we can, rest must have been zeroed out by makeNode() */
@@ -580,6 +581,7 @@ pgxc_FQS_planner(Query *query, int cursorOptions, ParamListInfo boundParams)
 	root->glob = glob;
 	root->query_level = 1;
 	root->planner_cxt = CurrentMemoryContext;
+	root->recursiveOk = true;
 
 	/*
 	 * We decided to ship the query to the Datanode/s, create a RemoteQuery node
diff --git a/src/backend/pgxc/pool/execRemote.c b/src/backend/pgxc/pool/execRemote.c
index 601e0bbf47..1263b3b574 100644
--- a/src/backend/pgxc/pool/execRemote.c
+++ b/src/backend/pgxc/pool/execRemote.c
@@ -7807,7 +7807,6 @@ RemoteSubplanMakeUnique(Node *plan, int unique)
 		((RemoteSubplan *)plan)->unique = unique;
 	}
 	/* Otherwise it is a Plan descendant */
-	RemoteSubplanMakeUnique((Node *) ((Plan *) plan)->initPlan, unique);
 	RemoteSubplanMakeUnique((Node *) ((Plan *) plan)->lefttree, unique);
 	RemoteSubplanMakeUnique((Node *) ((Plan *) plan)->righttree, unique);
 	/* Tranform special cases */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e7a4e826c5..4b1b00bd28 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -170,6 +170,7 @@ typedef struct Query
 						 * this is used by EXECUTE DIRECT especially. */
 	bool		is_ins_child_sel_parent;/* true if the query is such an INSERT SELECT that
 						 * inserts into a child by selecting from its parent */
+	bool		recursiveOK;	/* does query support WITH RECURSIVE */
 #endif
 #endif
 } Query;
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index f036ead7dd..97361105db 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -289,6 +289,7 @@ typedef struct PlannerInfo
 	 * to the target table distribution.
 	 */
 	Distribution *distribution; /* Query result distribution */
+	bool		recursiveOk;
 #endif
 } PlannerInfo;
 
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 39a5650eb6..d26c12dcfe 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -53,7 +53,7 @@ extern SubqueryScan *make_subqueryscan(List *qptlist, List *qpqual,
 extern ForeignScan *make_foreignscan(List *qptlist, List *qpqual,
 				 Index scanrelid, List *fdw_exprs, List *fdw_private);
 extern Append *make_append(List *appendplans, List *tlist);
-extern RecursiveUnion *make_recursive_union(List *tlist,
+extern RecursiveUnion *make_recursive_union(PlannerInfo *root, List *tlist,
 					 Plan *lefttree, Plan *righttree, int wtParam,
 					 List *distinctList, long numGroups);
 extern Sort *make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree,
@@ -139,6 +139,8 @@ extern void extract_query_dependencies(Node *query,
 #ifdef PGXC
 #ifdef XCP
 extern RemoteSubplan *find_push_down_plan(Plan *plan, bool force);
+extern RemoteSubplan *find_delete_push_down_plan(PlannerInfo *root, Plan *plan,
+		bool force, Plan **parent);
 extern RemoteSubplan *make_remotesubplan(PlannerInfo *root,
 				   Plan *lefttree,
 				   Distribution *resultDistribution,
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 6e0433e9bc..832ba52d53 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -29,35 +29,78 @@ UNION ALL
     SELECT n+1 FROM t WHERE n < 100
 )
 SELECT sum(n) FROM t;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ sum  
+------
+ 5050
+(1 row)
+
 WITH RECURSIVE t(n) AS (
     SELECT (VALUES(1))
 UNION ALL
     SELECT n+1 FROM t WHERE n < 5
 )
 SELECT * FROM t ORDER BY n;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ n 
+---
+ 1
+ 2
+ 3
+ 4
+ 5
+(5 rows)
+
 -- This is an infinite loop with UNION ALL, but not with UNION
 WITH RECURSIVE t(n) AS (
     SELECT 1
 UNION
     SELECT 10-n FROM t)
 SELECT * FROM t ORDER BY n;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ n 
+---
+ 1
+ 9
+(2 rows)
+
 -- This'd be an infinite loop, but outside query reads only as much as needed
 WITH RECURSIVE t(n) AS (
     VALUES (1)
 UNION ALL
     SELECT n+1 FROM t)
 SELECT * FROM t LIMIT 10;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ n  
+----
+  1
+  2
+  3
+  4
+  5
+  6
+  7
+  8
+  9
+ 10
+(10 rows)
+
 -- UNION case should have same property
 WITH RECURSIVE t(n) AS (
     SELECT 1
 UNION
     SELECT n+1 FROM t)
 SELECT * FROM t LIMIT 10;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ n  
+----
+  1
+  2
+  3
+  4
+  5
+  6
+  7
+  8
+  9
+ 10
+(10 rows)
+
 -- Test behavior with an unknown-type literal in the WITH
 WITH q AS (SELECT 'foo' AS x)
 SELECT x, x IS OF (unknown) as is_unknown FROM q;
@@ -72,7 +115,16 @@ UNION ALL
     SELECT n || ' bar' FROM t WHERE length(n) < 20
 )
 SELECT n, n IS OF (text) as is_text FROM t ORDER BY n;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+            n            | is_text 
+-------------------------+---------
+ foo                     | t
+ foo bar                 | t
+ foo bar bar             | t
+ foo bar bar bar         | t
+ foo bar bar bar bar     | t
+ foo bar bar bar bar bar | t
+(6 rows)
+
 --
 -- Some examples with a tree
 --
@@ -86,7 +138,7 @@ CREATE TEMP TABLE department (
 	id INTEGER PRIMARY KEY,  -- department ID
 	parent_department INTEGER ,
 	name TEXT -- department name
-);
+) DISTRIBUTE BY REPLICATION;
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "department_pkey" for table "department"
 INSERT INTO department VALUES (0, NULL, 'ROOT');
 INSERT INTO department VALUES (1, 0, 'A');
@@ -107,7 +159,15 @@ WITH RECURSIVE subdepartment AS
 		WHERE d.parent_department = sd.id
 )
 SELECT * FROM subdepartment ORDER BY name;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ root_name | id | parent_department | name 
+-----------+----+-------------------+------
+ A         |  1 |                 0 | A
+ A         |  2 |                 1 | B
+ A         |  3 |                 2 | C
+ A         |  4 |                 2 | D
+ A         |  6 |                 4 | F
+(5 rows)
+
 -- extract all departments under 'A' with "level" number
 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
 (
@@ -119,7 +179,15 @@ WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
 		WHERE d.parent_department = sd.id
 )
 SELECT * FROM subdepartment ORDER BY name;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ level | id | parent_department | name 
+-------+----+-------------------+------
+     1 |  1 |                 0 | A
+     2 |  2 |                 1 | B
+     3 |  3 |                 2 | C
+     3 |  4 |                 2 | D
+     4 |  6 |                 4 | F
+(5 rows)
+
 -- extract all departments under 'A' with "level" number.
 -- Only shows level 2 or more
 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
@@ -132,7 +200,14 @@ WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
 		WHERE d.parent_department = sd.id
 )
 SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ level | id | parent_department | name 
+-------+----+-------------------+------
+     2 |  2 |                 1 | B
+     3 |  3 |                 2 | C
+     3 |  4 |                 2 | D
+     4 |  6 |                 4 | F
+(4 rows)
+
 -- "RECURSIVE" is ignored if the query has no self-reference
 WITH RECURSIVE subdepartment AS
 (
@@ -157,7 +232,11 @@ SELECT count(*) FROM (
                 )
             SELECT * FROM t WHERE n < 50000
          ) AS t WHERE n < 100);
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ count 
+-------
+    98
+(1 row)
+
 -- use same CTE twice at different subquery levels
 WITH q1(x,y) AS (
     SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
@@ -181,7 +260,15 @@ CREATE TEMPORARY VIEW vsubdepartment AS
 	)
 	SELECT * FROM subdepartment;
 SELECT * FROM vsubdepartment ORDER BY name;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id | parent_department | name 
+----+-------------------+------
+  1 |                 0 | A
+  2 |                 1 | B
+  3 |                 2 | C
+  4 |                 2 | D
+  6 |                 4 | F
+(5 rows)
+
 -- Check reverse listing
 SELECT pg_get_viewdef('vsubdepartment'::regclass);
                                                                                                                                                                                     pg_get_viewdef                                                                                                                                                                                     
@@ -214,7 +301,34 @@ select * from (with recursive q as (
        select * from x)
     )
 select * from q limit 24) rel_alias order by 1, 2, 3;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id | parent_department | name 
+----+-------------------+------
+  0 |                   | ROOT
+  0 |                   | ROOT
+  0 |                   | ROOT
+  1 |                 0 | A
+  1 |                 0 | A
+  1 |                 0 | A
+  2 |                 1 | B
+  2 |                 1 | B
+  2 |                 1 | B
+  3 |                 2 | C
+  3 |                 2 | C
+  3 |                 2 | C
+  4 |                 2 | D
+  4 |                 2 | D
+  4 |                 2 | D
+  5 |                 0 | E
+  5 |                 0 | E
+  5 |                 0 | E
+  6 |                 4 | F
+  6 |                 4 | F
+  6 |                 4 | F
+  7 |                 5 | G
+  7 |                 5 | G
+  7 |                 5 | G
+(24 rows)
+
 select * from (with recursive q as (
       (select * from department order by id)
     union all
@@ -226,7 +340,42 @@ select * from (with recursive q as (
        select * from x)
     )
 select * from q limit 32) rel_alias order by 1, 2, 3;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id | parent_department | name 
+----+-------------------+------
+  0 |                   | ROOT
+  0 |                   | ROOT
+  0 |                   | ROOT
+  0 |                   | ROOT
+  1 |                 0 | A
+  1 |                 0 | A
+  1 |                 0 | A
+  1 |                 0 | A
+  2 |                 1 | B
+  2 |                 1 | B
+  2 |                 1 | B
+  2 |                 1 | B
+  3 |                 2 | C
+  3 |                 2 | C
+  3 |                 2 | C
+  3 |                 2 | C
+  4 |                 2 | D
+  4 |                 2 | D
+  4 |                 2 | D
+  4 |                 2 | D
+  5 |                 0 | E
+  5 |                 0 | E
+  5 |                 0 | E
+  5 |                 0 | E
+  6 |                 4 | F
+  6 |                 4 | F
+  6 |                 4 | F
+  6 |                 4 | F
+  7 |                 5 | G
+  7 |                 5 | G
+  7 |                 5 | G
+  7 |                 5 | G
+(32 rows)
+
 -- recursive term has sub-UNION
 WITH RECURSIVE t(i,j) AS (
 	VALUES (1,2)
@@ -235,14 +384,20 @@ WITH RECURSIVE t(i,j) AS (
 		(SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
 		JOIN t ON (t2.i = t.i+1))
 	SELECT * FROM t order by i;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ i | j 
+---+---
+ 1 | 2
+ 2 | 3
+ 3 | 4
+(3 rows)
+
 --
 -- different tree example
 --
 CREATE TEMPORARY TABLE tree(
     id INTEGER PRIMARY KEY,
     parent_id INTEGER 
-);
+) DISTRIBUTE BY REPLICATION;
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tree_pkey" for table "tree"
 INSERT INTO tree
 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
@@ -261,7 +416,23 @@ SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
 	array_upper(t1.path,1) = 1 AND
 	array_upper(t2.path,1) > 1)
 	ORDER BY t1.id, t2.id;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id | path | id |    path     
+----+------+----+-------------
+  2 | {2}  |  4 | {2,4}
+  2 | {2}  |  5 | {2,5}
+  2 | {2}  |  6 | {2,6}
+  2 | {2}  |  9 | {2,4,9}
+  2 | {2}  | 10 | {2,4,10}
+  2 | {2}  | 14 | {2,4,9,14}
+  3 | {3}  |  7 | {3,7}
+  3 | {3}  |  8 | {3,8}
+  3 | {3}  | 11 | {3,7,11}
+  3 | {3}  | 12 | {3,7,12}
+  3 | {3}  | 13 | {3,7,13}
+  3 | {3}  | 15 | {3,7,11,15}
+  3 | {3}  | 16 | {3,7,11,16}
+(13 rows)
+
 -- just count 'em
 WITH RECURSIVE t(id, path) AS (
     VALUES(1,ARRAY[]::integer[])
@@ -275,7 +446,12 @@ SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
 	array_upper(t2.path,1) > 1)
 	GROUP BY t1.id
 	ORDER BY t1.id;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id | count 
+----+-------
+  2 |     6
+  3 |     7
+(2 rows)
+
 -- this variant tickled a whole-row-variable bug in 8.4devel
 WITH RECURSIVE t(id, path) AS (
     VALUES(1,ARRAY[]::integer[])
@@ -285,11 +461,30 @@ UNION ALL
 )
 SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
 (t1.id=t2.id) ORDER BY id;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id |    path     |         t2         
+----+-------------+--------------------
+  1 | {}          | (1,{})
+  2 | {2}         | (2,{2})
+  3 | {3}         | (3,{3})
+  4 | {2,4}       | (4,"{2,4}")
+  5 | {2,5}       | (5,"{2,5}")
+  6 | {2,6}       | (6,"{2,6}")
+  7 | {3,7}       | (7,"{3,7}")
+  8 | {3,8}       | (8,"{3,8}")
+  9 | {2,4,9}     | (9,"{2,4,9}")
+ 10 | {2,4,10}    | (10,"{2,4,10}")
+ 11 | {3,7,11}    | (11,"{3,7,11}")
+ 12 | {3,7,12}    | (12,"{3,7,12}")
+ 13 | {3,7,13}    | (13,"{3,7,13}")
+ 14 | {2,4,9,14}  | (14,"{2,4,9,14}")
+ 15 | {3,7,11,15} | (15,"{3,7,11,15}")
+ 16 | {3,7,11,16} | (16,"{3,7,11,16}")
+(16 rows)
+
 --
 -- test cycle detection
 --
-create temp table graph( f int, t int, label text );
+create temp table graph( f int, t int, label text ) DISTRIBUTE BY REPLICATION;
 insert into graph values
 	(1, 2, 'arc 1 -> 2'),
 	(1, 3, 'arc 1 -> 3'),
@@ -323,27 +518,65 @@ WITH RECURSIVE
   y (id) AS (VALUES (1)),
   x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
 SELECT * FROM x ORDER BY id;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id 
+----
+  1
+  2
+  3
+  4
+  5
+(5 rows)
+
 -- forward reference OK
 WITH RECURSIVE
     x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
     y(id) AS (values (1))
  SELECT * FROM x ORDER BY id;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id 
+----
+  1
+  2
+  3
+  4
+  5
+(5 rows)
+
 WITH RECURSIVE
    x(id) AS
      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
    y(id) AS
      (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
  SELECT y.*, x.* FROM y LEFT JOIN x USING (id) ORDER BY 1;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id | id 
+----+----
+  1 |  1
+  2 |  2
+  3 |  3
+  4 |  4
+  5 |  5
+  6 |   
+  7 |   
+  8 |   
+  9 |   
+ 10 |   
+(10 rows)
+
 WITH RECURSIVE
    x(id) AS
      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
    y(id) AS
      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
  SELECT y.*, x.* FROM y LEFT JOIN x USING (id) ORDER BY 1;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id | id 
+----+----
+  1 |  1
+  2 |  2
+  3 |  3
+  4 |  4
+  5 |  5
+  6 |   
+(6 rows)
+
 WITH RECURSIVE
    x(id) AS
      (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
@@ -352,7 +585,37 @@ WITH RECURSIVE
    z(id) AS
      (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
  SELECT * FROM z ORDER BY id;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id 
+----
+  1
+  2
+  2
+  3
+  3
+  3
+  4
+  4
+  4
+  5
+  5
+  5
+  6
+  6
+  6
+  7
+  7
+  7
+  8
+  8
+  8
+  9
+  9
+  9
+ 10
+ 10
+ 10
+(27 rows)
+
 WITH RECURSIVE
    x(id) AS
      (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
@@ -361,11 +624,68 @@ WITH RECURSIVE
    z(id) AS
      (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
  SELECT * FROM z ORDER BY id;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id 
+----
+  1
+  1
+  2
+  2
+  2
+  2
+  3
+  3
+  3
+  3
+  3
+  3
+  4
+  4
+  4
+  4
+  4
+  4
+  5
+  5
+  5
+  5
+  5
+  5
+  6
+  6
+  6
+  6
+  6
+  6
+  7
+  7
+  7
+  7
+  7
+  7
+  8
+  8
+  8
+  8
+  8
+  8
+  9
+  9
+  9
+  9
+  9
+  9
+ 10
+ 10
+ 10
+ 10
+ 10
+ 10
+(54 rows)
+
 --
 -- Test WITH attached to a data-modifying statement
 --
-CREATE TEMPORARY TABLE y (a INTEGER);
+CREATE TEMPORARY TABLE y (a INTEGER) DISTRIBUTE BY REPLICATION;
 INSERT INTO y SELECT generate_series(1, 10);
 WITH t AS (
 	SELECT a FROM y
@@ -469,7 +789,7 @@ WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
 ERROR:  recursive reference to query "x" must not appear within its non-recursive term
 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
                                               ^
-CREATE TEMPORARY TABLE y (a INTEGER);
+CREATE TEMPORARY TABLE y (a INTEGER) DISTRIBUTE BY REPLICATION;
 INSERT INTO y SELECT generate_series(1, 10);
 -- LEFT JOIN
 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
@@ -608,7 +928,7 @@ LINE 2:    (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
                    ^
 HINT:  Cast the output of the non-recursive term to the correct type.
 -- disallow OLD/NEW reference in CTE
-CREATE TEMPORARY TABLE x (n integer);
+CREATE TEMPORARY TABLE x (n integer) DISTRIBUTE BY REPLICATION  ;
 CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
     WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
 ERROR:  cannot refer to OLD within WITH query
@@ -667,7 +987,65 @@ WITH RECURSIVE t(j) AS (
     SELECT j+1 FROM t WHERE j < 10
 )
 SELECT * FROM t order by 1;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ j  
+----
+  1
+  2
+  2
+  3
+  3
+  3
+  4
+  4
+  4
+  4
+  5
+  5
+  5
+  5
+  5
+  6
+  6
+  6
+  6
+  6
+  6
+  7
+  7
+  7
+  7
+  7
+  7
+  7
+  8
+  8
+  8
+  8
+  8
+  8
+  8
+  8
+  9
+  9
+  9
+  9
+  9
+  9
+  9
+  9
+  9
+ 10
+ 10
+ 10
+ 10
+ 10
+ 10
+ 10
+ 10
+ 10
+ 10
+(55 rows)
+
 --
 -- test WITH attached to intermediate-level set operation
 --
@@ -704,7 +1082,12 @@ WITH RECURSIVE outermost(x) AS (
          UNION SELECT * FROM innermost)
 )
 SELECT * FROM outermost;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ x 
+---
+ 1
+ 2
+(2 rows)
+
 WITH RECURSIVE outermost(x) AS (
   WITH innermost as (SELECT 2 FROM outermost) -- fail
     SELECT * FROM innermost
@@ -763,7 +1146,143 @@ WITH RECURSIVE
     )
   )
 SELECT * FROM iter;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ id_key | row_type | link 
+--------+----------+------
+      0 | base     |   17
+      1 | true     |   17
+      2 | true     |   17
+      3 | true     |   17
+      4 | true     |   17
+      5 | true     |   17
+      6 | true     |   17
+(7 rows)
+
+WITH RECURSIVE
+  tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
+  iter (id_key, row_type, link) AS (
+      SELECT 0, 'base', 17
+    UNION (
+      WITH remaining(id_key, row_type, link, min) AS (
+        SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
+        FROM tab INNER JOIN iter USING (link)
+        WHERE tab.id_key > iter.id_key
+      ),
+      first_remaining AS (
+        SELECT id_key, row_type, link
+        FROM remaining
+        WHERE id_key=min
+      ),
+      effect AS (
+        SELECT tab.id_key, 'new'::text, tab.link
+        FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
+        WHERE e.row_type = 'false'
+      )
+      SELECT * FROM first_remaining
+      UNION ALL SELECT * FROM effect
+    )
+  )
+SELECT * FROM iter;
+ id_key | row_type | link 
+--------+----------+------
+      0 | base     |   17
+      1 | true     |   17
+      2 | true     |   17
+      3 | true     |   17
+      4 | true     |   17
+      5 | true     |   17
+      6 | true     |   17
+(7 rows)
+
+--
+-- test WITH attached to intermediate-level set operation
+--
+WITH outermost(x) AS (
+  SELECT 1
+  UNION (WITH innermost as (SELECT 2)
+         SELECT * FROM innermost
+         UNION SELECT 3)
+)
+SELECT * FROM outermost;
+ x 
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+WITH outermost(x) AS (
+  SELECT 1
+  UNION (WITH innermost as (SELECT 2)
+         SELECT * FROM outermost  -- fail
+         UNION SELECT * FROM innermost)
+)
+SELECT * FROM outermost;
+ERROR:  relation "outermost" does not exist
+LINE 4:          SELECT * FROM outermost  
+                               ^
+DETAIL:  There is a WITH item named "outermost", but it cannot be referenced from this part of the query.
+HINT:  Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
+WITH RECURSIVE outermost(x) AS (
+  SELECT 1
+  UNION (WITH innermost as (SELECT 2)
+         SELECT * FROM outermost
+         UNION SELECT * FROM innermost)
+)
+SELECT * FROM outermost;
+ x 
+---
+ 1
+ 2
+(2 rows)
+
+WITH RECURSIVE outermost(x) AS (
+  WITH innermost as (SELECT 2 FROM outermost) -- fail
+    SELECT * FROM innermost
+    UNION SELECT * from outermost
+)
+SELECT * FROM outermost;
+ERROR:  recursive reference to query "outermost" must not appear within a subquery
+LINE 2:   WITH innermost as (SELECT 2 FROM outermost) 
+                                           ^
+--
+-- Test CTEs read in non-initialization orders
+--
+WITH RECURSIVE
+  tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
+  iter (id_key, row_type, link) AS (
+      SELECT 0, 'base', 17
+    UNION ALL (
+      WITH remaining(id_key, row_type, link, min) AS (
+        SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
+        FROM tab INNER JOIN iter USING (link)
+        WHERE tab.id_key > iter.id_key
+      ),
+      first_remaining AS (
+        SELECT id_key, row_type, link
+        FROM remaining
+        WHERE id_key=min
+      ),
+      effect AS (
+        SELECT tab.id_key, 'new'::text, tab.link
+        FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
+        WHERE e.row_type = 'false'
+      )
+      SELECT * FROM first_remaining
+      UNION ALL SELECT * FROM effect
+    )
+  )
+SELECT * FROM iter;
+ id_key | row_type | link 
+--------+----------+------
+      0 | base     |   17
+      1 | true     |   17
+      2 | true     |   17
+      3 | true     |   17
+      4 | true     |   17
+      5 | true     |   17
+      6 | true     |   17
+(7 rows)
+
 WITH RECURSIVE
   tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
   iter (id_key, row_type, link) AS (
@@ -955,7 +1474,7 @@ SELECT * FROM bug6051 ORDER BY 1;
  3
 (3 rows)
 
-CREATE TEMP TABLE bug6051_2 (i int);
+CREATE TEMP TABLE bug6051_2 (i int) DISTRIBUTE BY REPLICATION;
 CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
  INSERT INTO bug6051_2
  SELECT NEW.i;
@@ -985,7 +1504,7 @@ WITH RECURSIVE t(a) AS (
 		SELECT * FROM t RETURNING *
 )
 SELECT * FROM t2 JOIN y USING (a) ORDER BY a;
-ERROR:  WITH RECURSIVE currently not supported on distributed tables.
+ERROR:  INSERT/UPDATE/DELETE is not supported in subquery
 SELECT * FROM y order by 1;
  a  
 ----
@@ -1048,7 +1567,7 @@ SELECT * FROM y order by 1;
 -- check that run to completion happens in proper ordering
 TRUNCATE TABLE y;
 INSERT INTO y SELECT generate_series(1, 3);
-CREATE TEMPORARY TABLE yy (a INTEGER);
+CREATE TEMPORARY TABLE yy (a INTEGER) DISTRIBUTE BY REPLICATION;
 WITH RECURSIVE t1 AS (
   INSERT INTO y SELECT * FROM y RETURNING *
 ), t2 AS (
@@ -1199,24 +1718,22 @@ DROP TRIGGER y_trig ON y;
 ERROR:  trigger "y_trig" for table "y" does not exist
 DROP FUNCTION y_trigger();
 -- WITH attached to inherited UPDATE or DELETE
-CREATE TEMP TABLE parent ( id int, val text );
-CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
-CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
+CREATE TEMP TABLE parent ( id int, val text ) DISTRIBUTE BY REPLICATION;
+CREATE TEMP TABLE child1 ( ) INHERITS ( parent ) DISTRIBUTE BY REPLICATION;
+CREATE TEMP TABLE child2 ( ) INHERITS ( parent ) DISTRIBUTE BY REPLICATION;
 INSERT INTO parent VALUES ( 1, 'p1' );
 INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
 INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
 WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )
 UPDATE parent SET id = id + totalid FROM rcte;
-ERROR:  could not plan this distributed update
-DETAIL:  correlated UPDATE or updating distribution column currently not supported in Postgres-XL.
 SELECT * FROM parent ORDER BY id;
  id | val 
 ----+-----
-  1 | p1
- 11 | c11
- 12 | c12
- 23 | c21
- 24 | c22
+ 72 | p1
+ 82 | c11
+ 83 | c12
+ 94 | c21
+ 95 | c22
 (5 rows)
 
 WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )
@@ -1225,23 +1742,22 @@ ERROR:  INSERT/UPDATE/DELETE is not supported in subquery
 SELECT * FROM parent ORDER BY id;
  id | val 
 ----+-----
-  1 | p1
- 11 | c11
- 12 | c12
- 23 | c21
-(4 rows)
+ 72 | p1
+ 82 | c11
+ 83 | c12
+ 94 | c21
+ 95 | c22
+(5 rows)
 
 WITH rcte AS ( SELECT max(id) AS maxid FROM parent )
 DELETE FROM parent USING rcte WHERE id = maxid;
-ERROR:  Cannot generate remote query plan
-DETAIL:  This relation rowtype cannot be fetched
 SELECT * FROM parent ORDER BY id;
  id | val 
 ----+-----
-  1 | p1
- 11 | c11
- 12 | c12
- 23 | c21
+ 72 | p1
+ 82 | c11
+ 83 | c12
+ 94 | c21
 (4 rows)
 
 WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )
@@ -1250,10 +1766,10 @@ ERROR:  INSERT/UPDATE/DELETE is not supported in subquery
 SELECT * FROM parent ORDER BY id;
  id | val 
 ----+-----
-  1 | p1
- 11 | c11
- 12 | c12
- 23 | c21
+ 72 | p1
+ 82 | c11
+ 83 | c12
+ 94 | c21
 (4 rows)
 
 -- check EXPLAIN VERBOSE for a wCTE with RETURNING
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index dba417411a..07c382ed1c 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -77,7 +77,7 @@ CREATE TEMP TABLE department (
 	id INTEGER PRIMARY KEY,  -- department ID
 	parent_department INTEGER REFERENCES department, -- upper department ID
 	name TEXT -- department name
-);
+) DISTRIBUTE BY REPLICATION;
 
 INSERT INTO department VALUES (0, NULL, 'ROOT');
 INSERT INTO department VALUES (1, 0, 'A');
@@ -214,8 +214,8 @@ WITH RECURSIVE t(i,j) AS (
 --
 CREATE TEMPORARY TABLE tree(
     id INTEGER PRIMARY KEY,
-    parent_id INTEGER REFERENCES tree(id)
-);
+    parent_id INTEGER 
+) DISTRIBUTE BY REPLICATION;
 
 INSERT INTO tree
 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
@@ -263,7 +263,7 @@ SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
 --
 -- test cycle detection
 --
-create temp table graph( f int, t int, label text );
+create temp table graph( f int, t int, label text ) DISTRIBUTE BY REPLICATION;
 
 insert into graph values
 	(1, 2, 'arc 1 -> 2'),
@@ -342,7 +342,7 @@ WITH RECURSIVE
 -- Test WITH attached to a data-modifying statement
 --
 
-CREATE TEMPORARY TABLE y (a INTEGER);
+CREATE TEMPORARY TABLE y (a INTEGER) DISTRIBUTE BY REPLICATION;
 INSERT INTO y SELECT generate_series(1, 10);
 
 WITH t AS (
@@ -397,7 +397,7 @@ WITH RECURSIVE x(n) AS (SELECT n FROM x)
 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
 	SELECT * FROM x;
 
-CREATE TEMPORARY TABLE y (a INTEGER);
+CREATE TEMPORARY TABLE y (a INTEGER) DISTRIBUTE BY REPLICATION;
 INSERT INTO y SELECT generate_series(1, 10);
 
 -- LEFT JOIN
@@ -504,7 +504,7 @@ WITH RECURSIVE foo(i) AS
 SELECT * FROM foo;
 
 -- disallow OLD/NEW reference in CTE
-CREATE TEMPORARY TABLE x (n integer);
+CREATE TEMPORARY TABLE x (n integer) DISTRIBUTE BY REPLICATION  ;
 CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
     WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
 
@@ -621,7 +621,7 @@ INSERT INTO bug6051 SELECT * FROM t1;
 
 SELECT * FROM bug6051 ORDER BY 1;
 
-CREATE TEMP TABLE bug6051_2 (i int);
+CREATE TEMP TABLE bug6051_2 (i int) DISTRIBUTE BY REPLICATION;
 
 CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
  INSERT INTO bug6051_2
@@ -668,7 +668,7 @@ SELECT * FROM y order by 1;
 
 TRUNCATE TABLE y;
 INSERT INTO y SELECT generate_series(1, 3);
-CREATE TEMPORARY TABLE yy (a INTEGER);
+CREATE TEMPORARY TABLE yy (a INTEGER) DISTRIBUTE BY REPLICATION;
 
 WITH RECURSIVE t1 AS (
   INSERT INTO y SELECT * FROM y RETURNING *
@@ -763,9 +763,9 @@ DROP FUNCTION y_trigger();
 
 -- WITH attached to inherited UPDATE or DELETE
 
-CREATE TEMP TABLE parent ( id int, val text );
-CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
-CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
+CREATE TEMP TABLE parent ( id int, val text ) DISTRIBUTE BY REPLICATION;
+CREATE TEMP TABLE child1 ( ) INHERITS ( parent ) DISTRIBUTE BY REPLICATION;
+CREATE TEMP TABLE child2 ( ) INHERITS ( parent ) DISTRIBUTE BY REPLICATION;
 
 INSERT INTO parent VALUES ( 1, 'p1' );
 INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );