CREATE TABLE statements hang

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:
The CREATE TABLE statement takes a very long time to complete and seems to hang with the follwoing issues:

• CPU usage on the master is very high during the CREATE TABLE.
• SELECT on tables function without issues.
• CREATE TABLE statement functions without issues when connected to the master or when the segment is in the utility mode.

Environment

Cause

It is caused by OID overflow in the Pivotal Greenplum Database (GPDB). The OID is an Object Identifier. Every relation in the database gets assigned a unique OID when it is created.

There is an OID on master and on each segment which is represented by an unsigned 32 bit variable.

When creating a new object, the OID is obtained in three steps:
1. Get the max OID of all the segments as max_oid.
2. Increase the OID on master to max_oid.
3. Increase the OID on master further by 10.

After creating many objects, the OID is near the maximum value of the unsigned 32 bit variable (0xffffffff)(4294967295 decimal).
Because of step 3, the OID on master tends to overflow before the segments to a small value (FirstNormalObjectId 16384) while the OIDs on segments are very large (approaching the maximum value of 4294967295).
When creating a new object, step 2 consumes a lot of time to increase master OID and that's why the CPU usage on master is high and it looks like the master is hanging.

Resolution

Check the NextOID on the master and some of the segments with the following commands.
On the master:

pg_controldata $MASTER_DATA_DIRECTORY | egrep NextOID

On the segments:

pg_controldata </path/to/the/segment/directory/> | egrep NextOID

Note: Replace </path/to/the/segment/directory/> with the correct path to segment.

Verify that the following is true:

• The NextOID on the master is much lower than on the segments.
• The NextOID on the segments is getting close to 4294967295 (probably within 100 of the value).

If the above statements are true, then cause the OID on the segments to overflow by connecting to the segments directly in utility mode and creating and dropping test tables.

The suggested SQL statements to run on each segment are mentioned below. The "SELECT oid.." will report the current OID used on the segment and will show when the OID overflows.

CREATE TABLE test_overflow_table (a INTEGER);
SELECT oid FROM pg_class WHERE relname = 'test_overflow_table';
DROP TABLE test_overflow_table;