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