PostgreSQL 101: Pending Queries
pg_stat_activity whose view is a boolean field waiting has the column. The values of this column
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query =
queries where the transaction is ongoing, and
current_timestamp - query_start > '1 min';
when selected, it runs as queries waiting for a system lock to be released. To explain it another way; pg_stat_activity if its appearance waiting = TRUE If we filter as, in this case the results are returned with the first query above.
Who is blocking my query?
After learning that a query is blocked, we immediately want to find out who is blocking it. A query similar to the one below will give us the answer to this problem:
SELECT
w.current_query as waiting_query,
w.procpid as w_pid,
w.usename as w_user,
l.current_query as locking_query,
l.procpid as l_pid,
l.usename as l_user,
t.schemaname || '.' || t.relname as tablename
FROM pg_stat_activity w
join pg_locks l1 on w.procpid = l1.pid and not l1.granted
join pg_locks l2 on l1.relation = l2.relation and l2.granted
join pg_stat_activity l on l2.pid = l.procpid
join pg_stat_user_tables t on l1.relation = t.relid
WHERE w.waiting;
The result of this query will show the process ID along with the process number, the user, and the current query, along with the blocking and blocked operators. The query result also shows the names of the schema and table that caused the block.
Usernames are also a system view that pg_stat_user_tables is retrieved from a view via a join.
