"Unexpected internal error: Master process received signal SIGSEGV" when CTE is not consumed by SQL plan

Products

VMware Tanzu Data Suite Greenplum VMware Tanzu Greenplum

Issue/Introduction

Following cluster Panic can occur with SQL that utilises CTE:

2024-09-06 18:28:15.348041 UTC,,,p89550,th0,,,2024-09-06 18:28:06 UTC,0,con8522631,cmd3,seg-1,,,,,"PANIC","XX000","Unexpected internal error: Master process received signal SIGSEGV",,,,,,,0,,,,"
1    0x7f6017618630 libpthread.so.
0 <symbol not found> + 0x17618630
2    0xdc5f52 postgres _ZN11gpnaucrates21CFilterStatsProcessor25MakeHistHashMapConjFilterEPN4gpos11CMemoryPoolEPKN5gpopt17CStatisticsConfigEPNS1_8CHashMapIjNS_10CHistogramEXadL_ZNS1_9HashValueIjEEjPKT_EEXad
L_ZNS1_6EqualsIjEEbSD_SD_EEXadL_ZNS1_13CleanupDeleteIjEEvPSB_EEXadL_ZNSF_IS9_EEvSG_EEEENS1_7CDoubleEPNS_14CStatsPredConjEPSJ_ (CFilterStatsProcessor.cpp:362)
3    0xdc875c postgres _ZN11gpnaucrates21CFilterStatsProcessor15MakeStatsFilterEPN4gpos11CMemoryPoolEPKNS_11CStatisticsEPNS_10CStatsPredEb (CFilterStatsProcessor.cpp:284)
4    0xdef8aa postgres _ZN11gpnaucrates16CStatisticsUtils25DeriveStatsForDynamicScanEPN4gpos11CMemoryPoolERN5gpopt17CExpressionHandleEjPNS4_14CPartFilterMapE (CRefCount.h:103)
5    0xe96dd9 postgres _ZN5gpopt17CExpressionHandle22DeriveCostContextStatsEv (CExpressionHandle.cpp:532)
6    0xe2697a postgres _ZN5gpopt12CCostContext11DeriveStatsEv (CCostContext.cpp:205)
7    0xe27847 postgres _ZN5gpopt12CCostContext11CostComputeEPN4gpos11CMemoryPoolEPNS1_16CDynamicPtrArrayINS_5CCostEXadL_ZNS1_13CleanupDeleteIS5_EEvPT_EEEE (CCostContext.cpp:577)
8    0xf1f08b postgres _ZNK5gpopt16CGroupExpression11CostComputeEPN4gpos11CMemoryPoolEPNS_12CCostContextE (CRefCount.h:105)
9    0xf1fc7a postgres _ZN5gpopt16CGroupExpression14PccComputeCostEPN4gpos11CMemoryPoolEPNS_20COptimizationContextEjPNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEbNS_5CCostE (CDouble.h:103)
10   0xf28b6c postgres _ZN5gpopt31CJobGroupExpressionOptimization16EevtOptimizeSelfEPNS_17CSchedulerContextEPNS_4CJobE (CJobGroupExpressionOptimization.cpp:640)
11   0xf2a4d4 postgres _ZN5gpopt31CJobGroupExpressionOptimization8FExecuteEPNS_17CSchedulerContextE (CStateMachine.h:326)
12   0xf2f18d postgres _ZN5gpopt10CScheduler11ExecuteJobsEPNS_17CSchedulerContextE (CJob.h:185)
13   0xf2f1c0 postgres _ZN5gpopt10CScheduler3RunEPv (CScheduler.cpp:98)
14   0xe6c332 postgres _ZN5gpopt7CEngine8OptimizeEv (CRefCount.h:105)
15   0xf11d5a postgres _ZN5gpopt10COptimizer13PexprOptimizeEPN4gpos11CMemoryPoolEPNS_13CQueryContextEPNS1_16CDynamicPtrArrayINS_12CSearchStageEXadL_ZNS1_13CleanupDeleteIS7_EEvPT_EEEE (COptimizer.cpp:423)
16   0xf12528 postgres _ZN5gpopt10COptimizer13PdxlnOptimizeEPN4gpos11CMemoryPoolEPNS_11CMDAccessorEPKN5gpdxl8CDXLNodeEPKNS1_16CDynamicPtrArrayIS7_XadL_ZNS1_14CleanupReleaseIS7_EEvPT_EEEESG_PNS_19IConstExprE
valuatorEjjjPNSA_INS_12CSearchStageEXadL_ZNS1_13CleanupDeleteISJ_EEvSD_EEEEPNS_16COptimizerConfigEPKc (COptimizer.cpp:318)
17   0x102605a postgres _ZN9COptTasks12OptimizeTaskEPv (COptTasks.cpp:583)
18   0xd094c6 postgres _ZN4gpos5CTask7ExecuteEv (CTask.cpp:130)
19   0xd0a458 postgres _ZN4gpos7CWorker7ExecuteEPNS_5CTaskE (CWorker.cpp:82)
20   0xd0898d postgres _ZN4gpos14CAutoTaskProxy7ExecuteEPNS_5CTaskE (CAutoTaskProxy.cpp:301)
21   0xd0b051 postgres gpos_exec (_api.cpp:247)
22   0x10252b2 postgres _ZN9COptTasks18GPOPTOptimizedPlanEP5QueryP11SOptContext (COptTasks.cpp:261)
23   0x1027681 postgres GPOPTOptimizedPlan (CGPOptimizer.cpp:60)
24   0x9dd958 postgres optimize_query (orca.c:46
25   0x9cea46 postgres standard_planner (planner.c:243)
26   0x9ced95 postgres planner (planner.c:200)
27   0xa960aa postgres pg_plan_queries (postgres.c:975)
28   0xbd7442 postgres <symbol not found> (plancache.c:945)
29   0xbd800f postgres GetCachedPlan (plancache.c:1218)
30   0xa95c30 postgres <symbol not found> (postgres.c:2548)
31   0xa98777 postgres PostgresMain (postgres.c:5585)

Cause

Prior to GPDB 7.3.2 ORCA does not support a CTE being defined but not referenced.

ORCA asserts that the CTE has to be consumed in the plan.

If a CTE is defined, but not used within the query, then the above panic may occur.

Resolution

Workaround

Remove or comment out the unused CTE from the query, then ORCA will generate a plan and execute the query.

Fix

Fixed in Greenplum DB 7.3.2 and above

Fix will be released in future release of 6.x