============To check the locking and blocking===============

Query to be used mostly -----

SELECT

    activity.pid                AS blocked_pid,

    activity.usename            AS blocked_user,

    activity.application_name   AS blocked_app,

    activity.client_addr        AS blocked_client,

    activity.wait_event_type,

    activity.wait_event,

    now() - activity.query_start AS blocked_duration,

    activity.query              AS blocked_query,

    blocking.pid                AS blocking_pid,

    blocking.usename            AS blocking_user,

    blocking.application_name   AS blocking_app,

    blocking.client_addr        AS blocking_client,

    blocking.query              AS blocking_query,

    now() - blocking.query_start AS blocking_query_duration

FROM pg_stat_activity AS activity

JOIN pg_stat_activity AS blocking

  ON blocking.pid = ANY (pg_blocking_pids(activity.pid))

ORDER BY blocked_duration DESC;

--------------------------------------------------------------------------------------------------------------
1. To check the locking and blocking(Main query to use) 

SELECT

COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,

now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,

blockeda.query as blocked_query, blockedl.mode as blocked_mode,

blockinga.pid AS blocking_pid, blockinga.query as blocking_query,

blockingl.mode as blocking_mode

FROM pg_catalog.pg_locks blockedl

JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid

JOIN pg_catalog.pg_locks blockingl ON(( (blockingl.transactionid=blockedl.transactionid) OR

(blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)) AND blockedl.pid != blockingl.pid)

JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid

WHERE NOT blockedl.granted AND blockinga.datname = current_database();

-[ RECORD 1 ]----+-------------------------------------------------------------------------------

locked_item      | transactionid

waiting_duration | 04:01:41.317819

blocked_pid      | 25192

blocked_query    | SELECT DISTINCT t.schemaname, t.tablename                                     +

                 |   FROM pg_catalog.pg_publication_tables t                                     +

                 |  WHERE t.pubname IN ('znwuserspublication')

blocked_mode     | ShareLock

blocking_pid     | 13747

blocking_query   | INSERT INTO nwdev.nsNWCustomerUsageAggregate as a                             +

                 |  VALUES ($1, to_jsonb($2::jsonb), to_jsonb($3::jsonb), $4, $5, $6, $7, $8, $9)

blocking_mode    | ExclusiveLock

prod=>

====================How to Kill long-running PostgreSQL query================

2. How to Kill long-running PostgreSQL query

In case you don't want a query to continue running inside the database, you can use the pid (process ID) from the pg_stat_activity

or pg_locks views to terminate the running process.

pg_cancel_backend(pid) will attempt to gracefully kill a running query process.

pg_terminate_backend(pid) will immediately kill the running query process, but potentially have side affects across additional

queries running on your database server. The full connection may be reset when running pg_terminate_backend.

pg_cancel_backend('pid')

select pg_terminate_backend('pid')

6419

3780

3779

select pg_terminate_backend('6419');

pg_terminate_backend('3780');

pg_terminate_backend('3779');

========================Dynamic Query to kill multiple sessions===================

3. Dynamic Query to kill multiple sessions

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session where status='INACTIVE';

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE datname = 'Database_Name'

AND pid <> pg_backend_pid()

AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 

AND state_change < current_timestamp - INTERVAL '15' MINUTE;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity

WHERE datname = 'databasename'

AND pid <> pg_backend_pid()

AND state in ('idle');

====IMP - To kill all the active session in the database -

https://www.dbvis.com/thetable/how-to-kill-all-connections-to-a-database-in-postgresql/#:~:text=Dropping%20All%20Active%20Connections%20to%20a%20PostgreSQL%20Database&text=1%20SELECT%20pg_terminate_backend(pid)%202,want%20to%20close%20sessions%20for

SELECT

 pg_terminate_backend(pid)

FROM

 pg_stat_activity

WHERE

 datname = 

'QUARANTINE'

AND

 leader_pid 

IS NULL

;

-------------------To monitor an activity

SELECT pid,

       usename,

       datname,

       state,

       query_start,

       now() - query_start AS duration,

       query

FROM pg_stat_activity

WHERE query ILIKE '%DROP%';

=====================Locking and Blocking=======================

4. . To find the locks in the database.

select

relname as relation_name,

query,

pg_locks.*

from pg_locks

join pg_class on pg_locks.relation = pg_class.oid

join pg_stat_activity on pg_locks.pid = pg_stat_activity.pid

======================How to Find blocked query and blocking queries=============

5. . How to Find blocked query and blocking queries

SELECT

activity.pid,

activity.usename,

activity.query,

blocking.pid AS blocking_id,

blocking.query AS blocking_query

FROM pg_stat_activity AS activity

JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

Output -

prod-> JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

 pid | usename | query | blocking_id | blocking_query

-----+---------+-------+-------------+----------------

(0 rows)

================== How to View the Locks on Tables using pg_lock===============

6.  How to View the Locks on Tables using pg_lock

select * from pg_locks;

        How to find locks with table names and queries

select relname as relation_name, query, pg_locks.* from pg_locks

join pg_class on pg_locks.relation = pg_class.oid

join pg_stat_activity on pg_locks.pid = pg_stat_activity.pid;

==========================================================================

======To kill all locked sessions in PostgreSQL, you can follow these steps==========

1.  Identify Locked Sessions-

SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_locks.locktype, pg_locks.mode

FROM pg_stat_activity

JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid

WHERE pg_locks.granted = 'f';

2. Terminate the Locked Sessions

SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid

WHERE pg_locks.granted = 'f';