gpdb
gpdb copied to clipboard
Published
20 hours ago •
greenplum-db
greenplum-db
Coordinator crashes when handling ExecShareInputScan in sub plan
Bug Report
One of our production clusters was repeatedly experiencing coordinator seg faults running this query:
with open_rate_avg as
(select sum(tab.opened)*100/sum(tab.sended) as or_avg
from
(select date(date_trunc('week', date(customer_action_datetime_utc))) as week_n,
sum(case when mailing_action = 'MailingSend' then 1 else 0 end) as sended,
sum(case when mailing_action = 'MailingOpen' then 1 else 0 end) as opened,
sum(case when mailing_action = 'MailingClick' then 1 else 0 end) as clicked,
sum(case when mailing_action = 'MailingOpen' then 1.0 else 0.0 end)*100/sum(case when mailing_action = 'MailingSend' then 1.0 else 0.0 end) as open_rate,
sum(case when mailing_action = 'MailingClick' then 1.0 else 0.0 end)*100/sum(case when mailing_action = 'MailingOpen' then 1.0 else 0.0 end) as CTOR
from ods_mindbox.mailing_actions ma
where brand_system_name = 'xxxxxxxxxx' and channel_name = 'Email'
and mailing_name ilike '%Interrupted view%' and not mailing_name ilike '%price down%' and mailing_name ilike '%category%' and not mailing_name ilike '%not in stock%'
and date(customer_action_datetime_utc) > '2023-03-01'
group by week_n
having sum(case when mailing_action = 'MailingSend' then 1 else 0 end) > 1000 and sum(case when mailing_action = 'MailingOpen' then 1 else 0 end) > 0
order by week_n asc) as tab),
ct_or_avg as
(select sum(tab.clicked)*100/sum(tab.opened) as ctor_avg
from
(select date(date_trunc('week', date(customer_action_datetime_utc))) as week_n,
sum(case when mailing_action = 'MailingSend' then 1 else 0 end) as sended,
sum(case when mailing_action = 'MailingOpen' then 1 else 0 end) as opened,
sum(case when mailing_action = 'MailingClick' then 1 else 0 end) as clicked,
sum(case when mailing_action = 'MailingOpen' then 1.0 else 0.0 end)*100/sum(case when mailing_action = 'MailingSend' then 1.0 else 0.0 end) as open_rate,
sum(case when mailing_action = 'MailingClick' then 1.0 else 0.0 end)*100/sum(case when mailing_action = 'MailingOpen' then 1.0 else 0.0 end) as CTOR
from ods_mindbox.mailing_actions ma
where brand_system_name = 'xxxxxxxxxx' and channel_name = 'Email'
and mailing_name ilike '%Interrupted view%' and not mailing_name ilike '%price down%' and mailing_name ilike '%category%' and not mailing_name ilike '%not in stock%'
and date(customer_action_datetime_utc) > '2023-03-01'
group by week_n
having sum(case when mailing_action = 'MailingSend' then 1 else 0 end) > 1000 and sum(case when mailing_action = 'MailingOpen' then 1 else 0 end) > 0
order by week_n asc) as tab)
select power(avg(tb.abs_var_or), 0.5) as or_variance,
power(avg(tb.abs_var_ctor), 0.5) as ctor_variance,
stddev(tb.or_lag) as or_lag_variance,
stddev(tb.ctor_lag) as ctor_lag_variance,
max(tb.or_avg) as or_avg,
max(tb.ctor_avg) as ctor_avg
from
(select t.*,
power(((select or_avg from open_rate_avg) - t.open_rate), 2) as abs_var_or,
power(((select ctor_avg from ct_or_avg) - t.ctor), 2) as abs_var_ctor,
(select or_avg from open_rate_avg) as or_avg,
(select ctor_avg from ct_or_avg) as ctor_avg,
(t.open_rate - lag(t.open_rate) over (order by t.week_n))*100/lag(t.open_rate) over (order by t.week_n) as or_lag,
(t.ctor - lag(t.ctor) over (order by t.week_n))*100/lag(t.ctor) over (order by t.week_n) as ctor_lag
from
(select date(date_trunc('week', date(customer_action_datetime_utc))) as week_n, --mailing_name,
sum(case when ma.mailing_action = 'MailingSend' then 1 else 0 end) as sended,
sum(case when ma.mailing_action = 'MailingOpen' then 1 else 0 end) as opened,
sum(case when ma.mailing_action = 'MailingClick' then 1 else 0 end) as clicked,
sum(case when ma.mailing_action = 'MailingOpen' then 1.0 else 0.0 end)*100/sum(case when ma.mailing_action = 'MailingSend' then 1.0 else 0.0 end) as open_rate,
sum(case when ma.mailing_action = 'MailingClick' then 1.0 else 0.0 end)*100/sum(case when ma.mailing_action = 'MailingOpen' then 1.0 else 0.0 end) as CTOR
from ods_mindbox.mailing_actions as ma
where ma.brand_system_name = 'xxxxxxxxxx' and ma.channel_name = 'Email'
and mailing_name ilike '%Interrupted view%' and not mailing_name ilike '%price down%' and mailing_name ilike '%category%' and not mailing_name ilike '%not in stock%'
and date(ma.customer_action_datetime_utc) > '2023-03-01'
group by week_n
having sum(case when mailing_action = 'MailingSend' then 1 else 0 end) > 1000 and sum(case when mailing_action = 'MailingOpen' then 1 else 0 end) > 0
order by week_n asc) as t) as tb;
The query plan is:
Sequence (cost=0.00..11393954559223366.00 rows=1 width=48)
-> Shared Scan (share slice:id 0:0) (cost=0.00..9007.75 rows=1 width=1)
-> Materialize (cost=0.00..9007.75 rows=1 width=1)
-> Result (cost=0.00..9007.75 rows=1 width=8)
-> Aggregate (cost=0.00..9007.75 rows=1 width=16)
-> Gather Motion 144:1 (slice8; segments: 144) (cost=0.00..9007.75 rows=1 width=16)
-> Aggregate (cost=0.00..9007.75 rows=1 width=16)
-> Result (cost=0.00..9007.55 rows=28526 width=16)
Filter: (((sum(CASE WHEN ((mailing_actions_2.mailing_action)::text = 'MailingSend'::text) THEN 1 ELSE 0 END)) > 1000) AND ((sum(CASE WHEN ((mailing_actions_2.mailing_action)::text = 'MailingOpen'::text) THEN 1 ELSE 0 END)) > 0))
-> HashAggregate (cost=0.00..9000.95 rows=100285 width=32)
Group Key: (date(date_trunc('week'::text, (date(mailing_actions_2.customer_action_datetime_utc))::timestamp with time zone)))
-> Redistribute Motion 144:144 (slice7; segments: 144) (cost=0.00..8965.13 rows=275283 width=14)
Hash Key: (date(date_trunc('week'::text, (date(mailing_actions_2.customer_action_datetime_utc))::timestamp with time zone)))
-> Result (cost=0.00..8953.07 rows=275283 width=14)
-> Result (cost=0.00..8953.07 rows=275283 width=14)
-> Sequence (cost=0.00..8921.68 rows=275283 width=75)
-> Partition Selector for mailing_actions (dynamic scan id: 1) (cost=10.00..100.00 rows=1 width=4)
Partitions selected: 11 (out of 21)
-> Dynamic Seq Scan on mailing_actions mailing_actions_2 (dynamic scan id: 1) (cost=0.00..8921.68 rows=275283 width=75)
Filter: (((brand_system_name)::text = 'xxxxxxxxxx'::text) AND ((channel_name)::text = 'Email'::text) AND ((mailing_name)::text ~~* '%Interrupted view%'::text) AND ((mailing_name)::text !~~* '%price down%'::text) AND ((mailing_name)::text ~~* '%category%'::text) AND ((mailing_name)::text !~~* '%not in stock%'::text) AND (date(customer_action_datetime_utc) > '2023-03-01'::date))
-> Sequence (cost=0.00..11393954559214358.00 rows=1 width=48)
-> Shared Scan (share slice:id 0:1) (cost=0.00..9007.75 rows=1 width=1)
-> Materialize (cost=0.00..9007.75 rows=1 width=1)
-> Result (cost=0.00..9007.75 rows=1 width=8)
-> Aggregate (cost=0.00..9007.75 rows=1 width=16)
-> Gather Motion 144:1 (slice6; segments: 144) (cost=0.00..9007.75 rows=1 width=16)
-> Aggregate (cost=0.00..9007.75 rows=1 width=16)
-> Result (cost=0.00..9007.55 rows=28526 width=16)
Filter: (((sum(CASE WHEN ((mailing_actions_1.mailing_action)::text = 'MailingSend'::text) THEN 1 ELSE 0 END)) > 1000) AND ((sum(CASE WHEN ((mailing_actions_1.mailing_action)::text = 'MailingOpen'::text) THEN 1 ELSE 0 END)) > 0))
-> HashAggregate (cost=0.00..9000.95 rows=100285 width=32)
Group Key: (date(date_trunc('week'::text, (date(mailing_actions_1.customer_action_datetime_utc))::timestamp with time zone)))
-> Redistribute Motion 144:144 (slice5; segments: 144) (cost=0.00..8965.13 rows=275283 width=14)
Hash Key: (date(date_trunc('week'::text, (date(mailing_actions_1.customer_action_datetime_utc))::timestamp with time zone)))
-> Result (cost=0.00..8953.07 rows=275283 width=14)
-> Result (cost=0.00..8953.07 rows=275283 width=14)
-> Sequence (cost=0.00..8921.68 rows=275283 width=75)
-> Partition Selector for mailing_actions (dynamic scan id: 2) (cost=10.00..100.00 rows=1 width=4)
Partitions selected: 11 (out of 21)
-> Dynamic Seq Scan on mailing_actions mailing_actions_1 (dynamic scan id: 2) (cost=0.00..8921.68 rows=275283 width=75)
Filter: (((brand_system_name)::text = 'xxxxxxxxxx'::text) AND ((channel_name)::text = 'Email'::text) AND ((mailing_name)::text ~~* '%Interrupted view%'::text) AND ((mailing_name)::text !~~* '%price down%'::text) AND ((mailing_name)::text ~~* '%category%'::text) AND ((mailing_name)::text !~~* '%not in stock%'::text) AND (date(customer_action_datetime_utc) > '2023-03-01'::date))
-> Result (cost=0.00..11393954559205350.00 rows=1 width=48)
-> Aggregate (cost=0.00..11393954559205350.00 rows=1 width=48)
-> Gather Motion 144:1 (slice4; segments: 144) (cost=0.00..11393954559205350.00 rows=1 width=48)
-> Aggregate (cost=0.00..11393954559205350.00 rows=1 width=48)
-> Result (cost=0.00..11393954559205344.00 rows=28526 width=48)
-> Redistribute Motion 1:144 (slice3) (cost=0.00..11393954559205340.00 rows=1000 width=80)
-> Result (cost=0.00..11393954559205340.00 rows=7 width=80)
-> WindowAgg (cost=0.00..9361.54 rows=28526 width=48)
Order By: (date(date_trunc('week'::text, (date(mailing_actions.customer_action_datetime_utc))::timestamp with time zone)))
-> Gather Motion 144:1 (slice2; segments: 144) (cost=0.00..9279.39 rows=4107669 width=20)
Merge Key: (date(date_trunc('week'::text, (date(mailing_actions.customer_action_datetime_utc))::timestamp with time zone)))
-> Result (cost=0.00..9096.03 rows=28526 width=20)
-> Result (cost=0.00..9096.03 rows=28526 width=20)
-> Sort (cost=0.00..9095.46 rows=28526 width=36)
Sort Key: (date(date_trunc('week'::text, (date(mailing_actions.customer_action_datetime_utc))::timestamp with time zone)))
-> Result (cost=0.00..9009.29 rows=28526 width=36)
Filter: (((sum(CASE WHEN ((mailing_actions.mailing_action)::text = 'MailingSend'::text) THEN 1 ELSE 0 END)) > 1000) AND ((sum(CASE WHEN ((mailing_actions.mailing_action)::text = 'MailingOpen'::text) THEN 1 ELSE 0 END)) > 0))
-> HashAggregate (cost=0.00..9002.69 rows=100285 width=52)
Group Key: (date(date_trunc('week'::text, (date(mailing_actions.customer_action_datetime_utc))::timestamp with time zone)))
-> Redistribute Motion 144:144 (slice1; segments: 144) (cost=0.00..8965.13 rows=275283 width=14)
Hash Key: (date(date_trunc('week'::text, (date(mailing_actions.customer_action_datetime_utc))::timestamp with time zone)))
-> Result (cost=0.00..8953.07 rows=275283 width=14)
-> Result (cost=0.00..8953.07 rows=275283 width=14)
-> Sequence (cost=0.00..8921.68 rows=275283 width=75)
-> Partition Selector for mailing_actions (dynamic scan id: 3) (cost=10.00..100.00 rows=1 width=4)
Partitions selected: 11 (out of 21)
-> Dynamic Seq Scan on mailing_actions (dynamic scan id: 3) (cost=0.00..8921.68 rows=275283 width=75)
Filter: (((brand_system_name)::text = 'xxxxxxxxxx'::text) AND ((channel_name)::text = 'Email'::text) AND ((mailing_name)::text ~~* '%Interrupted view%'::text) AND ((mailing_name)::text !~~* '%price down%'::text) AND ((mailing_name)::text ~~* '%category%'::text) AND ((mailing_name)::text !~~* '%not in stock%'::text) AND (date(customer_action_datetime_utc) > '2023-03-01'::date))
SubPlan 1 (slice3)
-> Materialize (cost=0.00..431.00 rows=1 width=8)
-> Shared Scan (share slice:id 3:0) (cost=0.00..431.00 rows=1 width=8)
SubPlan 2 (slice3)
-> Materialize (cost=0.00..431.00 rows=1 width=8)
-> Shared Scan (share slice:id 3:1) (cost=0.00..431.00 rows=1 width=8)
SubPlan 3 (slice3)
-> Materialize (cost=0.00..431.00 rows=1 width=8)
-> Shared Scan (share slice:id 3:0) (cost=0.00..431.00 rows=1 width=8)
SubPlan 4 (slice3)
-> Materialize (cost=0.00..431.00 rows=1 width=8)
-> Shared Scan (share slice:id 3:1) (cost=0.00..431.00 rows=1 width=8)
Optimizer: Pivotal Optimizer (GPORCA)
(81 rows)
Here is the backtrace and some variables I believe might be of help:
#0 raise (sig=sig@entry=11) at ../sysdeps/unix/sysv/linux/raise.c:51
#1 0x0000563e76350a0f in StandardHandlerForSigillSigsegvSigbus_OnMainThread (processName=<optimized out>, postgres_signal_arg=11) at elog.c:5581
#2 <signal handler called>
#3 init_tuplestore_state (node=node@entry=0x563e77561d80) at nodeShareInputScan.c:102
#4 0x0000563e760eb1a0 in ExecShareInputScan (node=node@entry=0x563e77561d80) at nodeShareInputScan.c:172
#5 0x0000563e760b0cb8 in ExecProcNode (node=0x563e77561d80) at execProcnode.c:1125
#6 0x0000563e760cfb4a in ExecMaterial (node=node@entry=0x563e775617e8) at nodeMaterial.c:134
#7 0x0000563e760b0d58 in ExecProcNode (node=node@entry=0x563e775617e8) at execProcnode.c:1085
#8 0x0000563e760da49c in ExecScanSubPlan (isNull=0x563e77813836 "", econtext=<optimized out>, node=0x563e77806320) at nodeSubplan.c:302
#9 ExecSubPlan (node=0x563e77806320, econtext=<optimized out>, isNull=0x563e77813836 "", isDone=<optimized out>) at nodeSubplan.c:96
#10 0x0000563e760b9f05 in ExecTargetList (isDone=0x7ffc5b49fb40, itemIsDone=0x563e778139f0, isnull=0x563e77813830 "", values=0x563e778137a0, econtext=0x563e77805de0, targetlist=0x563e77812bd0)
at execQual.c:6395
#11 ExecProject (projInfo=<optimized out>, isDone=isDone@entry=0x7ffc5b49fb40) at execQual.c:6610
#12 0x0000563e760d7764 in ExecResult (node=node@entry=0x563e77805980) at nodeResult.c:202
#13 0x0000563e760b0bf0 in ExecProcNode (node=node@entry=0x563e77805980) at execProcnode.c:970
#14 0x0000563e760e8df8 in execMotionSender (node=0x563e77805340) at nodeMotion.c:303
#15 ExecMotion (node=node@entry=0x563e77805340) at nodeMotion.c:260
#16 0x0000563e760b0cc8 in ExecProcNode (node=node@entry=0x563e77805340) at execProcnode.c:1121
#17 0x0000563e760a8bb1 in ExecutePlan (estate=estate@entry=0x563e77561458, planstate=0x563e77805340, operation=operation@entry=CMD_SELECT, sendTuples=sendTuples@entry=0 '\000',
numberTuples=numberTuples@entry=0, direction=direction@entry=ForwardScanDirection, dest=0x7f94de2a2018) at execMain.c:3137
#18 0x0000563e760a9774 in ExecutePlan (dest=0x7f94de2a2018, direction=ForwardScanDirection, numberTuples=0, sendTuples=0 '\000', operation=CMD_SELECT, planstate=<optimized out>, estate=0x563e77561458)
at execMain.c:3103
#19 standard_ExecutorRun (queryDesc=0x563e774de968, direction=ForwardScanDirection, count=0) at execMain.c:1055
#20 0x0000563e762386b7 in PortalRunSelect (portal=portal@entry=0x563e7755f448, forward=forward@entry=1 '\001', count=0, count@entry=9223372036854775807, dest=dest@entry=0x7f94de2a2018) at pquery.c:1158
#21 0x0000563e7623a318 in PortalRun (portal=portal@entry=0x563e7755f448, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=1 '\001', dest=dest@entry=0x7f94de2a2018,
altdest=altdest@entry=0x7f94de2a2018, completionTag=completionTag@entry=0x7ffc5b4a0050 "") at pquery.c:999
#22 0x0000563e7623433b in exec_mpp_query (
query_string=query_string@entry=0x563e77539814 "with open_rate_avg as \r\n(select\t\t\tsum(tab.opened)*100/sum(tab.sended) as or_avg\r\n\t\t\t\t--(max(tab.open_rate) - min(tab.open_rate))/2 as or_variance,\r\n\t\t\t\t--(max(tab.CTOR) - min(tab.CTOR))/2 as ctor_vari"..., serializedQuerytree=serializedQuerytree@entry=0x0, serializedQuerytreelen=serializedQuerytreelen@entry=0,
serializedPlantree=serializedPlantree@entry=0x563e77539c14 "(\265/\375`\311~%f", serializedPlantreelen=serializedPlantreelen@entry=3278, serializedParams=serializedParams@entry=0x0,
serializedParamslen=0, serializedQueryDispatchDesc=0x563e7753a8e2 "(\265/\375`j\207\245\254", serializedQueryDispatchDesclen=5534) at postgres.c:1386
#23 0x0000563e76237b72 in PostgresMain (argc=<optimized out>, argv=argv@entry=0x563e7754ab78, dbname=<optimized out>, username=<optimized out>) at postgres.c:5547
#24 0x0000563e75ec7732 in BackendRun (port=0x563e77524b60) at postmaster.c:4843
#25 BackendStartup (port=0x563e77524b60) at postmaster.c:4507
#26 ServerLoop () at postmaster.c:1956
#27 0x0000563e761ccce7 in PostmasterMain (argc=6, argv=0x563e774b6240) at postmaster.c:1526
#28 0x0000563e75ec9147 in main (argc=6, argv=0x563e774b6240) at main.c:245
(gdb) frame 3
#3 init_tuplestore_state (node=node@entry=0x563e77561d80) at nodeShareInputScan.c:102
102 node->ts_state = ((MaterialState *)snState)->ts_state;
(gdb) list
97 ntuplestore_acc_seek_bof((NTupleStoreAccessor *)node->ts_pos);
98 }
99 else if(share_type == SHARE_MATERIAL)
100 {
101 /* The materialstate->ts_state structure should have been initialized already, during init of material node */
102 node->ts_state = ((MaterialState *)snState)->ts_state;
103 Assert(NULL != node->ts_state->matstore);
104 node->ts_pos = (void *) ntuplestore_create_accessor(node->ts_state->matstore, false);
105 ntuplestore_acc_seek_bof((NTupleStoreAccessor *)node->ts_pos);
106 }
(gdb) print snState
$1 = (PlanState *) 0x0
(gdb) print gp_cte_sharing
$5 = 0 '\000'
I can't share the core dump (nor would it be of much use as there are some patches applied) but I can provide any other info from it if needed and I'm also available for a short interactive Zoom debugging session if needed :) I have seen this manifest in 6.19 half a year ago but didn't have enough information to fill a bug report. My attempts to reproduce it locally with something similar (window functions + shared scans in subplans) have failed. Perhaps the team has better ideas on why share node state was left uninitialized.
Greenplum version or build
Seen it with 6.19 and 6.22
OS version and uname -a
Ubuntu 18.04, 5.4.134-19 x86_64
autoconf options used ( config.status --config )
Installation information ( pg_config )
Expected behavior
Do not crash
Actual behavior
Always crashes
Step to reproduce the behavior
Unfortunately, I wasn't able to reproduce it locally. Only seen it on a production cluster
