Update or delete query causes system "PANIC" in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

Some UPDATE or DELETE queries can cause system "PANIC". The receive an error message similar to the one below:

"PANIC","XX000","Unexpected internal error: Master process received signal SIGSEGV",,,,,,,0,,,,"1    0xxxxxxxxxxx libpthread.so.0 <symbol not found> + 0xxxxxxxxxxx
2    0xxxxxxxxxxx libgpopt.so.3 _ZNK5gpopt8CLogical25PcrsDeriveCorrelatedApplyEPN4gpos11IMemoryPoolERNS_17CExpressionHandleE + 0xe6
3    0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt19CDrvdPropRelational6DeriveEPN4gpos11IMemoryPoolERNS_17CExpressionHandleEPNS_13CDrvdPropCtxtE + 0x8d
4    0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt17CExpressionHandle11DerivePropsEPNS_13CDrvdPropCtxtE + 0x2b8
5    0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt11CExpression9PdpDeriveEPNS_13CDrvdPropCtxtE + 0xae
6    0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt11CNormalizer29PexprPullUpAndCombineProjectsEPN4gpos11IMemoryPoolEPNS_11CExpressionEPb + 0x23b
7    0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt11CNormalizer22PexprPullUpProjectionsEPN4gpos11IMemoryPoolEPNS_11CExpressionE + 0x4f
8    0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt20CXformSubqueryUnnest19PexprSubqueryUnnestEPN4gpos11IMemoryPoolEPNS_11CExpressionEb + 0x145
9    0xxxxxxxxxxx libgpopt.so.3 _ZNK5gpopt20CXformSubqueryUnnest9TransformEPNS_13CXformContextEPNS_12CXformResultEPNS_11CExpressionEb + 0x1c
10   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt16CGroupExpression9TransformEPN4gpos11IMemoryPoolES3_PNS_6CXformEPNS_12CXformResultEPj + 0x251
11   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt18CJobTransformation13EevtTransformEPNS_17CSchedulerContextEPNS_4CJobE + 0x9e
12   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt18CJobTransformation8FExecuteEPNS_17CSchedulerContextE + 0x7d
13   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt10CScheduler8FExecuteEPNS_4CJobEPNS_17CSchedulerContextE + 0x8c
14   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt10CScheduler11ExecuteJobsEPNS_17CSchedulerContextE + 0x41
15   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt10CScheduler11ProcessJobsEPNS_17CSchedulerContextE + 0x62
16   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt10CScheduler3RunEPv + 0x13
17   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt7CEngine18MainThreadOptimizeEv + 0x4af
18   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt7CEngine8OptimizeEv + 0x12f
19   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt10COptimizer13PexprOptimizeEPN4gpos11IMemoryPoolEPNS_13CQueryContextEPNS1_16CDynamicPtrArrayINS_12CSearchStageEXadL_ZNS1_13CleanupDeleteIS7_EEvPT_EEEE + 0x4c
20   0xxxxxxxxxxx libgpopt.so.3 _ZN5gpopt10COptimizer13PdxlnOptimizeEPN4gpos11IMemoryPoolEPNS_11CMDAccessorEPKN5gpdxl8CDXLNodeEPKNS1_16CDynamicPtrArrayIS7_XadL_ZNS1_14CleanupReleaseIS7_EEvPT_EEEESG_PNS_19IConstExprEvaluatorEjjjPNSA_INS_12CSearchStageEXadL_ZNS1_13CleanupDeleteISJ_EEvSD_EEEEPNS_16COptimizerConfigEPKc + 0x466
21   0xxxxxxxxxxx postgres _ZN9COptTasks12OptimizeTaskEPv (COptTasks.cpp:1102)

Environment

Product Version: 5.19

Resolution

Below is a sample query that can trigger this issue:

update tablename
set columnname='1'
where column2 in (select distinct column3 from table2);

Workaround 

This issue is fixed in Greenplum Database (GPDB) 5.21.1. Another workaround is to set optimizer to off.