Segfault on EstimateSnapshotSpace

[yazun]

Looks like there is an another hanging bug related to the parallel path and aggregation. It happens occasionally, most-likely due to races and is also data dependent it seems so hard to create an repeatable example:

TBase/src/backend/utils/time/snapmgr.c

Line 2314 in 949f970

size = add_size(sizeof(SerializedSnapshotData),

TBase/src/backend/utils/time/snapmgr.c

Line 2315 in 949f970

mul_size(snap->xcnt, sizeof(TransactionId)));

where snap ptr became null regardless of the asserts at the beginning of the fn.

Using host libthread_db library "/lib64/libthread_db.so.1".
Core was generated by `postgres: distefan_local surveys 192.168.169.251(44486) SELECT              '.
Program terminated with signal 11, Segmentation fault.
#0  EstimateSnapshotSpace (snap=0x0) at snapmgr.c:2314
2314    snapmgr.c: No such file or directory.
Missing separate debuginfos, use: debuginfo-install cyrus-sasl-lib-2.1.26-23.el7.x86_64 glibc-2.17-307.el7.1.x86_64 keyutils-libs-1.5.8-3.el7.x86_64 krb5-libs-1.15.1-46.el7.x86_64 libcom_err-1.42.9-17.el7.x86_64 libselinux-2.5-15.el7.x86_64 libxml2-2.9.1-6.el7.4.x86_64 nspr-4.21.0-1.el7.x86_64 nss-3.44.0-7.el7_7.x86_64 nss-softokn-freebl-3.44.0-8.el7_7.x86_64 nss-util-3.44.0-4.el7_7.x86_64 openldap-2.4.44-21.el7_6.x86_64 openssl-libs-1.0.2k-19.el7.x86_64 pcre-8.32-17.el7.x86_64 xz-libs-5.2.2-1.el7.x86_64 zlib-1.2.7-18.el7.x86_64
(gdb) bt
#0  EstimateSnapshotSpace (snap=0x0) at snapmgr.c:2314
#1  0x0000000000b9c0c2 in InitializeParallelDSM () at parallel.c:241
#2  0x00000000009f7508 in ExecInitParallelPlan () at execParallel.c:756
#3  0x00000000009d351c in ExecGather (pstate=0x1ebb6e0) at nodeGather.c:183
#4  0x00000000009dc1fc in ExecProcNode (node=0x1ebb6e0) at ../../../src/include/executor/executor.h:275
#5  fetch_input_tuple (aggstate=aggstate@entry=0x1ebb038) at nodeAgg.c:739
#6  0x00000000009e30c8 in agg_fill_hash_table (aggstate=0x1ebb038) at nodeAgg.c:3487
#7  ExecAgg (pstate=0x1ebb038) at nodeAgg.c:3029
#8  0x00000000009fdcc6 in ExecProcNode (node=0x1ebb038) at ../../../src/include/executor/executor.h:275
#9  ExecutePlan (execute_once=<optimized out>, dest=0x7f52f580a090, direction=<optimized out>, numberTuples=0, sendTuples=<optimized out>, operation=CMD_SELECT, use_parallel_mode=<optimized out>, planstate=0x1ebb038, estate=<optimized out>) at execMain.c:2061
#10 standard_ExecutorRun (queryDesc=<optimized out>, direction=<optimized out>, count=0, execute_once=<optimized out>) at execMain.c:471
#11 0x000000000076218c in ExecutorRun (execute_once=<optimized out>, count=0, direction=ForwardScanDirection, queryDesc=0x1eba768) at execMain.c:414
#12 PortalRunSelect () at pquery.c:1715
#13 0x0000000000762be1 in PortalRun (portal=0x1ca62e8, count=9223372036854775807, isTopLevel=<optimized out>, run_once=<optimized out>, dest=0x7f52f580a090, altdest=0x7f52f580a090, completionTag=0x7ffceedd6020 "") at pquery.c:1356
#14 0x000000000076bf03 in exec_simple_query.lto_priv.0 () at postgres.c:1511
#15 0x0000000000765cc5 in PostgresMain (argc=<optimized out>, argv=<optimized out>, dbname=<optimized out>, username=<optimized out>) at postgres.c:5456
#16 0x0000000000829f54 in BackendRun (port=0x1bc1200) at postmaster.c:4982
#17 BackendStartup (port=0x1bc1200) at postmaster.c:4654
#18 ServerLoop () at postmaster.c:1959
#19 0x000000000082b999 in PostmasterMain () at postmaster.c:1567
#20 0x00000000004f4a1d in main (argc=5, argv=0x1b96690) at main.c:233

[JennyJennyChen]

what is GUCs max_parallel_workers_per_gather and max_parallel_workers?
what is the query of coredump?

[yazun]

show  max_parallel_workers_per_gather;
 max_parallel_workers_per_gather
---------------------------------
 20
(1 row)

Time: 4.156 ms
(dr3_ops_cs36@gaiadb01i:55431) [surveys] > show   max_parallel_workers
surveys-# ;
 max_parallel_workers
----------------------
 40

The query and the plan:

(gdb) printf "%s\n", (queryDesc.sourceText)

v_source_catalog_derived is a simple view with a join.

explain select distinct sourceid, delta, alpha
surveys-> from v_source_catalog_derived
surveys-> where catalogid=getmaincatalog() and
surveys-> sourceid in (5872353585199986048,5873902419176095616,5870400680789563136,5866941995130925568,5874864010845922432,5867123341511662464,5884533764540798080,5895610523893945856,5870443527373207424,5867918155320330240,5870181770543066752,5873893932319722752,5891430760462613120,5867093246698092672,5873126576256118656,5866927289136302592,5867291193155343360,5866819914978209664,5873292499423810048,5866722092794488704,5877852036732333952,5866641209981241984,5866882999462836096,5870045401056725632,5868980420988751488,4513767846485784960,4517218629395253120,4978753439875030528,4979006675444569216,4978945927427097088,4979018873150950656,4972145443711649024,5009491940993329920,4967177339079058432,5034330905097581696,5076375439469564288,5009914767637419648,5009987855097592320,5085474031332115840,5010701300704483840,5074608043247195392,5062563752359737088,5018465394688794240,5081350111109805184,5084008730226397952,5090749217540566912,5077427530361832448,4968045918904857472,4995436265379609984,4960088272217329792,5063473598232807296,5083262917745169920,5017941928372008192,5275173086008382080,4962634569348690176,5058802941555966976,5062899618804003968,5083503401554073088,4984494372337848832,5080937966048288000,5081107423981207808,5117433540112045312,5008531723744686080,4981065270449180288,5061520350187347328,4976885992392515456,4898348873251823872,5009769224082142848,5084865627742296832,5084936718040911616,5084034225152246528,4977866791124916992,4968162604575665280,4967958782607575424,4896744651427573376,5021633057625851136,5021637971068376320,4975995834650436736,5617905909960088192,5809290889621959296,5711256302584730752,5710634833694923136,5661090839742766720,5631058061887216256,5631040572781785728,5699988400037527040,5646470500494968064,5814105273080164992,5654678629663965824,5808513947218394112,5635794929775242112,5808794563199856256,5725559540107575168,5704141053593240576,5641564342169281024,5821282781915610240,5697726846360504320,5694306643649906304,5629199028244463872,5627484580377527040);

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=4389.08..4389.93 rows=17 width=24)
   Group Key: sourceid, delta, alpha
   ->  Gather  (cost=1200.24..4381.43 rows=1700 width=24)
         Workers Planned: 1
         ->  Parallel Nested Loop  (cost=200.24..3211.43 rows=1000 width=24)
               ->  Parallel Remote Subquery Scan on all (datanode1,datanode10,datanode11,datanode12,datanode2,datanode3,datanode4,datanode5,datanode6,datanode7,datanode8,datanode9)  (cost=100.12..674.80 rows=25 width=26)
                     ->  Parallel Append  (cost=0.12..574.08 rows=25 width=26)
                           ->  Parallel Index Scan using source_10_pkey on source_10 s  (cost=0.12..114.57 rows=5 width=26)
                                 Index Cond: (sourceid = ANY ('{5872353585199986048,5873902419176095616,5870400680789563136,5866941995130925568,5874864010845922432,5867123341511662464,5884533764540798080,5895610523893945856,5870443527373207424,5867918155320330240,5870181770543066752,5873893932319722752,5891430760462613120,5867093246698092672,5873126576256118656,5866927289136302592,5867291193155343360,5866819914978209664,5873292499423810048,5866722092794488704,58778520367323
                           ->  Parallel Index Scan using source_11_pkey on source_11 s_1  (cost=0.12..114.56 rows=5 width=26)
                                 Index Cond: (sourceid = ANY ('{5872353585199986048,5873902419176095616,5870400680789563136,5866941995130925568,5874864010845922432,5867123341511662464,5884533764540798080,5895610523893945856,5870443527373207424,5867918155320330240,5870181770543066752,5873893932319722752,5891430760462613120,5867093246698092672,5873126576256118656,5866927289136302592,5867291193155343360,5866819914978209664,5873292499423810048,5866722092794488704,58778520367323
                           ->  Parallel Index Scan using source_13_pkey on source_13 s_2  (cost=0.12..114.85 rows=5 width=26)
                                 Index Cond: (sourceid = ANY ('{5872353585199986048,5873902419176095616,5870400680789563136,5866941995130925568,5874864010845922432,5867123341511662464,5884533764540798080,5895610523893945856,5870443527373207424,5867918155320330240,5870181770543066752,5873893932319722752,5891430760462613120,5867093246698092672,5873126576256118656,5866927289136302592,5867291193155343360,5866819914978209664,5873292499423810048,5866722092794488704,58778520367323
                           ->  Parallel Index Scan using source_14_pkey on source_14 s_3  (cost=0.12..115.17 rows=5 width=26)
                                 Index Cond: (sourceid = ANY ('{5872353585199986048,5873902419176095616,5870400680789563136,5866941995130925568,5874864010845922432,5867123341511662464,5884533764540798080,5895610523893945856,5870443527373207424,5867918155320330240,5870181770543066752,5873893932319722752,5891430760462613120,5867093246698092672,5873126576256118656,5866927289136302592,5867291193155343360,5866819914978209664,5873292499423810048,5866722092794488704,58778520367323
                           ->  Parallel Index Scan using source_16_pkey on source_16 s_4  (cost=0.12..114.93 rows=5 width=26)
                                 Index Cond: (sourceid = ANY ('{5872353585199986048,5873902419176095616,5870400680789563136,5866941995130925568,5874864010845922432,5867123341511662464,5884533764540798080,5895610523893945856,5870443527373207424,5867918155320330240,5870181770543066752,5873893932319722752,5891430760462613120,5867093246698092672,5873126576256118656,5866927289136302592,5867291193155343360,5866819914978209664,5873292499423810048,5866722092794488704,58778520367323
               ->  Materialize  (cost=100.12..101.42 rows=1 width=10)
                     ->  Parallel Remote Subquery Scan on all (datanode1,datanode10,datanode11,datanode12,datanode2,datanode3,datanode4,datanode5,datanode6,datanode7,datanode8,datanode9)  (cost=100.12..101.41 rows=1 width=10)
                           ->  Index Only Scan using fk_catalog_source_fdatalight_catalogid_fkey_idx on catalog_source cs  (cost=0.12..1.40 rows=1 width=10)
                                 Index Cond: ((fdatalight_catalogid = '2'::smallint) AND (fdatalight_sourceid = sourceid))
(21 rows)

[yazun]

Unfortunately we still get the same core-dumps (even more often) for some other plans after merging with the latest 2.2.0 branch.

[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Core was generated by `postgres: dr3_ops_cs36 surveys 192.168.169.251(55016) SELECT                '.
Program terminated with signal 11, Segmentation fault.
#0  EstimateSnapshotSpace (snap=0x0) at utils/time/snapmgr.c:2314
2314    utils/time/snapmgr.c: No such file or directory.
Missing separate debuginfos, use: debuginfo-install cyrus-sasl-lib-2.1.26-23.el7.x86_64 glibc-2.17-323.el7_9.x86_64 keyutils-libs-1.5.8-3.el7.x86_64 krb5-libs-1.15.1-50.el7.x86_64 libcom_err-1.42.9-19.el7.x86_64 libselinux-2.5-15.el7.x86_64 libxml2-2.9.1-6.el7.5.x86_64 nspr-4.25.0-2.el7_9.x86_64 nss-3.53.1-3.el7_9.x86_64 nss-softokn-freebl-3.53.1-6.el7_9.x86_64 nss-util-3.53.1-1.el7_9.x86_64 openldap-2.4.44-22.el7.x86_64 openssl-libs-1.0.2k-21.el7_9.x86_64 pcre-8.32-17.el7.x86_64 xz-libs-5.2.2-1.el7.x86_64 zlib-1.2.7-19.el7_9.x86_64
(gdb) bt
#0  EstimateSnapshotSpace (snap=0x0) at utils/time/snapmgr.c:2314
#1  0x000000000058b803 in InitializeParallelDSM () at access/transam/parallel.c:241
#2  0x0000000000720e88 in ExecInitParallelPlan () at executor/execParallel.c:756
#3  0x0000000000742559 in ExecGather (pstate=0x6bc6918) at executor/nodeGather.c:183
#4  0x000000000075ec1f in ExecProcNode (node=0x6bc6918) at executor/../../../src/include/executor/executor.h:275
#5  ExecSort (pstate=0x6bc65b8) at executor/nodeSort.c:154
#6  0x000000000073225c in ExecProcNode (node=0x6bc65b8) at executor/../../../src/include/executor/executor.h:275
#7  fetch_input_tuple (aggstate=0x2dea608) at executor/nodeAgg.c:739
#8  0x000000000073d6a5 in agg_retrieve_direct (aggstate=<optimized out>) at executor/nodeAgg.c:3225
#9  ExecAgg (pstate=<optimized out>) at executor/nodeAgg.c:3036
#10 0x000000000075ec1f in ExecProcNode (node=0x2dea608) at executor/../../../src/include/executor/executor.h:275
#11 ExecSort (pstate=0x2dea198) at executor/nodeSort.c:154
#12 0x0000000000769646 in ExecProcNode (node=0x2dea198) at executor/../../../src/include/executor/executor.h:275
#13 begin_partition (winstate=winstate@entry=0x2de96d8) at executor/nodeWindowAgg.c:1083
#14 0x000000000076b4b3 in ExecWindowAgg () at executor/nodeWindowAgg.c:1672
#15 0x000000000075ec1f in ExecProcNode (node=0x2de96d8) at executor/../../../src/include/executor/executor.h:275
#16 ExecSort (pstate=0x2de9238) at executor/nodeSort.c:154
#17 0x0000000000718538 in ExecProcNode (node=0x2de9238) at executor/../../../src/include/executor/executor.h:275
#18 ExecutePlan (execute_once=<optimized out>, dest=0x2c7dd18, direction=<optimized out>, numberTuples=0, sendTuples=<optimized out>, operation=CMD_SELECT, use_parallel_mode=<optimized out>, planstate=<optimized out>, estate=<optimized out>) at executor/execMain.c:2061
#19 standard_ExecutorRun (queryDesc=0x590a0f8, direction=<optimized out>, count=0, execute_once=<optimized out>) at executor/execMain.c:471
#20 0x000000000099d2cc in ExecutorRun (execute_once=<optimized out>, count=0, direction=ForwardScanDirection, queryDesc=0x590a0f8) at executor/execMain.c:414
#21 PortalRunSelect () at tcop/pquery.c:1715
#22 0x00000000009a1531 in PortalRun (portal=portal@entry=0x2d76778, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=1 '\001', run_once=<optimized out>, dest=dest@entry=0x2c7dd18, altdest=altdest@entry=0x2c7dd18, completionTag=0x7ffc5ebadc90 "") at tcop/pquery.c:1356
#23 0x00000000009a3e3b in exec_execute_message (max_rows=9223372036854775807, portal_name=0x2c7d908 "") at tcop/postgres.c:2995
#24 PostgresMain (argc=<optimized out>, argv=<optimized out>, dbname=<optimized out>, username=<optimized out>) at tcop/postgres.c:5571
#25 0x00000000008e3368 in BackendRun (port=0x2bbad90) at postmaster/postmaster.c:4982
#26 BackendStartup (port=0x2bbad90) at postmaster/postmaster.c:4654
#27 ServerLoop () at postmaster/postmaster.c:1959
#28 0x00000000008e425c in PostmasterMain () at postmaster/postmaster.c:1567
#29 0x00000000004f77ad in main (argc=5, argv=0x2b8e550) at main/main.c:233

It looks like CTE related usually

#20 0x000000000099d2cc in ExecutorRun (execute_once=<optimized out>, count=0, direction=ForwardScanDirection, queryDesc=0x590a0f8) at executor/execMain.c:414
414     executor/execMain.c: No such file or directory.
(gdb) printf "%s\n", (queryDesc.sourceText)
with a as (
select varitype,count(*) cnt
    from    dr3_ops_cs36_mv.final_dr3_export_helper y
    join lateral unnest(y.classification_types) varitype on true
    group by 1 order by lower(varitype)
)
select varitype,pretty(cnt) sources,pretty(sum(cnt)over()) total  from a order by lower(varitype);

Moreover it's is harder to repeat, as usually happens when run in a chain of queries, the same query works ok if run independently:

explain with a as (
surveys(# select varitype,count(*) cnt
surveys(#     from    dr3_ops_cs36_mv.final_dr3_export_helper y
surveys(#     join lateral unnest(y.classification_types) varitype on true
surveys(#     group by 1 order by lower(varitype)
surveys(# )
surveys-# select varitype,pretty(cnt) sources,pretty(sum(cnt)over()) total  from a order by lower(varitype);
                                                                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2794138.71..2794138.86 rows=100 width=128)
   Sort Key: (lower(varitype.varitype))
   ->  WindowAgg  (cost=2794096.11..2794136.71 rows=100 width=128)
         ->  Sort  (cost=2794096.11..2794096.26 rows=100 width=72)
               Sort Key: (lower(varitype.varitype))
               ->  Parallel Finalize GroupAggregate  (cost=2794088.52..2794094.12 rows=100 width=72)
                     Group Key: varitype.varitype
                     ->  Parallel Sort  (cost=2794088.52..2794088.67 rows=100 width=40)
                           Sort Key: varitype.varitype
                           ->  Gather  (cost=2794071.53..2794086.53 rows=100 width=40)
                                 Workers Planned: 6
                                 ->  Partial HashAggregate  (cost=2793071.53..2793076.53 rows=100 width=40)
                                       Group Key: varitype.varitype
                                       ->  Parallel Nested Loop  (cost=100.00..2065053.88 rows=242672550 width=32)
                                             ->  Parallel Remote Subquery Scan on all (datanode1,datanode10,datanode11,datanode12,datanode2,datanode3,datanode4,datanode5,datanode6,datanode7,datanode8,datanode9)  (cost=100.00..42783.88 rows=202227 width=31)
                                                   ->  Gather  (cost=0.00..35808.16 rows=808908 width=31)
                                                         Workers Planned: 4
                                                         ->  Parallel Seq Scan on final_dr3_export_helper y  (cost=0.00..35808.16 rows=202227 width=31)
                                             ->  Function Scan on unnest varitype  (cost=0.00..5.00 rows=100 width=32)
(19 rows)

Time: 5.543 ms
(dr3_ops_cs36@gaiadb12i:55431) [surveys] > explain analyze  with a as (
select varitype,count(*) cnt
    from    dr3_ops_cs36_mv.final_dr3_export_helper y
    join lateral unnest(y.classification_types) varitype on true
    group by 1 order by lower(varitype)
)
select varitype,pretty(cnt) sources,pretty(sum(cnt)over()) total  from a order by lower(varitype);
                                                                                                                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2794138.71..2794138.86 rows=100 width=128) (actual time=4313.267..4313.268 rows=25 loops=1)
   Sort Key: (lower(varitype.varitype))
   Sort Method: quicksort  Memory: 27kB
   ->  WindowAgg  (cost=2794096.11..2794136.71 rows=100 width=128) (actual time=4313.137..4313.247 rows=25 loops=1)
         ->  Sort  (cost=2794096.11..2794096.26 rows=100 width=72) (actual time=4312.870..4312.870 rows=25 loops=1)
               Sort Key: (lower(varitype.varitype))
               Sort Method: quicksort  Memory: 27kB
               ->  Parallel Finalize GroupAggregate  (cost=2794088.52..2794094.12 rows=100 width=72) (actual time=4312.807..4312.835 rows=25 loops=1)
                     Group Key: varitype.varitype
                     ->  Parallel Sort  (cost=2794088.52..2794088.67 rows=100 width=40) (actual time=4312.793..4312.799 rows=150 loops=1)
                           Sort Key: varitype.varitype
                           Sort Method: quicksort  Memory: 32kB
                           ->  Gather  (cost=2794071.53..2794086.53 rows=100 width=40) (actual time=3752.560..4312.650 rows=150 loops=1)
                                 Workers Planned: 6
                                 Workers Launched: 6
                                 ->  Partial HashAggregate  (cost=2793071.53..2793076.53 rows=100 width=40) (actual time=3988.244..3988.248 rows=25 loops=6)
                                       Group Key: varitype.varitype
                                       ->  Parallel Nested Loop  (cost=100.00..2065053.88 rows=242672550 width=32) (actual time=133.755..3572.985 rows=2104960 loops=6)
                                             ->  Parallel Remote Subquery Scan on all (datanode1,datanode10,datanode11,datanode12,datanode2,datanode3,datanode4,datanode5,datanode6,datanode7,datanode8,datanode9)  (cost=100.00..42783.88 rows=202227 width=31) (actual time=133.678..763.941 rows=2426726 loops=6)
                                                   ->  Gather  (cost=0.00..35808.16 rows=808908 width=31)
                                                         DN (actual startup time=5.772..14.504 total time=340.922..633.491 rows=1211117..1217258 loops=1..1)
                                                         Workers Planned: 4
                                                         Workers Launched: 0
                                                         ->  Seq Scan on final_dr3_export_helper y  (cost=0.00..35808.16 rows=202227 width=31)
                                                               DN (actual startup time=0.021..0.086 total time=68.064..89.717 rows=302779..304314 loops=4..4)
                                             ->  Function Scan on unnest varitype  (cost=0.00..5.00 rows=100 width=32) (actual time=0.001..0.001 rows=1 loops=14560353)
 Planning time: 0.414 ms
 Execution time: 4314.671 ms
(28 rows)

Time: 4318.558 ms (00:04.319)

[beth-database]

@yazun Can you give me the definitions of dr3_ops_cs36_mv.final_dr3_export_helper and pretty function? You can use '\d+ dr3_ops_cs36_mv.final_dr3_export_helper ' to get the definition of table, and '\df+ pretty' to get the definition of the function.
In addition, you can check the core file to confirm whether 'ActiveSnapshot' is empty to help check the promble.
And the core file is caused by coordinator or datanode?
Look forward to your reply.

[beth-database]

@yazun
Maybe the global var g_set_global_snapshot is false and ActiveSnapshot is NULL when the core dump happend, please check that in the core file, and please go to level 19 by using 'f 19' to see queryDesc->snapshot, maybe it is NULL too.
In addition, please check the guc parameter value of set_global_snapshot.

[yazun]

Hello @beth-database
final_dr3_export_helper is relatively straightforward:

\d+ dr3_ops_cs36_mv.final_dr3_export_helper
                              Table "dr3_ops_cs36_mv.final_dr3_export_helper"
         Column          |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
-------------------------+---------+-----------+----------+---------+----------+--------------+-------------
 sourceid                | bigint  |           | not null |         | plain    |              |
 inclassification        | boolean |           |          |         | plain    |              |
 insosrrlyrae            | boolean |           |          |         | plain    |              |
 insoscepheid            | boolean |           |          |         | plain    |              |
 insosplanetarytransit   | boolean |           |          |         | plain    |              |
 insosshorttimescale     | boolean |           |          |         | plain    |              |
 insoslongperiodvariable | boolean |           |          |         | plain    |              |
 insoseclipsingbinary    | boolean |           |          |         | plain    |              |
 insosrotationmodulation | boolean |           |          |         | plain    |              |
 insosflaringstar        | boolean |           |          |         | plain    |              |
 insosmsoscillators      | boolean |           |          |         | plain    |              |
 insosagn                | boolean |           |          |         | plain    |              |
 insosmicrolensing       | boolean |           |          |         | plain    |              |
 insoscompactcompanion   | boolean |           |          |         | plain    |              |
 insospmsoscillator      | boolean |           |          |         | plain    |              |
 insosbestar             | boolean |           |          |         | plain    |              |
 inpencilbeam            | boolean |           |          |         | plain    |              |
 inrvs                   | boolean |           |          |         | plain    |              |
 isgalaxy                | boolean |           |          |         | plain    |              |
 sostypes                | text[]  |           |          |         | extended |              |
 classification_types    | text[]  |           |          |         | extended |              |
 retraction_type         | text[]  |           |          |         | extended |              |
 retraction_id           | integer |           |          |         | plain    |              |
Indexes:
    "final_dr3_export_helper_non_retracted" UNIQUE, btree (sourceid) WHERE retraction_type IS NULL, tablespace "output_tablespace"
    "uq_final_dr3_export_helper" UNIQUE, btree (sourceid), tablespace "final_run_validation_tablespace"
    "idx_gin_helper" gin (classification_types), tablespace "final_run_validation_tablespace"
    "idx_gin_helper_sos" gin (sostypes) WHERE sostypes IS NOT NULL, tablespace "final_run_validation_tablespace"
    "idx_gin_helper_sos_non_retracted" btree (sourceid) WHERE sostypes IS NOT NULL AND retraction_type IS NULL, tablespace "final_run_validation_tablespace"
Foreign-key constraints:
    "fkey_retraction" FOREIGN KEY (retraction_id) REFERENCES dr3_ops_cs36_mv.final_dr3_export_retraction(id) ON DELETE CASCADE
Tablespace: "output_tablespace"
Distribute By: SHARD(sourceid)
Location Nodes: ALL DATANODES

pretty is just a display prettifier of large numbers:

CREATE OR REPLACE FUNCTION dr3_ops_cs36.pretty(num numeric)
 RETURNS text
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE
AS $function$
    select  trim(to_char($1,'999,999,999,999'));
$function$

For the debug:
Global snapshot is indeed off (performance-related, carried over from XL) and snapshot ptr is indeed a null:

set_global_snapshot                    | off                                                         | always use global snapshot for query

(gdb) p g_set_global_snapshot
$4 = 0 '\000'

(gdb) f 19
#19 standard_ExecutorRun (queryDesc=0x2db1a38, direction=<optimized out>, count=0, execute_once=<optimized out>) at executor/execMain.c:471
p queryDesc->snapshot
$1 = (Snapshot) 0x0

p *queryDesc
$3 = {operation = CMD_SELECT, plannedstmt = 0xc257d78, sourceText = 0x2db1828 "with a as (\nselect varitype,count(*) cnt \n    from    dr3_ops_cs36_mv.final_dr3_export_helper y \n    join lateral unnest(y.classification_types) varitype on true \n    group by 1 order by lower(varityp"..., snapshot = 0x0, crosscheck_snapshot = 0x0, dest = 0x2b83b88, params = 0x0, queryEnv = 0x0, instrument_options = 0, tupDesc = 0x4db10d8, estate = 0xc4de978, planstate = 0xc4debb8, squeue = 0x0, sender = 0x0, es_param_exec_vals = 0x0, epqContext = 0x0,
  myindex = -1, already_executed = 1 '\001', totaltime = 0x0}
  

I think core is caused by coord here.

(gdb) p isPGXCCoordinator
$5 = 1 '\001'
(gdb) p isPGXCDataNode
$6 = 0 '\000'

Hope this helps!

[yazun]

not sure if related but I have feeling that CTEs are failing/coredumping more often now.
Before, for few cases CTEs were used as a barrier not to push-down as a workaround for failing/coredumping queries.
Since the new code uses more CTE logic from PG12 (where by default CTEs are pushed-down unless MATERIALIZED is not used, according to my understanding) we see more segfaults..But it's more of a gut feeling than something measured.

[beth-database]

@yazun Thanks for the replies.
I think the reason for the problem is set_global_snapshot parameter. You can set set_global_snapshot to false, and then execute the parallel select query, then the core will be reproduced.
In distributed system, we always set set_global_snapshot to be true, and when distributed transaction exists, it must be set to true or there will be a problem with transaction consistency.
However, If your project only run read without write, the you can use set_global_snapshot=false as before, and we will fix the bug in the new version.

[yazun]

Thanks a lot for clarification!
indeed, this is a case where we want to materialize results in a table via insert .. into tbl.. as the data is big and queries are usually time-consuming. Would be wonderful to have it fixed permanently as we do not have control always over usage like this.