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 pgBadger

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

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart