How to detect query which holds the lock in Postgres?

by

Since 9.6 this is a lot easier as it introduced the function pg_blocking_pids() to find the sessions that are blocking another session.

So you can use something like this:

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. Any downsides of using data type “text” for storing strings?
  3. Index for finding an element in a JSON array
  4. Query for array elements inside JSON type
  5. Calculating Cumulative Sum in PostgreSQL
  6. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  7. Is it possible to make a recursive SQL query?
  8. Change PostgreSQL columns used in views
  9. Way to try multiple SELECTs till a result is available?
  10. PostgreSQL IF statement
  11. Split given string and prepare case statement
  12. Column ‘mary’ does not exist
  13. Get top row(s) with highest value, with ties
  14. Double colon `::` notation in SQL
  15. Delete parent if it’s not referenced by any other child
  16. PostgreSQL wildcard LIKE for any of a list of words
  17. Query that ignore the spaces
  18. SQL query to get most recent row for each instance of a given key
  19. Postgres constraint for unique datetime range
  20. Postgres function returning table not returning data in columns
  21. PostgreSQL create table if not exists
  22. Get values from first and last row per group
  23. How can I add a column that doesn’t allow nulls in a Postgresql database?
  24. Create PostgreSQL ROLE (user) if it doesn’t exist
  25. Run a query with a LIMIT/OFFSET and also get the total number of rows
  26. Return rows from INSERT with ON CONFLICT without needing to update
  27. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  28. Insert data and set foreign keys with Postgres
  29. Fetch records that are non zero after the decimal point in PostgreSQL
  30. How to use a ring data structure in window functions

Leave a Comment