Re: Postgresql crash (signal 11). keywords: distinct, subselect, union

"Magnus Naeslund(f)" <mag@fbab.net> writes:
> I just wanted to check if this has been fixed in any recent v8.1.x
> release, since I'm using v8.1.0 now.

Here's the fix if you need it.
        regards, tom lane

Index: allpaths.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v
retrieving revision 1.137.2.1
diff -c -r1.137.2.1 allpaths.c
*** allpaths.c    22 Nov 2005 18:23:10 -0000    1.137.2.1
--- allpaths.c    13 Feb 2006 16:07:30 -0000
***************
*** 793,803 ****  * it will work correctly: sublinks will already have been transformed into  * subplans in the qual,
butnot in the subquery).  *
 
!  * 2. The qual must not refer to any subquery output columns that were  * found to have inconsistent types across a
setoperation tree by  * subquery_is_pushdown_safe().  *
 
!  * 3. If the subquery uses DISTINCT ON, we must not push down any quals that  * refer to non-DISTINCT output columns,
becausethat could change the set  * of rows returned.  This condition is vacuous for DISTINCT, because then  * there
areno non-DISTINCT output columns, but unfortunately it's fairly
 
--- 793,806 ----  * it will work correctly: sublinks will already have been transformed into  * subplans in the qual,
butnot in the subquery).  *
 
!  * 2. The qual must not refer to the whole-row output of the subquery
!  * (since there is no easy way to name that within the subquery itself).
!  *
!  * 3. The qual must not refer to any subquery output columns that were  * found to have inconsistent types across a
setoperation tree by  * subquery_is_pushdown_safe().  *
 
!  * 4. If the subquery uses DISTINCT ON, we must not push down any quals that  * refer to non-DISTINCT output columns,
becausethat could change the set  * of rows returned.  This condition is vacuous for DISTINCT, because then  * there
areno non-DISTINCT output columns, but unfortunately it's fairly
 
***************
*** 805,811 ****  * parsetree representation.  It's cheaper to just make sure all the Vars  * in the qual refer to
DISTINCTcolumns.  *
 
!  * 4. We must not push down any quals that refer to subselect outputs that  * return sets, else we'd introduce
functions-returning-setsinto the  * subquery's WHERE/HAVING quals.  */
 
--- 808,814 ----  * parsetree representation.  It's cheaper to just make sure all the Vars  * in the qual refer to
DISTINCTcolumns.  *
 
!  * 5. We must not push down any quals that refer to subselect outputs that  * return sets, else we'd introduce
functions-returning-setsinto the  * subquery's WHERE/HAVING quals.  */
 
***************
*** 834,839 ****
--- 837,849 ----          Assert(var->varno == rti); 
+         /* Check point 2 */
+         if (var->varattno == 0)
+         {
+             safe = false;
+             break;
+         }
+          /*          * We use a bitmapset to avoid testing the same attno more than once.          * (NB: this only
worksbecause subquery outputs can't have negative
 
***************
*** 843,849 ****             continue;         tested = bms_add_member(tested, var->varattno); 
!         /* Check point 2 */         if (differentTypes[var->varattno])         {             safe = false;
--- 853,859 ----             continue;         tested = bms_add_member(tested, var->varattno); 
!         /* Check point 3 */         if (differentTypes[var->varattno])         {             safe = false;
***************
*** 855,861 ****         Assert(tle != NULL);         Assert(!tle->resjunk); 
!         /* If subquery uses DISTINCT or DISTINCT ON, check point 3 */         if (subquery->distinctClause != NIL &&
          !targetIsInSortList(tle, subquery->distinctClause))         {
 
--- 865,871 ----         Assert(tle != NULL);         Assert(!tle->resjunk); 
!         /* If subquery uses DISTINCT or DISTINCT ON, check point 4 */         if (subquery->distinctClause != NIL &&
          !targetIsInSortList(tle, subquery->distinctClause))         {
 
***************
*** 864,870 ****             break;         } 
!         /* Refuse functions returning sets (point 4) */         if (expression_returns_set((Node *) tle->expr))
 {             safe = false;
 
--- 874,880 ----             break;         } 
!         /* Refuse functions returning sets (point 5) */         if (expression_returns_set((Node *) tle->expr))
 {             safe = false;