Help Center/ Data Warehouse Service/ Developer Guide/ Developer Guide (9.1.1.x)/ DWS Performance Tuning/ Performance Diagnosis/ Slow SQL Analysis/ Checking Blocked Statements
Updated on 2026-01-04 GMT+08:00
View PDF
Share

Checking Blocked Statements

During database running, query statements are blocked in some service scenarios and run for an excessively long time. In this case, you can forcibly terminate the faulty session.

Procedure

  1. View blocked query statements and information about the tables and schemas that block the query statements. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    		 
    SELECT w.query as waiting_query,
w.coorname as w_cn,
w.pid as w_pid,
w.usename as w_user,
l.query as locking_query,
l.pid as l_pid,
l.usename as l_user,
t.schemaname || '.' || t.relname as tablename
from pgxc_stat_activity w join pg_locks l1 on w.pid = l1.pid
and not l1.granted join pg_locks l2 on l1.relation = l2.relation
and l2.granted join pgxc_stat_activity l on l2.pid = l.pid join pg_stat_user_tables t on l1.relation = t.relid
where w.waiting;

    The thread ID, CN name, user information, query status, as well as information about the tables and schemas that block the query statements are returned.

  2. Run the following command to terminate the required session, w_cn and w_pid indicate the CN name and thread ID, respectively. 

    1
    		 
    EXECUTE DIRECT ON(w_cn) 'SELECT pg_terminate_backend(w_pid)';

    If information similar to the following is displayed, the session is successfully terminated:

     PG_TERMINATE_BACKEND
----------------------
 t
(1 row)

    If a command output similar to the following is displayed, a user is attempting to terminate the session, and the session will be reconnected rather than being terminated.

    FATAL:  terminating connection due to administrator command
FATAL:  terminating connection due to administrator command
The connection to the server was lost. Attempting reset: Succeeded.

    If the PG_TERMINATE_BACKEND function is used to terminate the background threads of the session, the gsql client will be reconnected rather than be logged out.

Parent Topic: Slow SQL Analysis