Finding blocked sessions

In this recipe, we will be discussing how to verify the queries that are currently blocking the SQL queries.

Getting ready

In any database, sessions will be blocked due to multiple technical reasons, to avoid concurrent access on the same resource. In PostgreSQL, a session can be blocked due to an idle in transaction or due to concurrent access on the same resource or due to some prepared transactions. In this recipe, we will be discussing how to get all the blocked/waiting sessions from the currently running instance.

How to do it...

1. Initiate pgbench as follows:

           $ pgbench -i; pgbench -c 4 -T 100
   

2. Connect to your database using the psql client as a superuser and execute the following SQL command:

           $ psql -h localhost -U postgres 
           postgres=# 
           SELECT
             datname, 
             usename, 
             application_name, 
             now()-backend_start AS "Session duration", 
             pid, 
             query...