BUG #11811: Server segfault with many subpartitions when using nestloop

The following bug has been logged on the website:

Bug reference: 11811
Logged by: Federico Campoli
Email address: federico@brandwatch.com
PostgreSQL version: 9.2.9
Operating system: Debian GNU/Linux 7 amd64
Description:

We noticed a server crash with SEGFAULT after the upgrade to 9.2.9 with the
queries involving a large number of sub partitions.

Here the steps to reproduce the problem.

--create two tables with just two fields each one
DROP TABLE IF EXISTS t_root_01 CASCADE;
DROP TABLE IF EXISTS t_root_02 CASCADE;
CREATE TABLE t_root_01
(
i_id serial,
v_values character varying,
CONSTRAINT pk_t_root_01 PRIMARY KEY (i_id)

)
;

CREATE TABLE t_root_02
(
i_id serial,
v_values character varying,
CONSTRAINT pk_t_root_02 PRIMARY KEY (i_id)

)
;

--build 24 subpartitions for each root table
DO LANGUAGE plpgsql
$BODY$
DECLARE
v_t_sql text;

BEGIN
FOR i IN 1..24
LOOP
v_t_sql:=format('CREATE TABLE t_leaf_%s
(
CONSTRAINT pk_t_leaf_01_%s PRIMARY KEY (i_id)
)

INHERITS
(t_root_01);',i,i);
EXECUTE v_t_sql;

v_t_sql:=format('CREATE TABLE t_leaf_02_%s
(
CONSTRAINT pk_t_leaf_%s PRIMARY KEY (i_id)
)

INHERITS
(t_root_01);',i,i);
EXECUTE v_t_sql;

END LOOP;

END;
$BODY$
;

--the following query with the nested loop disabled runs fine
SET enable_nestloop ='off';
SELECT
*
FROM
t_root_01 t1
INNER JOIN
(
SELECT
*
FROM
t_root_01
UNION ALL

SELECT
*
FROM
t_root_02
) t2
ON t1.i_id=t2.i_id
;

--enabling the nested loop the server crashes
SET enable_nestloop ='on';
SELECT
*
FROM
t_root_01 t1
INNER JOIN
(
SELECT
*
FROM
t_root_01
UNION ALL

SELECT
*
FROM
t_root_02
) t2
ON t1.i_id=t2.i_id
;

This is the gdb stack trace of the backend crash.

Program received signal SIGSEGV, Segmentation fault.
ExecEvalScalarVar (exprstate=0x555555f07ca0, econtext=0x555555f07d00,
isNull=0x7fffffffda6f "", isDone=0x0) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/execQual.c:625
625
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/execQual.c:
No such file or directory.
(gdb) bt
#0 ExecEvalScalarVar (exprstate=0x555555f07ca0, econtext=0x555555f07d00,
isNull=0x7fffffffda6f "", isDone=0x0) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/execQual.c:625
#1 0x000055555573526f in ExecIndexEvalRuntimeKeys
(econtext=econtext@entry=0x555555f07d00, runtimeKeys=<optimized out>,
numRuntimeKeys=<optimized out>) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/nodeIndexscan.c:234
#2 0x0000555555735303 in ExecReScanIndexScan
(node=node@entry=0x555555eca060) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/nodeIndexscan.c:181
#3 0x000055555571ea7d in ExecReScan (node=0x555555eca060) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/execAmi.c:156
#4 0x00005555557351f5 in ExecIndexScan (node=node@entry=0x555555eca060) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/nodeIndexscan.c:148
#5 0x0000555555722c58 in ExecProcNode (node=0x555555eca060) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/execProcnode.c:403
#6 0x000055555572f2f1 in ExecAppend (node=node@entry=0x555555ec8850) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/nodeAppend.c:209
#7 0x0000555555722c98 in ExecProcNode (node=node@entry=0x555555ec8850) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/execProcnode.c:380
#8 0x000055555573b3fe in ExecNestLoop (node=node@entry=0x555555ec8430) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/nodeNestloop.c:123
#9 0x0000555555722bb8 in ExecProcNode (node=node@entry=0x555555ec8430) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/execProcnode.c:448
#10 0x00005555557202b6 in ExecutePlan (dest=0x5555560e7140,
direction=<optimized out>, numberTuples=0, sendTuples=1 '\001',
operation=CMD_SELECT, planstate=0x555555ec8430, estate=0x555555ec82f0)
at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/execMain.c:1396
#11 standard_ExecutorRun (queryDesc=0x555555d8bb70, direction=<optimized
out>, count=0) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/executor/execMain.c:304
#12 0x0000555555803cbf in PortalRunSelect
(portal=portal@entry=0x555555e44950, forward=forward@entry=1 '\001',
count=0, count@entry=9223372036854775807, dest=dest@entry=0x5555560e7140) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/tcop/pquery.c:946
#13 0x00005555558051c7 in PortalRun (portal=portal@entry=0x555555e44950,
count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=1 '\001',
dest=dest@entry=0x5555560e7140, altdest=altdest@entry=0x5555560e7140,
completionTag=completionTag@entry=0x7fffffffe090 "") at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/tcop/pquery.c:790
#14 0x0000555555800f63 in exec_simple_query (
query_string=0x555555e07f30 "SELECT \n\t*\nFROM \t\n\tt_root_01
t1\n\tINNER JOIN \n\t(\n\t\tSELECT \n\t\t\t* \n\t\tFROM
\n\t\t\tt_root_01\n\t\tUNION ALL\n\n\t\tSELECT \n\t\t\t* \n\t\tFROM
\n\t\t\tt_root_02\n\t) t2\n\tON t1.i_id=t2.i_id\n;\n")
at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/tcop/postgres.c:1046
#15 PostgresMain (argc=<optimized out>, argv=argv@entry=0x555555d43fe0,
dbname=0x7fffffffda6f "", username=<optimized out>) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/tcop/postgres.c:3968
#16 0x00005555557bc9da in BackendRun (port=0x555555d817c0) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/postmaster/postmaster.c:3617
#17 BackendStartup (port=0x555555d817c0) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/postmaster/postmaster.c:3299
#18 ServerLoop () at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/postmaster/postmaster.c:1362
#19 0x00005555557bd77c in PostmasterMain (argc=argc@entry=1,
argv=argv@entry=0x555555d43320) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/postmaster/postmaster.c:1122
#20 0x00005555555ec020 in main (argc=1, argv=0x555555d43320) at
/tmp/buildd/postgresql-9.2-9.2.9/build/../src/backend/main/main.c:229
(gdb)

Many thanks.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs