PG_THREAD_WAIT_STATUS

PG_THREAD_WAIT_STATUS allows you to test the block waiting status about the backend thread and auxiliary thread of the current instance.

**Table 1** PG_THREAD_WAIT_STATUS columns
Name	Type	Description
node_name	text	Current node name
db_name	text	Database name
thread_name	text	Thread name
query_id	bigint	Query ID. It is equivalent to debug_query_id.
tid	bigint	Thread ID of the current thread
sessionid	bigint	Current session ID
lwtid	integer	Lightweight thread ID of the current thread
psessionid	bigint	Parent session ID
tlevel	integer	Level of the streaming thread
smpid	integer	Concurrent thread ID
wait_status	text	For details about the waiting status of the current thread, see Table 2.
wait_event	text	Event that the current thread is waiting for
locktag	text	Information about the lock that the current thread is waiting for
lockmode	text	Lock mode that the current thread is waiting to obtain. The values include table-level lock, row-level lock, and page-level lock modes.
block_sessionid	bigint	ID of the session that blocks the current thread from obtaining the lock
global_sessionid	text	Global session ID

The waiting states in the wait_status column are as follows:

**Table 2** Waiting state list
Value	Description
none	Waiting for no event
acquire lock	Waiting for locking until the locking succeeds or times out
acquire lwlock	Waiting for a lightweight lock
wait io	Waiting for I/O completion
wait cmd	Waiting for reading network communication packets to complete
wait pooler get conn	Waiting for pooler to obtain connections
wait pooler abort conn	Waiting for pooler to terminate connections
wait pooler clean conn	Waiting for pooler to clear connections
pooler create conn: [nodename], total N	Waiting for the pooler to set up a connection. The connection is being established with the node specified by nodename, and there are N connections waiting to be set up.
get conn	Obtaining the connection to other nodes
set cmd: [nodename]	Waiting for running the SET, RESET, TRANSACTION BLOCK LEVEL PARA SET, or SESSION LEVEL PARA SET statement on the connection. The statement is being executed on the node specified by nodename.
cancel query	Canceling the SQL statement that is being executed through the connection
stop query	Stopping the query that is being executed through the connection
wait node: [nodename](plevel), total N, [phase]	Waiting for receiving data from a connected node. The thread is waiting for data from the plevel thread of the node specified by nodename. The data of N connections is waiting to be returned. If phase is included, the possible phases are as follows: begin: The transaction is being started. commit: The transaction is being committed. rollback: The transaction is being rolled back.
wait transaction sync: xid	Waiting for synchronizing the transaction specified by xid
wait wal sync	Waiting for the completion of WAL of synchronization from the specified LSN to the standby instance
wait data sync	Waiting for the completion of data page synchronization to the standby instance
wait data sync queue	Waiting for putting the data pages that are in the row-store or the CU in the column-store into the synchronization queue
flush data: [nodename](plevel), [phase]	Waiting for sending data to the plevel thread of the node specified by nodename. If phase is included, the possible phase is wait quota, indicating that the current communication flow is waiting for the quota value.
stream get conn: [nodename], total N	Waiting for connecting to the consumer object of the node specified by nodename when the stream flow is initialized. There are N consumers waiting to be connected.
wait producer ready: [nodename](plevel), total N	Waiting for each producer to be ready when the stream flow is initialized. The thread is waiting for the procedure of the plevel thread on the nodename node to be ready. There are N producers waiting to be ready.
synchronize quit	Waiting for the threads in the stream thread group to quit when the stream plan ends
nodegroup destroy	Waiting for destroying the stream node group when the stream plan ends
wait active statement	Waiting for job execution under resource and load control.
gtm connect	Waiting for connecting to GTM
gtm get gxid	Wait for obtaining transaction IDs from GTM
gtm get snapshot	Wait for obtaining transaction snapshots from GTM
gtm begin trans	Waiting for GTM to start a transaction
gtm commit trans	Waiting for GTM to commit a transaction
gtm rollback trans	Waiting for GTM to roll back a transaction
gtm start prepare trans	Waiting for GTM to start the prepare phase of a two-phase transaction
gtm prepare trans	Waiting for GTM to complete the prepare phase of a two-phase transaction
gtm open sequence	Waiting for GTM to open a sequence
gtm close sequence	Waiting for GTM to close a sequence
gtm create sequence	Waiting for GTM to create a sequence
gtm alter sequence	Waiting for GTM to modify a sequence
gtm get sequence val	Waiting for obtaining the next value of a sequence from GTM
gtm set sequence val	Waiting for GTM to set a sequence value
gtm drop sequence	Waiting for GTM to delete a sequence
gtm rename sequece	Waiting for GTM to rename a sequence
analyze: [relname], [phase]	The thread is doing ANALYZE to the relname table. If phase is included, the possible phase is autovacuum, indicating that the database automatically enables the AutoVacuum thread to execute ANALYZE.
vacuum: [relname], [phase]	The thread is doing VACUUM to the relname table. If phase is included, the possible phase is autovacuum, indicating that the database automatically enables the AutoVacuum thread to execute VACUUM.
vacuum full: [relname]	The thread is doing VACUUM FULL to the relname table.
create index	An index is being created
HashJoin - [ build hash \| write file ]	The HashJoin operator is being executed. In this phase, you need to pay attention to the execution time-consuming. build hash: The HashJoin operator is creating a hash table. write file: The HashJoin operator is writing data to disks.
HashAgg - [ build hash \| write file ]	The HashAgg operator is being executed. In this phase, you need to pay attention to the execution time-consuming. build hash: The HashAgg operator is creating a hash table. write file: The HashAgg operator is writing data to disks.
HashSetop - [build hash \| write file ]	The HashSetop operator is being executed. In this phase, you need to pay attention to the execution time-consuming. build hash: The HashSetop operator is creating a hash table. write file: The HashSetop operator is writing data to disks.
Sort \| Sort - [fetch tuple \| write file]	The Sort operator is used for sorting. fetch tuple indicates that the Sort operator is obtaining tuples, and write file indicates that the Sort operator is writing data to disks.
Material \| Material - write file	The Material operator is being executed. write file indicates that the Material operator is writing data to disks.
standby read recovery conflict	The read-only mode of the standby node conflicts with the log replay mode.
standby get snapshot	The standby node obtains the snapshot in read-only mode.

If wait_status is acquire lwlock, acquire lock, or wait io, there is an event performing I/O operations or waiting for obtaining the corresponding lightweight lock or transaction lock.

The following table describes the corresponding wait events when wait_status is acquire lwlock. If wait_event is extension, the lightweight lock is dynamically allocated and is not monitored.

**Table 3** List of wait events corresponding to lightweight locks
wait_event	Description
ShmemIndexLock	Used to protect the primary index table, a hash table, in shared memory
OidGenLock	Used to prevent different threads from generating the same OID
XidGenLock	Used to prevent two transactions from obtaining the same XID
ProcArrayLock	Used to prevent concurrent access to or concurrent modification on the ProcArray shared array
SInvalReadLock	Used to prevent concurrent execution with invalid message deletion
SInvalWriteLock	Used to prevent concurrent execution with invalid message write and deletion
WALInsertLock	Used to prevent concurrent execution with WAL insertion
WALWriteLock	Used to prevent concurrent write from a WAL buffer to a disk
ControlFileLock	Used to prevent concurrent read/write or concurrent write/write on the pg_control file
CheckpointLock	Used to prevent multi-checkpoint concurrent execution
CLogControlLock	Used to prevent concurrent access to or concurrent modification on the Clog control data structure
SubtransControlLock	Used to prevent concurrent access to or concurrent modification on the subtransaction control data structure
MultiXactGenLock	Used to allocate a unique MultiXact ID in serial mode
MultiXactOffsetControlLock	Used to prevent concurrent read/write or concurrent write/write on pg_multixact/offset
MultiXactMemberControlLock	Used to prevent concurrent read/write or concurrent write/write on pg_multixact/members
RelCacheInitLock	Used to add a lock before any operations are performed on the init file when messages are invalid
CheckpointerCommLock	Used to send file flush requests to a checkpointer. The request structure needs to be inserted to a request queue in serial mode.
TwoPhaseStateLock	Used to prevent concurrent access to or modification on two-phase information sharing arrays
TablespaceCreateLock	Used to check whether a tablespace exists.
BtreeVacuumLock	Used to prevent VACUUM from clearing pages that are being used by B-tree indexes
AlterPortLock	Used to protect the CN's operation of changing the registered port number.
AutovacuumLock	Used to access the autovacuum worker array in serial mode
AutovacuumScheduleLock	Used to distribute tables requiring VACUUM in serial mode
AutoanalyzeLock	Used to obtain and release resources related to a task that allows for autoanalyze execution
SyncScanLock	Used to determine the start position of a relfilenode during heap scanning
NodeTableLock	Used to protect a shared structure that stores CN and DN node information.
PoolerLock	Used to prevent two threads from simultaneously obtaining the same connection from a connection pool
RelationMappingLock	Used to wait for the mapping file between system catalogs and storage locations to be updated
Async Ctl	Used to protect the async buffer.
AsyncCtlLock	Used to prevent concurrent access to or concurrent modification on the sharing notification status
AsyncQueueLock	Used to prevent concurrent access to or concurrent modification on the sharing notification queue
SerializableXactHashLock	Used to prevent concurrent read/write or concurrent write/write on a sharing structure for serializable transactions
SerializableFinishedListLock	Used to prevent concurrent read/write or concurrent write/write on a shared linked list for completed serial transactions
SerializablePredicateLockListLock	Used to protect a linked list of serializable transactions that have locks
OldSerXidLock	Used to protect a structure that records serializable transactions that have conflicts
FileStatLock	Used to protect a data structure that stores statistics file information
SyncRepLock	Used to protect Xlog synchronization information during primary-standby replication
DataSyncRepLock	Used to protect data page synchronization information during primary-standby replication
CStoreColspaceCacheLock	Used to add a lock when CU space is allocated for a column-store table
CStoreCUCacheSweepLock	Used to add a lock when CU caches used by a column-store table are cyclically washed out
MetaCacheSweepLock	Used to add a lock when metadata is cyclically washed out
dummyServerInfoCacheLock	Used to protect a global hash table where the information about cluster connections is cached. (Due to specification changes, the current version no longer supports the current feature. Do not use this feature.)
ExtensionConnectorLibLock	Used to add a lock when a specific dynamic library is loaded or uninstalled in ODBC connection initialization scenarios
SearchServerLibLock	Used to add a lock on the file read operation when a specific dynamic library is initially loaded in GPU-accelerated scenarios
LsnXlogChkFileLock	Used to serially update the Xlog flush points for primary and standby servers recorded in a specific structure
GTMHostInfoLock	Used to prevent concurrent access to or concurrent modification on GTM host information
ReplicationSlotAllocationLock	Used to add a lock when a primary server allocates stream replication slots during primary-standby replication
ReplicationSlotControlLock	Used to prevent concurrent update of replication slot status during primary-standby replication
ResourcePoolHashLock	Used to prevent concurrent access to or concurrent modification on a resource pool table, a hash table
WorkloadStatHashLock	Used to prevent concurrent access to or concurrent modification on a hash table that contains SQL requests from the CN side
WorkloadIoStatHashLock	Used to prevent concurrent access to or concurrent modification on a hash table that contains I/O information of the current DN
WorkloadCGroupHashLock	Used to prevent concurrent access to or concurrent modification on a hash table that contains Cgroup information
OBSGetPathLock	Used to prevent concurrent read/write or concurrent write/write on an OBS path
WorkloadUserInfoLock	Used to prevent concurrent access to or concurrent modification on a hash table that contains user information about load management. (The current feature is a lab feature. Contact Huawei engineers for technical support before using it.)
WorkloadRecordLock	Used to prevent concurrent access to or concurrent modification on a hash table that contains requests received by CNs during adaptive memory management
WorkloadIOUtilLock	Used to protect a structure that records iostat and CPU load information
WorkloadNodeGroupLock	Used to prevent concurrent access to or concurrent modification on a hash table that contains node group information in memory
JobShmemLock	Used to protect global variables in the shared memory that is periodically read during a scheduled task where MPP is compatible with Oracle
OBSRuntimeLock	Used to obtain environment variables, for example, GASSHOME
LLVMDumpIRLock	Used to export the assembly language for dynamically generating functions. The current feature is a lab feature. Contact Huawei technical support before using it.
LLVMParseIRLock	Used to compile and parse a finished IR function from the IR file at the start position of a query. The current feature is a lab feature. Contact Huawei technical support before using it.
RPNumberLock	Used by a DN on a cluster to count the number of threads for a task where plans are being executed. (Due to specification changes, the current version no longer supports the current feature. Do not use this feature.)
ClusterRPLock	Used by a cluster to control concurrent access on cluster load data maintained in a CCN of the cluster. (Due to specification changes, the current version no longer supports the current feature. Do not use this feature.)
CriticalCacheBuildLock	Used to load caches from a shared or local cache initialization file
WaitCountHashLock	Used to protect a shared structure in user statement counting scenarios
BufMappingLock	Used to protect operations on a table mapped to shared buffer
LockMgrLock	Used to protect a common lock structure
PredicateLockMgrLock	Used to protect a lock structure that has serializable transactions
OperatorRealTLock	Used to prevent concurrent access to or concurrent modification on a global structure that contains real-time data at the operator level
OperatorHistLock	Used to prevent concurrent access to or concurrent modification on a global structure that contains historical data at the operator level
SessionRealTLock	Used to prevent concurrent access to or concurrent modification on a global structure that contains real-time data at the query level
SessionHistLock	Used to prevent concurrent access to or concurrent modification on a global structure that contains historical data at the query level
CacheSlotMappingLock	Used to protect global CU cache information
BarrierLock	Used to ensure that only one thread is creating a barrier at a time
StartBlockMappingLock	Used by globalstat to obtain information such as startblockarray from pgstat.
ConsumerStateLock	Used to update the working state of the time series consumer.
DeleteConsumerLock	Used to maintain the time series consumer hash table.
NgroupDestoryLock	Adds a lock to the concurrent modification of the Node Group hash table.
NGroupMappingLock	Adds a lock to the concurrent modification of a single bucket that protects the NodeGroup hash table.
GlobalSeqLock	Used to manage global sequence numbers.
MatviewSeqnoLock	Used to manage the cache of materialized views.
GPCCommitLock	Used to protect the addition of the global Plan Cache hash table. The current feature is a lab feature. Contact Huawei technical support before using it.
GPCClearLock	Used to protect the clearing of the global plan cache hash table. The current feature is a lab feature. Contact Huawei technical support before using it.
GPCTimelineLock	Used to protect the timeline check of the global plan cache hash table. The current feature is a lab feature. Contact Huawei technical support before using it.
GPCMappingLock	Used to manage the global plan cache. The current feature is a lab feature. Contact Huawei technical support before using it.
GPCPrepareMappingLock	Used to manage the global plan cache. The current feature is a lab feature. Contact Huawei technical support before using it.
GPRCMappingLock	Used to manage the access and modification operations of the global cache hash table of autonomous transactions.
PldebugLock	Used to debug stored procedures and perform concurrent maintenance operations.
BufFreelistLock	Used to ensure the atomicity of free list operations in the shared buffer.
AddinShmemInitLock	Used to protect the initialization of the shared memory object.
wait active statement	Waiting for job execution under resource and load control.
wait memory	Waiting for obtaining the memory.
DnUsedSpaceHashLock	Used to update space usage information corresponding to a session.
InstanceRealTLock	Used to protect the update of the hash table that stores shared instance statistics.
IOStatLock	Used to concurrently maintain the hash table of resource management I/O statistics.

The following table describes the corresponding wait events when wait_status is wait io.

**Table 4** List of wait events corresponding to I/Os
wait_event	Description
BufFileRead	Reads data from a temporary file to a specified buffer.
BufFileWrite	Writes the content of a specified buffer to a temporary file.
ControlFileRead	Reads the pg_control file, mainly during database startup, checkpoint execution, and primary/standby verification.
ControlFileSync	Flushes the pg_control file to a disk, during database initialization.
ControlFileSyncUpdate	Flushes the pg_control file to a disk, mainly during database startup, checkpoint execution, and primary/standby verification.
ControlFileWrite	Writes to the pg_control file, during database initialization.
ControlFileWriteUpdate	Updates the pg_control file, mainly during database startup, checkpoint execution, and primary/standby verification.
CopyFileRead	Reads a file during file copying.
CopyFileWrite	Writes a file during file copying.
DataFileExtend	Writes a file during file name extension.
DataFileFlush	Flushes a table data file to a disk.
DataFileImmediateSync	Flushes a table data file to a disk immediately.
DataFilePrefetch	Reads a table data file asynchronously.
DataFileRead	Reads a table data file synchronously.
DataFileSync	Flushes table data file modifications to a disk.
DataFileTruncate	Truncates a table data file.
DataFileWrite	Writes a table data file.
LockFileAddToDataDirRead	Reads the postmaster.pid file.
LockFileAddToDataDirSync	Flushes the postmaster.pid file to a disk.
LockFileAddToDataDirWrite	Writes PID information into the postmaster.pid file.
LockFileCreateRead	Read the LockFile file %s.lock.
LockFileCreateSync	Flushes the LockFile file %s.lock to a disk.
LockFileCreateWRITE	Writes PID information into the LockFile file %s.lock.
RelationMapRead	Reads the mapping file between system catalogs and storage locations.
RelationMapSync	Flushes the mapping file between system catalogs and storage locations to a disk.
RelationMapWrite	Writes the mapping file between system catalogs and storage locations.
ReplicationSlotRead	Reads a stream replication slot file during a restart.
ReplicationSlotRestoreSync	Flushes a stream replication slot file to a disk during a restart.
ReplicationSlotSync	Flushes a temporary stream replication slot file to a disk during checkpoint execution.
ReplicationSlotWrite	Writes a temporary stream replication slot file during checkpoint execution.
SLRUFlushSync	Flushes the pg_clog, pg_subtrans, and pg_multixact files to a disk, mainly during checkpoint execution and database shutdown.
SLRURead	Reads the pg_clog, pg_subtrans, and pg_multixact files.
SLRUSync	Writes dirty pages into the pg_clog, pg_subtrans, and pg_multixact files, and flushes the files to a disk, mainly during checkpoint execution and database shutdown.
SLRUWrite	Writes the pg_clog, pg_subtrans, and pg_multixact files.
TimelineHistoryRead	Reads the timeline history file during database startup.
TimelineHistorySync	Flushes the timeline history file to a disk during database startup.
TimelineHistoryWrite	Writes to the timeline history file during database startup.
TwophaseFileRead	Reads the pg_twophase file, mainly during two-phase transaction commit and restoration.
TwophaseFileSync	Flushes the pg_twophase file to a disk, mainly during two-phase transaction commit and restoration.
TwophaseFileWrite	Writes the pg_twophase file, mainly during two-phase transaction commit and restoration.
WALBootstrapSync	Flushes an initialized WAL file to a disk during database initialization.
WALBootstrapWrite	Writes an initialized WAL file during database initialization.
WALCopyRead	Read operation generated when an existing WAL file is read for replication after archiving and restoration.
WALCopySync	Flushes a replicated WAL file to a disk after archiving and restoration.
WALCopyWrite	Write operation generated when an existing WAL file is read for replication after archiving and restoration.
WALInitSync	Flushes a newly initialized WAL file to a disk during log reclaiming or writing.
WALInitWrite	Initializes a newly created WAL file to 0 during log reclaiming or writing.
WALRead	Reads data from Xlogs during redo operations on two-phase files.
WALSyncMethodAssign	Flushes all open WAL files to a disk.
WALWrite	Writes a WAL file.

The following table describes the corresponding wait events when wait_status is acquire lock.

**Table 5** List of wait events corresponding to transaction locks
wait_event	Description
relation	Adds a lock to a table.
extend	Adds a lock to a table being scaled out.
partition	Adds a lock to a partitioned table.
partition_seq	Adds a lock to a partition of a partitioned table.
page	Adds a lock to a table page.
tuple	Adds a lock to a tuple on a page.
transactionid	Adds a lock to a transaction ID.
virtualxid	Adds a lock to a virtual transaction ID.
object	Adds a lock to an object.
cstore_freespace	Adds a lock to idle column-store space.
userlock	Adds a lock to a user.
advisory	Adds an advisory lock.