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

Examples

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

    begin;update td set c2=6 where c1=1;
    
  2. 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.

  3. Open another window to connect to the CN node and create an index.

    create index c2_key on td(c2);
    
  4. 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)