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

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

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

7 Steps to configure BDR replication in postgresql

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform