Checking Blocked Statements

Procedure

1. Connect to a database. For details, see Connecting to a Database.
2. Check the blocked query statements, the SQL statement that blockes the query, query status, and session ID (applicable when the SQL thread pool is enabled).

   SELECT w.query AS waiting_query,
   w.state as w_state,
   w.datname as w_datname,
   w.usename as w_user,
   w.client_addr as w_client_addr,
   l.query AS locking_query,
   l.state as l_state,
   l.pid AS l_pid,
   l.sessionid as l_sessionid
   FROM pgxc_stat_activity AS w
   JOIN (
       SELECT query_id,block_sessionid,global_sessionid,node_name
       FROM pgxc_thread_wait_status
   ) AS a ON a.query_id = w.query_id
    JOIN (
       SELECT sessionid,node_name,global_sessionid  FROM pgxc_thread_wait_status
   ) AS b ON b.sessionid = a.block_sessionid and b.node_name=a.node_name
   JOIN (select query,pid,sessionid,global_sessionid,state from pgxc_stat_activity ) l
   on substring(l.global_sessionid,0,instr(l.global_sessionid,'#')) ilike substring(b.global_sessionid,0,instr(b.global_sessionid,'#'))
   WHERE a.block_sessionid !=0;

   This query returns the session ID, user information, query status, and the statement that causes the blocking and its query status and session ID.

3. Run the following command to terminate the required session, where 139834762094352 is the thread ID:

   1	SELECT PG_TERMINATE_BACKEND(139834762094352);

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

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

   If information similar to the following is displayed, the user is attempting to end the current session:

   FATAL:  terminating connection due to administrator command
   FATAL:  terminating connection due to administrator command

   

   1. If the PG_TERMINATE_BACKEND function is used to terminate the backend threads of the current session and the user is an initial user, the gsql client is reconnected automatically rather than be logged out. The message "The connection to the server was lost. Attempting reset: Succeeded." is returned. Otherwise, the client fails to be reconnected and the error message "The connection to the server was lost. Attempting reset: Failed." is returned because only the initial user can use password-free login.

   2. If the PG_TERMINATE_BACKEND function is used to terminate inactive backend threads and the thread pool is opened, idle sessions do not have thread IDs and cannot be ended. In non-thread pool mode, ended sessions are not automatically reconnected.