ports/90066: [PATCH] PostgreSQL-8.1 "unplanned sub-select" bug

[Roman Neuhauser]

>Number:         90066
>Category:       ports
>Synopsis:       [PATCH] PostgreSQL-8.1 "unplanned sub-select" bug
>Confidential:   no
>Severity:       non-critical
>Priority:       low
>Responsible:    freebsd-ports-bugs
>State:          open
>Quarter:        
>Keywords:       
>Date-Required:
>Class:          change-request
>Submitter-Id:   current-users
>Arrival-Date:   Wed Dec 07 12:50:08 GMT 2005
>Closed-Date:
>Last-Modified:
>Originator:     Roman Neuhauser
>Release:        FreeBSD 4.10-STABLE i386
>Organization:
>Environment:
System: FreeBSD isis.sigpipe.cz 4.10-STABLE FreeBSD 4.10-STABLE #3: Sat Aug 7 16:06:48 CEST 2004 roman at isis.wad.cz:/usr/obj/usr/src/sys/FREEPUPPY2_6 i386


	
>Description:
Hello,

PostgreSQL 8.1 has a new bug where an insert into a view with a subquery
in the table value constructor ("VALUES (...)") causes an abort with
"ERROR: cannot handle unplanned sub-select". [1]

The bug has been confirmed by Tom Lane[2], PostgreSQL head developer;
he also produced a patch, which is now committed in their cvs tree. [3]

[1] http://archives.postgresql.org/pgsql-sql/2005-11/msg00209.php
[2] http://archives.postgresql.org/pgsql-sql/2005-11/msg00226.php
[3] http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/rewrite/rewriteHandler.c.diff?r1=1.158.2.1;r2=1.158.2.2
    http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/rewrite/rewriteManip.c.diff?r1=1.92.2.1;r2=1.92.2.2

>How-To-Repeat:
	
>Fix:

	

Index: databases/postgresql81-server/files/patch-src-backend-rewriteHandler.c
===================================================================
RCS file: databases/postgresql81-server/files/patch-src-backend-rewriteHandler.c
diff -N databases/postgresql81-server/files/patch-src-backend-rewriteHandler.c
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ databases/postgresql81-server/files/patch-src-backend-rewriteHandler.c	7 Dec 2005 12:45:40 -0000
@@ -0,0 +1,26 @@
+--- src/backend/rewrite/rewriteHandler.c	2005/11/22 18:23:16	1.158.2.1
++++ src/backend/rewrite/rewriteHandler.c	2005/11/23 17:21:22	1.158.2.2
+@@ -7,7 +7,7 @@
+  * Portions Copyright (c) 1994, Regents of the University of California
+  *
+  * IDENTIFICATION
+- *	  $PostgreSQL: pgsql/src/backend/rewrite/rewriteHandler.c,v 1.158 2005/10/15 02:49:24 momjian Exp $
++ *	  $PostgreSQL: pgsql/src/backend/rewrite/rewriteHandler.c,v 1.158.2.1 2005/11/22 18:23:16 momjian Exp $
+  *
+  *-------------------------------------------------------------------------
+  */
+@@ -374,6 +374,14 @@ rewriteRuleAction(Query *parsetree,
+ 
+ 			sub_action->jointree->fromlist =
+ 				list_concat(newjointree, sub_action->jointree->fromlist);
++
++			/*
++			 * There could have been some SubLinks in newjointree, in which
++			 * case we'd better mark the sub_action correctly.
++			 */
++			if (parsetree->hasSubLinks && !sub_action->hasSubLinks)
++				sub_action->hasSubLinks =
++					checkExprHasSubLink((Node *) newjointree);
+ 		}
+ 	}
+ 
Index: databases/postgresql81-server/files/patch-src-backend-rewriteManip.c
===================================================================
RCS file: databases/postgresql81-server/files/patch-src-backend-rewriteManip.c
diff -N databases/postgresql81-server/files/patch-src-backend-rewriteManip.c
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ databases/postgresql81-server/files/patch-src-backend-rewriteManip.c	7 Dec 2005 12:45:40 -0000
@@ -0,0 +1,45 @@
+--- src/backend/rewrite/rewriteManip.c	2005/11/22 18:23:16	1.92.2.1
++++ src/backend/rewrite/rewriteManip.c	2005/11/23 17:21:22	1.92.2.2
+@@ -7,7 +7,7 @@
+  *
+  *
+  * IDENTIFICATION
+- *	  $PostgreSQL: pgsql/src/backend/rewrite/rewriteManip.c,v 1.92 2005/10/15 02:49:24 momjian Exp $
++ *	  $PostgreSQL: pgsql/src/backend/rewrite/rewriteManip.c,v 1.92.2.1 2005/11/22 18:23:16 momjian Exp $
+  *
+  *-------------------------------------------------------------------------
+  */
+@@ -930,6 +930,7 @@ ResolveNew(Node *node, int target_varno,
+ 		   RangeTblEntry *target_rte,
+ 		   List *targetlist, int event, int update_varno)
+ {
++	Node	   *result;
+ 	ResolveNew_context context;
+ 
+ 	context.target_varno = target_varno;
+@@ -944,8 +945,21 @@ ResolveNew(Node *node, int target_varno,
+ 	 * Must be prepared to start with a Query or a bare expression tree; if
+ 	 * it's a Query, we don't want to increment sublevels_up.
+ 	 */
+-	return query_or_expression_tree_mutator(node,
+-											ResolveNew_mutator,
+-											(void *) &context,
+-											0);
++	result = query_or_expression_tree_mutator(node,
++											  ResolveNew_mutator,
++											  (void *) &context,
++											  0);
++
++	if (context.inserted_sublink)
++	{
++		if (IsA(result, Query))
++			((Query *) result)->hasSubLinks = true;
++		/*
++		 * Note: if we're called on a non-Query node then it's the caller's
++		 * responsibility to update hasSubLinks in the ancestor Query.
++		 * This is pretty fragile and perhaps should be rethought ...
++		 */
++	}
++
++	return result;
+ }

>Release-Note:
>Audit-Trail:
>Unformatted: