Troubleshooting Queries Showing rsgname='unknown' in pg_stat_activity

Products

• VMware Tanzu Data
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

Introduction

Active sessions in Greenplum Command Center (GPCC) or pg_stat_activity may occasionally fail to display the assigned resource group, showing rsgname as unknown. This behavior occurs even when the session is executing queries and consuming system resources.

Symptoms

• GPCC Query Monitor shows "unknown" or blank for the workload/resource group.
• The query SELECT rsgname FROM pg_stat_activity returns unknown for active sessions.
• Database logs show execute <handle> patterns for these specific sessions.

Environment

• Database: Greenplum Database
• Feature: Resource Group
• Affected Versions: 7.4.1 ~ 7.5.4, 6.30.0
• Resolved Versions: 6.30.1, 7.6.0 and higher

Cause

The reasons for rsgname showing as unknown fall into two main categories: a known version defect (Bug) and expected system behaviors (By Design).

A. Root Cause: Server-side Prepared Statement Bug (Defect)

A defect exists where the database engine only assigns a resource group during the "Prepare" phase of a server-side prepared statement. Subsequent "Execute" commands reuse the cached plan but skip the resource group assignment step, causing the session to run without resource group limitations (bypassing concurrency and memory limits).

Impact: 

These queries run completely outside of their designated resource group (e.g., etl_group) and do not consume a concurrency slot. The consequences differ significantly depending on the Greenplum version:

• In Greenplum 6.x: Queries not assigned to a resource group are allocated a hardcoded limit of exactly 1MB of memory. This is often not enough to even build the initial execution plan for complex queries, leading directly to Out of Memory (OOM) failures.
• In Greenplum 7.x: The 1MB limitation does not exist. The unassigned query will fall back to using the statement_mem parameter as its limit. If the query requires more memory than statement_mem, it will spill to disk instead of causing an OOM.

B. Expected Behaviors (By Design)

Apart from the bug mentioned above, the following 8 scenarios will also legitimately show as unknown by architectural design:

• Idle Queries: Sessions that have ended their transactions and are in an idle state have had their concurrency slots reclaimed, and rsgname resets to unknown.
• QE Processes: When resource groups are enabled. Only query dispatcher (QD) processes will have a rsgid and rsgname. Other server processes such as a query executer (QE) process or session connection processes will have a rsgid value of 0 and a rsgname value of unknown. QE processes are managed by the same resource group as the dispatching QD process.
• Early Unassign: If gp_resgroup_enable_early_unassign is set to on, queries can turn into rsgname='unknown' at some stage to release the slot.
• Role-Level Bypass: The role executing the query is configured with the specific setting gp_resource_group_bypass = on.
• Session-Level Bypass: The user set gp_resource_group_bypass to on before actually executing the query.
• Cost-based Bypass: A query whose total cost evaluates to below the MIN_COST threshold of the resource group. The system bypasses it to save queuing time.
• Catalog Query Bypass: A catalog query when gp_resource_group_bypass_catalog_query is set to on.
• Direct Dispatch Bypass: A query bypassing the resource group due to a direct dispatch plan when gp_resource_group_bypass_direct_dispatch is set to on.

Resolution

Permanent Fix :

Upgrade the Greenplum cluster to version 7.6.0 or 6.30.1 and higher. See https://knowledge.broadcom.com/external/article/142814/download-broadcom-products-and-software.html to download this release.

Temporary Workarounds:

• Disable server-side prepared statements in the client driver.
• Wrap the work in an explicit transaction (autocommit=off)

Additional Information

How to confirm if the prepared statement bug is triggered?

You can verify this by checking the database CSV logs (pg_log/log). If the logged queries start with execute <statement_name>: (e.g., execute ST7F9968028900: select ...), it can be confirmed that the client is utilizing server-side prepared statements.