• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Systems Tables and Views
  5. PG_THREAD_WAIT_STATUS

PG_THREAD_WAIT_STATUS

The PG_THREAD_WAIT_STATUS view 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

lwtid

integer

Lightweight thread ID of the current thread

ptid

integer

Parent thread of the streaming thread

tlevel

integer

Level of the streaming thread

smpid

integer

Concurrent thread ID

wait_status

text

Waiting status of the current thread. For details about the waiting status, see Table 2.

wait_event

text

If wait_status is acquire lock, acquire lwlock, or wait io, this column describes the lock, lightweight lock, and I/O information, respectively. If wait_status is not any of the three values, this column is empty.

The waiting statuses in the wait_status column are as follows:

Table 2 Waiting status 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 network communication packet read to complete

wait pooler get conn

Waiting for pooler to obtain the connection

wait pooler abort conn

Waiting for pooler to terminate the connection

wait pooler clean conn

Waiting for pooler to clear connections

get conn

Obtaining the connection to other nodes

set cmd

Executing SET/RESET/TRANSACTION BLOCK LEVEL PARA SET/SESSION LEVEL PARA SET through the connection

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

Waiting for receiving data through the connection to an instance

wait transaction sync

Waiting for transaction synchronization

wait wal sync

Waiting for the completion of wal log 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 storage or the CU in the column storage into the synchronization queue

net flush data

Waiting for sending data to other nodes in the network

stream get conn

Waiting for establishing the connection to consumer nodes when the stream flow is initialized

wait producer ready

Waiting for every producer to get ready when the stream flow is initialized

synchronize quit

Waiting for the threads in the stream thread group to quit when the steam plan ends

nodegroup destroy

Waiting for destroying the stream node group when the steam plan ends

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.)

List of wait events corresponding to lightweight locks

wait_event Value

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 already exists

BtreeVacuumLock

Used to prevent VACUUM from clearing pages that are being used by B-tree indexes

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 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

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

DfsConnectorCacheLock

Used to protect a global hash table where HDFS connection handles are cached

dummyServerInfoCacheLock

Used to protect a global hash table where the information about computing Node Group connections is cached

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

DfsUserLoginLock

Used to protect a global linked table where HDFS user information is stored

DfsSpaceCacheLock

Used to ensure that the IDs of files to be imported to an HDFS table increase monotonically

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 the 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

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

LLVMParseIRLock

Used to compile and parse a finished IR function from the IR file at the start position of a query

RPNumberLock

Used by a DN on a computing Node Group to count the number of threads for a task where plans are being executed

ClusterRPLock

Used to control concurrent access on cluster load data maintained in a CCN of the cluster

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

It is 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

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

List of wait events corresponding to I/O

wait_event Value

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, mainly 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, mainly 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 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 the 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 the 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 submission and restoration.

TwophaseFileSync

Flushes the pg_twophase file to a disk, mainly during two-phase transaction submission and restoration.

TwophaseFileWrite

Writes the pg_twophase file, mainly during two-phase transaction submission 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.

List of wait events corresponding to transaction locks

wait_event Value

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.