Обсуждение: bug in explain - core dump

Hello

On 9.1 I found a query where explain do backend crash

backtrace

Core was generated by `postgres: pavel postgres [local] EXPLAIN       '.
Program terminated with signal 6, Aborted.
#0  0x0000003226c329a5 in raise () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install glibc-2.12.1-3.x86_64
(gdb) bt
#0  0x0000003226c329a5 in raise () from /lib64/libc.so.6
#1  0x0000003226c34185 in abort () from /lib64/libc.so.6
#2  0x00000000006eedad in ExceptionalCondition (conditionName=<value
optimized out>, errorType=<value optimized out>,   fileName=<value optimized out>, lineNumber=<value optimized out>)
at assert.c:57
#3  0x0000000000693870 in print_parameter_expr (expr=0xe72e40,
ancestor_cell=<value optimized out>, dpns=0xe75258,
context=0x7fffaf057790)   at ruleutils.c:4386
#4  0x00000000006929c6 in get_oper_expr (node=0xe742c8,
context=0x7fffaf057790, showimplicit=<value optimized out>) at
ruleutils.c:5655
#5  get_rule_expr (node=0xe742c8, context=0x7fffaf057790,
showimplicit=<value optimized out>) at ruleutils.c:4820
#6  0x00000000006914f6 in get_rule_expr (node=0xe74cb8,
context=0x7fffaf057790, showimplicit=<value optimized out>) at
ruleutils.c:4873
#7  0x0000000000697dcd in deparse_expression_pretty (expr=0xe74cb8,
dpcontext=0xe752d8, forceprefix=<value optimized out>,   showimplicit=0 '\000', prettyFlags=0, startIndent=0) at
ruleutils.c:2086
#8  0x000000000052a294 in show_expression (node=0xe74cb8,
qlabel=0x81e376 "Index Cond", planstate=<value optimized out>,   ancestors=<value optimized out>, useprefix=0 '\000',
es=0x7fffaf057e30) at explain.c:1330
#9  0x000000000052b62e in ExplainNode (planstate=0xe76a18,
ancestors=0xe74930, relationship=0x81e531 "Outer",   plan_name=<value optimized out>, es=0x7fffaf057e30) at
explain.c:1018
#10 0x000000000052b049 in ExplainNode (planstate=0xe766d8,
ancestors=0xe74930, relationship=0x81e528 "InitPlan",   plan_name=<value optimized out>, es=0x7fffaf057e30) at
explain.c:1203
#11 0x000000000052b01f in ExplainSubPlans (planstate=0xe77c80,
ancestors=0xe74930, relationship=0x81e546 "SubPlan",   plan_name=<value optimized out>, es=0x7fffaf057e30) at
explain.c:1687
#12 ExplainNode (planstate=0xe77c80, ancestors=0xe74930,
relationship=0x81e546 "SubPlan", plan_name=<value optimized out>,   es=0x7fffaf057e30) at explain.c:1199
#13 0x000000000052b168 in ExplainSubPlans (planstate=0xe7a018,
ancestors=0xe74930, relationship=0x81e537 "Inner",   plan_name=<value optimized out>, es=0x7fffaf057e30) at
explain.c:1687
#14 ExplainNode (planstate=0xe7a018, ancestors=0xe74930,
relationship=0x81e537 "Inner", plan_name=<value optimized out>,
es=0x7fffaf057e30)   at explain.c:1249
#15 0x000000000052b064 in ExplainNode (planstate=0xe78420,
ancestors=0xe74930, relationship=0x81e531 "Outer",   plan_name=<value optimized out>, es=0x7fffaf057e30) at
explain.c:1208
#16 0x000000000052b049 in ExplainNode (planstate=0xe78578,
ancestors=0xe74930, relationship=0x0, plan_name=<value optimized out>,   es=0x7fffaf057e30) at explain.c:1203
#17 0x000000000052c4c7 in ExplainOnePlan (plannedstmt=<value optimized
out>, es=0x7fffaf057e30, queryString=<value optimized out>,   params=0x0) at explain.c:411
#18 0x000000000052c9cb in ExplainOneQuery (stmt=0xd78fe8,
queryString=<value optimized out>, params=0x0, dest=0xe29288) at
explain.c:303
#19 ExplainQuery (stmt=0xd78fe8, queryString=<value optimized out>,
params=0x0, dest=0xe29288) at explain.c:209
#20 0x0000000000638007 in PortalRunUtility (portal=0xe7e538,
utilityStmt=0xd78fe8, isTopLevel=1 '\001', dest=0xe29288,   completionTag=0x7fffaf057ee0 "") at pquery.c:1191
#21 0x00000000006393cc in FillPortalStore (portal=0xe7e538,
isTopLevel=1 '\001') at pquery.c:1065
#22 0x0000000000639997 in PortalRun (portal=0xe7e538,
count=9223372036854775807, isTopLevel=1 '\001', dest=0xe81050,
altdest=0xe81050,   completionTag=0x7fffaf0580c0 "") at pquery.c:791
#23 0x0000000000635d69 in exec_simple_query (   query_string=0xd77108 "explain  select max(a), b from foo,
generate_series(0,30) g(v) where a = (select max(a) from foo where b =
v) group by b;") at postgres.c:1052
#24 0x0000000000636d20 in PostgresMain (argc=<value optimized out>,
argv=<value optimized out>, username=<value optimized out>)   at postgres.c:3869
#25 0x00000000005fb811 in BackendRun () at postmaster.c:3556

postgres=# \d foo     Table "public.foo"Column │  Type   │ Modifiers
────────┼─────────┼───────────a      │ integer │b      │ integer │
Indexes:   "foo_a_b_idx" btree (a, b)   "foo_a_idx" btree (a)   "foo_b_a_idx" btree (b, a)   "foo_b_idx" btree (b)
"foo_b_idx1"btree (b) 

statement: explain  select max(a), b from foo, generate_series(0,30)
g(v) where a = (select max(a) from foo where b = v) group by b;

regards

Pavel Stehule

Pavel Stehule <pavel.stehule@gmail.com> writes:
> On 9.1 I found a query where explain do backend crash

Can't reproduce here.  Would you provide a self-contained test case?
        regards, tom lane

postgres=# \i crash.sql
                                                 version
──────────────────────────────────────────────────────────────────────────────────────────────────────────
 PostgreSQL 9.1devel on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.4.4 20100630 (Red Hat 4.4.4…
…-10), 64-bit
(1 row)

Time: 0.979 ms
DROP TABLE
Time: 53.507 ms
CREATE TABLE
Time: 47.260 ms
INSERT 0 100000
Time: 6877.858 ms
ANALYZE
Time: 58.261 ms
CREATE INDEX
Time: 312.896 ms
CREATE INDEX
Time: 236.879 ms
psql:crash.sql:13: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
psql:crash.sql:13: connection to server was lost

Regards

Pavel Stehule

Pavel Stehule <pavel.stehule@gmail.com> writes:
> DROP TABLE IF EXISTS  foo;
> CREATE TABLE foo(a int, b int);
> INSERT INTO foo SELECT (random()*10000)::int, (random()*10)::int from generate_series(1,100000);
> ANALYZE foo;
> CREATE INDEX ON foo(a,b);
> CREATE INDEX ON foo(b,a);

> EXPLAIN SELECT max(a), b 
>            FROM foo, generate_series(0,30) g(v) 
>           WHERE a = (SELECT max(a) FROM foo WHERE b = v) 
>           GROUP BY b;

Fixed, thanks.
        regards, tom lane