Pivotal Knowledge Base

Follow

How to cancel running queries OR idle sessions

Goal

There might be scenarios where a user query needs to be cancelled or an idle session needs to be terminated by Greenplum Database (GPDB) DBA/superuser, so the goal of the article is to help you understand when to take the approach.

Note: Avoid using Operating System kill command to kill the query process as it will result into database crash/Postmaster Reset.

Solution

To test this functionality we will take a look at the below 2 sessions (one IDLE and one running. ) , so the pg_stat_activity looks like.

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 query is running, the function will not impact when the session is IDLE.

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

Canceling the query may take some time depending on the cleanup/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 where the session is not <IDLE> ..

 gpadmin=# select pg_terminate_backend(24285) ; 
 pg_terminate_backend 
 ----------------------
  t 
 (1 row) 
  • 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)

The IDLE session that you have witnessed again above is after we have cancelled the query through pg_cancel_backend, the query has been cancelled by the function but the user session still is connected.

Comments

  • Avatar
    Gurupreet Singh Bhatia

    Thanks, Its really helpful

  • Avatar
    Nabil Hjiej-Andaloussi

    With regards idle connections I would rather using a pooling mechanism such as pgbouncer and add the settings in there to kill idle processes after a certain amount of time - It's much cleaner and saves you have to watch for idle processes.

Powered by Zendesk