Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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.

Comments

Popular posts from this blog

PostgreSQL DOMAIN Data Type -2

VMWARE WORKSTATION 3,4,5,6,7,8,9,10,11,12,14,15...etc LICENSE KEYS COLLECTION

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

Postgres Database Patch

PostgreSQL Database startup / shutdown /restart