Find and kill postgresql blocked sessions

Find and kill postgresql blocked sessions

1. Create a view that you can use at any time:

CREATE VIEW pg_blocking AS SELECT

Blocked_locks.pid AS blocked_pid

Blocked_activity.usename AS blocked_user

Blocking_locks.pid AS blocking_pid

Blocking_activity.usename AS blocking_user

Blocked_activity. QUERY AS blocked_statement

Blocking_activity. QUERY AS current_statement_in_blocking_process

FROM

Pg_catalog.pg_locks blocked_locks

JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid

JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype

AND blocking_locks. DATABASE IS NOT DISTINCT

FROM

Blocked_locks. DATABASE

AND blocking_locks.relation IS NOT DISTINCT

FROM

Blocked_locks.relation

AND blocking_locks.page IS NOT DISTINCT

FROM

Blocked_locks.page

AND blocking_locks.tuple IS NOT DISTINCT

FROM

Blocked_locks.tuple

AND blocking_locks.virtualxid IS NOT DISTINCT

FROM

Blocked_locks.virtualxid

AND blocking_locks.transactionid IS NOT DISTINCT

FROM

Blocked_locks.transactionid

AND blocking_locks.classid IS NOT DISTINCT

FROM

Blocked_locks.classid

AND blocking_locks.objid IS NOT DISTINCT

FROM

Blocked_locks.objid

AND blocking_locks.objsubid IS NOT DISTINCT

FROM

Blocked_locks.objsubid

AND blocking_locks.pid! = blocked_locks.pid

JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid

WHERE

NOT blocked_locks.GRANTED

The view query created by the query is blocked:

Select * from pg_blocking

two。 Kill the detected blocking session:

There are two ways to kill. The first is:

SELECT pg_cancel_backend (PID); # cancel an executing SQL

The second is:

SELECT pg_terminate_backend (PID); # terminates a background service process and releases the resources of the background service process.

The difference between these two functions is that the pg_cancel_backend () function actually configures a cancel flag to the SQL task that is being executed, and the task that is executing is actively exiting after detecting this flag at the appropriate time; but if the task does not actively detect this flag, the task cannot exit normally, so you need to use the pg_terminate_backend () command to terminate the execution of SQL.

Usually, pg_stat_activity is queried first to try to find out the long-running SQL, and then terminate it. You can also terminate the reply in batch:

SELECT 'SELECT pg_terminate_backend (' | | blocking_pid | |'); 'from pg_blocking