Re: BUG #11457: The below query crashes 9.3.5, but not 9.3.4

Nelson Page wrote:
> Hi Michael,
>
> I've attached the create scripts for those tables. I'm relatively new
> to postgresql, so if that's not as helpful as I think it is, let me
> know what else I can provide.

FWIW I can reproduce the crash in 9.3 HEAD with these scripts, so that's
probably helpful enough. Table "Article" (not sent) is referenced in an
FK, but I just commented out that clause. Backtrace is:

#0 0x00007fd34d9ca1a5 in *__GI_raise (sig=<optimized out>) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#1 0x00007fd34d9cd420 in *__GI_abort () at abort.c:92
#2 0x0000000000768c27 in ExceptionalCondition (conditionName=conditionName(at)entry=0x8c76b0 "!(!bms_overlap(appendrel->relids, required_outer))",
errorType=errorType(at)entry=0x7a080c "FailedAssertion",
fileName=fileName(at)entry=0x8c7470 "../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/util/relnode.c",
lineNumber=lineNumber(at)entry=953) at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/utils/error/assert.c:54
#3 0x000000000063c3a5 in get_appendrel_parampathinfo (appendrel=appendrel(at)entry=0x7fd3444db9c0, required_outer=required_outer(at)entry=0x7fd3444e40e8)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/util/relnode.c:953
#4 0x0000000000636293 in create_append_path (rel=rel(at)entry=0x7fd3444db9c0, subpaths=subpaths(at)entry=0x7fd3444f6bd8,
required_outer=required_outer(at)entry=0x7fd3444e40e8)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/util/pathnode.c:898
#5 0x0000000000605df0 in set_append_rel_pathlist (rti=1146046832, rel=0x7fd3444db9c0, root=0x7fd344506c80, rte=<optimized out>)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/path/allpaths.c:852
#6 set_rel_pathlist (root=root(at)entry=0x7fd344506c80, rel=0x7fd3444db9c0, rti=1146046832, rti(at)entry=1, rte=<optimized out>)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/path/allpaths.c:306
#7 0x0000000000606407 in set_base_rel_pathlists (root=<optimized out>)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/path/allpaths.c:208
#8 make_one_rel (root=root(at)entry=0x7fd344506c80, joinlist=joinlist(at)entry=0x7fd3444df710)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/path/allpaths.c:138
#9 0x0000000000620d7d in query_planner (root=root(at)entry=0x7fd344506c80, tlist=tlist(at)entry=0x7fd3444db700, tuple_fraction=0,
tuple_fraction(at)entry=<error reading variable: Could not find type for DW_OP_GNU_const_type>, limit_tuples=-1,
qp_callback=qp_callback(at)entry=0x6218d0 <standard_qp_callback>, qp_extra=qp_extra(at)entry=0x7fff4c709470,
cheapest_path=cheapest_path(at)entry=0x7fff4c709460, sorted_path=sorted_path(at)entry=0x7fff4c709468, num_groups=num_groups(at)entry=0x7fff4c709450)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/plan/planmain.c:261
#10 0x0000000000622157 in grouping_planner (root=root(at)entry=0x7fd344506c80, tuple_fraction=0,
tuple_fraction(at)entry=<error reading variable: Could not find type for DW_OP_GNU_const_type>)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/plan/planner.c:1214
#11 0x0000000000624acb in subquery_planner (glob=0x27e0bc0, parse=parse(at)entry=0x27efdf0, parent_root=parent_root(at)entry=0x27f1ab0,
hasRecursion=hasRecursion(at)entry=0 '\000', tuple_fraction=0, subroot=subroot(at)entry=0x7fff4c709638)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/plan/planner.c:558
#12 0x00000000006050ef in set_subquery_pathlist (rte=<optimized out>, rti=1, rel=<optimized out>, root=<optimized out>)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/path/allpaths.c:1231
#13 set_rel_size (root=root(at)entry=0x27f1ab0, rel=<optimized out>, rti=rti(at)entry=1, rte=<optimized out>)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/path/allpaths.c:264
#14 0x00000000006063a6 in set_base_rel_sizes (root=<optimized out>)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/path/allpaths.c:179
#15 make_one_rel (root=root(at)entry=0x27f1ab0, joinlist=joinlist(at)entry=0x27f25b0)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/path/allpaths.c:137
#16 0x0000000000620d7d in query_planner (root=root(at)entry=0x27f1ab0, tlist=tlist(at)entry=0x27f2498, tuple_fraction=0,
tuple_fraction(at)entry=<error reading variable: Could not find type for DW_OP_GNU_const_type>, limit_tuples=-1,
qp_callback=qp_callback(at)entry=0x6218d0 <standard_qp_callback>, qp_extra=qp_extra(at)entry=0x7fff4c709850,
cheapest_path=cheapest_path(at)entry=0x7fff4c709840, sorted_path=sorted_path(at)entry=0x7fff4c709848, num_groups=num_groups(at)entry=0x7fff4c709830)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/plan/planmain.c:261
#17 0x0000000000622157 in grouping_planner (root=root(at)entry=0x27f1ab0, tuple_fraction=0,
tuple_fraction(at)entry=<error reading variable: Could not find type for DW_OP_GNU_const_type>)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/plan/planner.c:1214
#18 0x0000000000624acb in subquery_planner (glob=glob(at)entry=0x27e0bc0, parse=parse(at)entry=0x271c428, parent_root=parent_root(at)entry=0x0,
hasRecursion=hasRecursion(at)entry=0 '\000', tuple_fraction=0, subroot=subroot(at)entry=0x7fff4c7099e8)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/plan/planner.c:558
#19 0x0000000000624df7 in standard_planner (parse=0x271c428, cursorOptions=0, boundParams=0x0)
at ../../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/optimizer/plan/planner.c:209
#20 0x0000000000696549 in pg_plan_query (querytree=<optimized out>, cursorOptions=cursorOptions(at)entry=0, boundParams=boundParams(at)entry=0x0)
at ../../../../../../../../../pgsql/source/REL9_3_STABLE/src/backend/tcop/postgres.c:753

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services