Friday, April 12, 2013

Show blocking Postgres processes and kill them

Have you ever had to kill your Postgres cluster because one hanging client starved other processes until most clients became unresponsive blocking on this one pesky process?

There is a very nice way to show currently blocked queries and the processes those are blocking on slightly adapted from this query posted on the Postgres mailing list. I suggest putting it into a view so you can easily access it when you need it:

CREATE VIEW blocking_procs AS
SELECT 
    kl.pid as blocking_pid,
    ka.usename as blocking_user,
    ka.current_query as blocking_query,
    bl.pid as blocked_pid,
    a.usename as blocked_user, 
    a.current_query as blocked_query, 
    to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
FROM pg_catalog.pg_locks bl
    JOIN pg_catalog.pg_stat_activity a 
        ON bl.pid = a.procpid
    JOIN pg_catalog.pg_locks kl 
        ON bl.locktype = kl.locktype
        and bl.database is not distinct from kl.database
        and bl.relation is not distinct from kl.relation
        and bl.page is not distinct from kl.page
        and bl.tuple is not distinct from kl.tuple
        and bl.virtualxid is not distinct from kl.virtualxid
        and bl.transactionid is not distinct from kl.transactionid
        and bl.classid is not distinct from kl.classid
        and bl.objid is not distinct from kl.objid
        and bl.objsubid is not distinct from kl.objsubid
        and bl.pid <> kl.pid 
    JOIN pg_catalog.pg_stat_activity ka 
        ON kl.pid = ka.procpid
WHERE kl.granted and not bl.granted
ORDER BY a.query_start;

How to test the query on a testing server (not your production DB server)


Connect to your database open a transaction and manually lock a table:

BEGIN;
LOCK your_table;

Leave the transaction and connection open.

Open another client that accesses that data:

# SELECT count(*) from your_table;

It now should be blocked.

View the currently held locks with a third client:

# SELECT * FROM blocking_procs;
blocking_pid   | 25842
blocking_user  | postgres
blocking_query | in transaction
blocked_pid    | 25844
blocked_user   | postgres
blocked_query  | SELECT COUNT(*) FROM "your_table"
age            | 00h:00m:23s


It's now possible to kill the offending process holding the lock using:


# SELECT pg_terminate_backend(25842);

This will kill the connection where you've set the lock and the open transaction is rolled back but it seems to leave everything else intact. The second client should now get the response from the server.


Tested on Postgres 9.1.

8 comments:

  1. Very helpful script for identifying blocking process.

    ReplyDelete
  2. /* Updated for PostgreSQL 9.3 */
    CREATE VIEW blocking_procs AS
    SELECT
    kl.pid as blocking_pid,
    ka.usename as blocking_user,
    ka.query as blocking_query,
    bl.pid as blocked_pid,
    a.usename as blocked_user,
    a.query as blocked_query,
    to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
    FROM pg_catalog.pg_locks bl
    JOIN pg_catalog.pg_stat_activity a
    ON bl.pid = a.pid
    JOIN pg_catalog.pg_locks kl
    ON bl.locktype = kl.locktype
    and bl.database is not distinct from kl.database
    and bl.relation is not distinct from kl.relation
    and bl.page is not distinct from kl.page
    and bl.tuple is not distinct from kl.tuple
    and bl.virtualxid is not distinct from kl.virtualxid
    and bl.transactionid is not distinct from kl.transactionid
    and bl.classid is not distinct from kl.classid
    and bl.objid is not distinct from kl.objid
    and bl.objsubid is not distinct from kl.objsubid
    and bl.pid <> kl.pid
    JOIN pg_catalog.pg_stat_activity ka
    ON kl.pid = ka.pid
    WHERE kl.granted and not bl.granted
    ORDER BY a.query_start;

    ReplyDelete
  3. Nice Article !

    Really this will help to people of PostgreSQL Community.
    I have also prepared small note on this, Find and stop all running connections and sessions of PostgreSQL.

    http://www.dbrnd.com/2015/11/postgresql-script-to-kill-all-running-connections-and-sessions-of-a-database/

    ReplyDelete
  4. Very nice! For AWS's RDS Postgres , it is necessary to replace both occurences of "procpid" in your query with "pid" and both occurences of "current_query" with "query"

    ReplyDelete
  5. How rapidly you Fix the Postgres Error if Current Transaction is prematurely ended? Contact to Postgres SQL Support for Windows

    In the vast majority of the situation we have seen that if any client executes a non-remedy SQL proclamation then they will get a blunder message like this "Present Transaction is prematurely ended" however here don't stress on the grounds that the Postgres won't end the execution independent from anyone else it implies you can undoubtedly or physically settle this issue yet it require some specialized abilities. On the off chance that you don't how to do physically at that point instantly contact to Postgres SQL Support Linux or PostgreSQL Remote Database Service.

    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  6. The most effective method to settle a Stuck Postgres Process through Postgres SQL Support for Windows
    At some point your Postgres procedure will adhere because of specialized issues and because of this reason you are never again to work with your Postgres database. In this issue, a procedure in Postgres has bolted and which is keeping your application from working. To determine this issue, you can see cautioning in your log yield to see the points of interest of stuck process. You can likewise execute the individual procedure to tackle this issue. In the event that physically you are not ready to explain this issue at that point attempt to contact Postgres SQL Support for Linux or PostgreSQL Remote Database Service. Our expert database specialists attempt their level best to take care of your concern and make you mistake free

    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete