ERROR: unexpected gang size: 1152 (nodeMotion.c:1028)

Products

• VMware Tanzu Data Suite
• Greenplum
• VMware Tanzu Greenplum

Issue/Introduction

"CREATE TABLE AS" (CTAS) can report the following error:

 ERROR:  unexpected gang size: 1152 (nodeMotion.c:1028)

The error can be reported when the CTAS is creating a randomly distributed table and from many "UNION ALL" operations that combines tables of different distribution types, that is distributed randomly, distributed replicated or distributed by (key). 

For example:

CREATE TABLE new_table AS (
   SELECT 0 as int1, 0 as int2
   UNION ALL SELECT a, b FROM replicated_table -- replicated_table is "DISTRIBUTED REPLICATED"
   UNION ALL SELECT j, k FROM heap_table      -- heap_table is "DISTRIBUTED BY (j)"
) DISRIBUTED RANDOMLY;

The error may also occur in Greenplum 7.x, but is less likely due to the cost model will cause a different, valid plan to be chosen.

Environment

Greenplum 6.x

Cause

There is a code bug in the Pivotal Optimizer (GPORCA) that causes it to create and choose an incorrect plan.

Resolution

Workaround 1

This applies to create tables using CTAS and the SELECT query has more than one UNION ALL operations and a variety of distribution types (replicated/random/derived - complex queries with joins)

Ensure the first UNION ALL operation is NOT a replicated table.

Problematic query will be of this pattern 

CREATE TABLE new_table AS (
   SELECT 0 as int1, 0 as int2
   UNION ALL SELECT a, b FROM replicated_table -- replicated_table is "DISTRIBUTED REPLICATED"
   UNION ALL SELECT j, k FROM heap_table -- heap_table is "DISTRIBUTED BY (j)"
) DISRIBUTED RANDOMLY;

Change the order of the UNION ALL:

CREATE TABLE new_table AS (
   SELECT 0 as int1, 0 as int2
   UNION ALL SELECT j, k FROM heap_table -- heap_table is "DISTRIBUTED BY (j)" (First UNION ALL is no longer a replicated table)
   UNION ALL SELECT a, b FROM replicated_table -- replicated_table is "DISTRIBUTED REPLICATED"
) DISRIBUTED RANDOMLY;

Workaround 2

Create the new table with a specific distribution key:

CREATE TABLE new_table AS (
   SELECT 0 as int1, 0 as int2
   UNION ALL SELECT a, b FROM replicated_table -- replicated_table is "DISTRIBUTED REPLICATED"
   UNION ALL SELECT j, k FROM heap_table -- heap_table is "DISTRIBUTED BY (j)" 
) DISRIBUTED BY (int1);  -- distribution key specified

Fix

As of March 2025 fix is in development and will be released in future maintenance releases of 6.x and 7.x

Check the Release Notes of the versions for issue 36156736

Additional Information

Unit test to reproduce the issue at will

 -- DDL:
CREATE TABLE rep (id integer NOT NULL) DISTRIBUTED REPLICATED;
CREATE TABLE dist (id integer NOT NULL) DISTRIBUTED BY (id);

CREATE OR REPLACE VIEW v as SELECT 0 AS flag  UNION ALL (SELECT 0 AS flag FROM rep)  UNION ALL (SELECT 0 as flag FROM dist);

-- Problematic Query
EXPLAIN CREATE TEMP TABLE foo as select * from v distributed randomly;
CREATE TEMP TABLE foo as select * from v distributed randomly;