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 |
Examples
- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
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)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot