Pg_Cancel_Backend Vs Pg_Terminate_Backend In Postgresql

1.To test this functionality we will take a look at the below 2 sessions (one IDLE and one running. )

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)

2.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)

note:- Canceling the query may take some time depending on the cleanup/rollback of the transactions.

3.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)

4.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.