Re: [HACKERS] Runtime Partition Pruning

On Wed, Feb 21, 2018 at 2:36 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> I've attached v11 of the patch.
>

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