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