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