Re: [HACKERS] Runtime Partition Pruning

Hi,

I have applied attached patch on head "6f1d723b6359507ef55a81617167507bc25e3e2b" over Amit's v30 patches. while testing further I got a server crash with below test case. Please take a look.

CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250);
CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600);
CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500);
INSERT INTO prt1 SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i;
CREATE INDEX iprt1_p1_a on prt1_p1(a);
CREATE INDEX iprt1_p2_a on prt1_p2(a);
CREATE INDEX iprt1_p3_a on prt1_p3(a);
ANALYZE prt1;

CREATE TABLE prt2 (a int, b int, c varchar) PARTITION BY RANGE(b);
CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (250);
CREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (250) TO (500);
CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (600);
INSERT INTO prt2 SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 3) i;
CREATE INDEX iprt2_p1_b on prt2_p1(b);
CREATE INDEX iprt2_p2_b on prt2_p2(b);
CREATE INDEX iprt2_p3_b on prt2_p3(b);
ANALYZE prt2;

CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN ('0000', '0003', '0004', '0010');
CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0001', '0005', '0002', '0009');
CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN ('0006', '0007', '0008', '0011');
INSERT INTO plt1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
CREATE INDEX iplt1_p1_c on plt1_p1(c);
CREATE INDEX iplt1_p2_c on plt1_p2(c);
CREATE INDEX iplt1_p3_c on plt1_p3(c);
ANALYZE plt1;

CREATE TABLE plt2 (a int, b int, c text) PARTITION BY LIST(c);
CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0000', '0003', '0004', '0010');
CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0001', '0005', '0002', '0009');
CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0006', '0007', '0008', '0011');
INSERT INTO plt2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i;
CREATE INDEX iplt2_p1_c on plt2_p1(c);
CREATE INDEX iplt2_p2_c on plt2_p2(c);
CREATE INDEX iplt2_p3_c on plt2_p3(c);
ANALYZE plt2;

select count(*) from prt1 x where (x.a,x.b) in (select t1.a,t2.b from prt1 t1,prt2 t2 where t1.a=t2.b)
and (x.c) in (select t3.c from plt1 t3,plt2 t4 where t3.c=t4.c);

/*
postgres=# select count(*) from prt1 x where (x.a,x.b) in (select t1.a,t2.b from prt1 t1,prt2 t2 where t1.a=t2.b)
postgres-# and (x.c) in (select t3.c from plt1 t3,plt2 t4 where t3.c=t4.c);
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
*/

stack-trace give below :

/*
(gdb) bt
#0  0x00000000006ce6dc in ExecEvalParamExec (state=0x26e9ee0, op=0x26e9f78, econtext=0x26ea390) at execExprInterp.c:2222
#1  0x00000000006cc66a in ExecInterpExpr (state=0x26e9ee0, econtext=0x26ea390, isnull=0x7ffe0f75d77f "") at execExprInterp.c:1024
#2  0x00000000006cdd8c in ExecInterpExprStillValid (state=0x26e9ee0, econtext=0x26ea390, isNull=0x7ffe0f75d77f "") at execExprInterp.c:1819
#3  0x00000000007db078 in ExecEvalExprSwitchContext (state=0x26e9ee0, econtext=0x26ea390, isNull=0x7ffe0f75d77f "") at ../../../../src/include/executor/executor.h:305
#4  0x00000000007e2072 in evaluate_expr (expr=0x26a3cb0, result_type=25, result_typmod=-1, result_collation=0) at clauses.c:4890
#5  0x00000000007e588a in partkey_datum_from_expr (context=0x26d3180, parttypid=25, expr=0x26a3cb0, value=0x7ffe0f75da00) at partprune.c:1504
#6  0x00000000007e5243 in extract_bounding_datums (context=0x26d3180, minimalclauses=0x7ffe0f75d900, keys=0x7ffe0f75da00) at partprune.c:1307
#7  0x00000000007e377d in get_partitions_from_clauses (context=0x26d3180) at partprune.c:273
#8  0x00000000006ea2ec in set_valid_runtime_subplans_recurse (node=0x269bf90, pinfo=0x7f6cf6765cf0, ctxcache=0x26d3158, validsubplans=0x7ffe0f75de10) at nodeAppend.c:771
#9  0x00000000006e9ebf in set_valid_runtime_subplans (node=0x269bf90) at nodeAppend.c:640
#10 0x00000000006e99b5 in choose_next_subplan_locally (node=0x269bf90) at nodeAppend.c:426
#11 0x00000000006e9598 in ExecAppend (pstate=0x269bf90) at nodeAppend.c:224
#12 0x00000000006deb3a in ExecProcNodeFirst (node=0x269bf90) at execProcnode.c:446
#13 0x00000000006fb9ee in ExecProcNode (node=0x269bf90) at ../../../src/include/executor/executor.h:239
#14 0x00000000006fbcc4 in ExecHashJoinImpl (pstate=0x2697808, parallel=0 '\000') at nodeHashjoin.c:262
#15 0x00000000006fc3fd in ExecHashJoin (pstate=0x2697808) at nodeHashjoin.c:565
#16 0x00000000006deb3a in ExecProcNodeFirst (node=0x2697808) at execProcnode.c:446
#17 0x000000000070c376 in ExecProcNode (node=0x2697808) at ../../../src/include/executor/executor.h:239
#18 0x000000000070c70e in ExecNestLoop (pstate=0x262c0a0) at nodeNestloop.c:160
#19 0x00000000006deb3a in ExecProcNodeFirst (node=0x262c0a0) at execProcnode.c:446
#20 0x00000000006fb9ee in ExecProcNode (node=0x262c0a0) at ../../../src/include/executor/executor.h:239
#21 0x00000000006fbcc4 in ExecHashJoinImpl (pstate=0x262bec8, parallel=0 '\000') at nodeHashjoin.c:262
#22 0x00000000006fc3fd in ExecHashJoin (pstate=0x262bec8) at nodeHashjoin.c:565
#23 0x00000000006deb3a in ExecProcNodeFirst (node=0x262bec8) at execProcnode.c:446
#24 0x00000000006ea5bd in ExecProcNode (node=0x262bec8) at ../../../src/include/executor/executor.h:239
#25 0x00000000006eaab0 in fetch_input_tuple (aggstate=0x262ba18) at nodeAgg.c:406
#26 0x00000000006ecd40 in agg_retrieve_direct (aggstate=0x262ba18) at nodeAgg.c:1736
#27 0x00000000006ec932 in ExecAgg (pstate=0x262ba18) at nodeAgg.c:1551
#28 0x00000000006deb3a in ExecProcNodeFirst (node=0x262ba18) at execProcnode.c:446
#29 0x00000000006d59cd in ExecProcNode (node=0x262ba18) at ../../../src/include/executor/executor.h:239
#30 0x00000000006d8326 in ExecutePlan (estate=0x262b7c8, planstate=0x262ba18, use_parallel_mode=0 '\000', operation=CMD_SELECT, sendTuples=1 '\001', numberTuples=0,
    direction=ForwardScanDirection, dest=0x7f6cf676c7f0, execute_once=1 '\001') at execMain.c:1721
#31 0x00000000006d5f9f in standard_ExecutorRun (queryDesc=0x258aa98, direction=ForwardScanDirection, count=0, execute_once=1 '\001') at execMain.c:361
#32 0x00000000006d5dbb in ExecutorRun (queryDesc=0x258aa98, direction=ForwardScanDirection, count=0, execute_once=1 '\001') at execMain.c:304
#33 0x00000000008b588b in PortalRunSelect (portal=0x25caa58, forward=1 '\001', count=0, dest=0x7f6cf676c7f0) at pquery.c:932
#34 0x00000000008b5519 in PortalRun (portal=0x25caa58, count=9223372036854775807, isTopLevel=1 '\001', run_once=1 '\001', dest=0x7f6cf676c7f0, altdest=0x7f6cf676c7f0,
    completionTag=0x7ffe0f75e5e0 "") at pquery.c:773
#35 0x00000000008af540 in exec_simple_query (
    query_string=0x2565728 "select count(*) from prt1 x where (x.a,x.b) in (select t1.a,t2.b from prt1 t1,prt2 t2 where t1.a=t2.b) \nand (x.c) in (select t3.c from plt1 t3,plt2 t4 where t3.c=t4.c);") at postgres.c:1120
#36 0x00000000008b37d4 in PostgresMain (argc=1, argv=0x25910e0, dbname=0x2590f40 "postgres", username=0x2562228 "edb") at postgres.c:4144
#37 0x0000000000812afa in BackendRun (port=0x2588ea0) at postmaster.c:4412
#38 0x000000000081226e in BackendStartup (port=0x2588ea0) at postmaster.c:4084
*/

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation