PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

Create a blocking scenario with the following steps:

1. Connect to a database and create a test table, and then lock it with this SQL code:

CREATE TABLE lock_test (junk INT); 
BEGIN; 
LOCK TABLE lock_test IN EXCLUSIVE MODE; 

2. In a second connection, execute the following statement:

INSERT INTO lock_test (junk) VALUES (42); 

Next, investigate the problem with these steps (PostgreSQL 9.6 and above only):

1. Execute this query to obtain locking information:

SELECT pid, locktype, mode, granted, 
       relation::REGCLASS::TEXT AS locked_object 
  FROM pg_locks 
 WHERE relation IS NOT NULL 
 ORDER BY relation, granted DESC;

2. Run this query to determine blocker sources:

SELECT p.pid, p.query, s.pid AS blocker_pid, s.query AS blocker_query FROM pg_stat_activity ...