PGXC_WAIT_DETAIL

PGXC_WAIT_DETAIL displays detailed information about the SQL waiting hierarchy of all nodes in a cluster. This view is supported only by clusters of version 8.1.3.200 or later.

**Table 1** PGXC_WAIT_DETAIL columns
Name	Type	Description
level	integer	Level in the wait hierarchy. The value starts with 1 and increases by 1 when there is a wait relationship.
lock_wait_hierarchy	text	Wait hierarchy, in the format of Node name: Process ID->Node name:Waiting process ID->Node name:Waiting process ID->...
node_name	text	Node name
db_name	text	Database name
thread_name	text	Thread name
query_id	bigint	ID of a query statement
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
wait_event	text	Virtual ID of the transaction holding or awaiting this lock
exec_cn	boolean	SQL execution CN
wait_node	text	Lock level
query	text	Query statement
application_name	text	Name of the application connected to the backend
backend_start	timestamp with time zone	Startup time of the backend process, that is, the time when the client connects to the server
xact_start	timestamp with time zone	Start time of the current transaction
query_start	timestamp with time zone	Start time of the active query
waiting	boolean	Waiting status
state	text	Overall state of the backend
waittime	timestamp with time zone	Timestamp when the lock wait starts. This column is available only in clusters of version 9.1.0.200 or later.
holdtime	timestamp with time zone	Timestamp when the lock starts to be obtained. This column is available only in clusters of version 9.1.0.200 or later.

Example

Connect to the CN, start a transaction, and perform the update operation.
1

begin;update td set c2=6 where c1=1;
Open another window to connect to the CN, start another transaction, and perform the update operation. (Do not update the same record concurrently.)
1

begin;update td set c2=6 where c1=7;
In this case, the update operation is blocked.
Open another window to connect to the CN node and create an index.
1

create index c2_key on td(c2);

Run the select * from pgxc_wait_detail; command.

      
       
         
         
           SELECT * FROM PGXC_WAIT_DETAIL;
level |                lock_wait_hierarchy                 | node_name | db_name  | thread_name |     query_id      |       tid       | lwtid  | ptid | tlevel | sm
pid | wait_status  | wait_event | exec_cn | wait_node |                 query                  | application_name |         backend_start         |          xact_st
art           |          query_start          | waiting | state
-------+----------------------------------------------------+-----------+----------+-------------+-------------------+-----------------+--------+------+--------+---
----+--------------+------------+---------+-----------+----------------------------------------+------------------+-------------------------------+-----------------
--------------+-------------------------------+---------+--------
1 | cn_5001:139870843444360                            | cn_5001   | postgres | workload    | 73183493945299462 | 139870843444360 | 578531 |      |      0 |
0 | wait node    |            | t       |           | WLM fetch collect info from data nodes | workload         | 2023-03-13 13:56:56.611486+08 | 2023-03-14 11:54
:33.562808+08 | 2023-03-13 13:57:00.262736+08 | t       | active
1 | cn_5001:139870843654544                            | cn_5001   | postgres | gsql        | 73183493945299204 | 139870843654544 | 722259 |      |      0 |
0 | wait node    |            | t       |           | update td set c2=6 where c1=1;         | gsql             | 2023-03-14 11:52:05.176588+08 | 2023-03-14 11:52
:19.054727+08 | 2023-03-14 11:53:58.114794+08 | t       | active
1 | cn_5001:139870843655296                            | cn_5001   | postgres | gsql        | 73183493945299218 | 139870843655296 | 722301 |      |      0 |
0 | wait node    |            | t       |           | update td set c2=6 where c1=7;         | gsql             | 2023-03-14 11:52:08.084265+08 | 2023-03-14 11:52
:42.978132+08 | 2023-03-14 11:53:59.459575+08 | t       | active
1 | cn_5001:139870843656424                            | cn_5001   | postgres | gsql        | 73183493945299223 | 139870843656424 | 722344 |      |      0 |
0 | acquire lock | relation   | t       |           | create index c2_key on td(c2);          | gsql             | 2023-03-14 11:52:10.967028+08 | 2023-03-14 11:52
:53.463227+08 | 2023-03-14 11:54:00.25203+08  | t       | active
2 | cn_5001:139870843656424 -> cn_5001:139870843655296 | cn_5001   | postgres | gsql        | 73183493945299218 | 139870843655296 | 722344 |      |        |
|              |            | f       |           | update td set c2=6 where c1=7;         | gsql             | 2023-03-14 11:52:08.084265+08 | 2023-03-14 11:52
:42.978132+08 | 2023-03-14 11:53:59.459575+08 | t       | active
(5 rows)

          

        

      
     