How to cancel running queries or idle sessions in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article explains how to cancel running queries or idle sessions in Greenplum Database (GPDB).

There might be scenarios where a user query needs to be canceled or an idle session needs to be terminated by Greenplum Database (GPDB) database administrator. The goal of the article is to help you understand the difference between canceling a running query versus canceling an idle session.

Note: Avoid using the operating system kill command to end the query process, as it will result in a database crash or a postmaster reset.

Resolution

Below is the pg_stat_activity for a running query and an idle session:

gpadmin=# select datname,usename,procpid,sess_id,current_query from pg_stat_activity ;
 datname |    usename    | procpid | sess_id |                                current_query                                 
---------+---------------+---------+---------+------------------------------------------------------------------------------
 gpadmin | running_user1 |   24174 |      26 | insert into test_table values (generate_series(1,100000000000));
 gpadmin | idle_user1    |   24285 |      27 | <IDLE>
 gpadmin | gpadmin       |   23494 |      21 | select datname,usename,procpid,sess_id,current_query from pg_stat_activity ;
3 rows)

pg_cancel_backend()

pg_cancel_backend (procpid from pg_stat_activity) should be used when the query is running. The function will not do anything when the session is IDLE.

gpadmin=# select pg_cancel_backend(24174) ; 
pg_cancel_backend 
------------------- 
 t 
 (1 row)

Note: Canceling the query may take some time depending on the cleanup or rollback of the transactions.

pg_terminate_backend()

pg_terminate_backend (procpid from pg_stat_activity) should be used for terminating IDLE sessions. Avoid using them on an active query or when the session is not IDLE.

 gpadmin=# select pg_terminate_backend(24285) ; 
 pg_terminate_backend 
 ----------------------
  t 
 (1 row)

The following is the state of  pg_stat_activity after running the above two commands:

gpadmin=# select datname,usename,procpid,sess_id,current_query from pg_stat_activity ;
 datname | usename       | procpid | sess_id | current_query 
 ---------+---------------+---------+---------+------------------------------------------------------------------------------ 
 gpadmin | running_user1 | 24174   | 26      |  <IDLE>
 gpadmin | gpadmin       | 23494   | 21      | select datname,usename,procpid,sess_id,current_query from pg_stat_activity ; 
 (2 rows)

Observe that the query has been canceled by the function but the user session still is connected.