PostgreSQL High Availability Cookbook - Second 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:

        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 ...