How to detect query which holds the lock in Postgres?

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;

From this excellent article on query locks in Postgres, one can get blocked query and blocker query and their information from the following query.

CREATE VIEW lock_monitor AS(
SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);

SELECT * from lock_monitor;

As the query is long but useful, the article author has created a view for it to simplify it's usage.

This modification of a_horse_with_no_name's answer will give you the last (or current, if it's still actively running) query of the blocking session in addition to just the blocked sessions:

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

This helps you understand which operations are interfering with each other (even if the block comes from a previous query), giving you insight what the impact of killing one session will be and to figure out how to prevent blocking in the future.

Postgres has a very rich system catalog exposed via SQL tables. PG's statistics collector is a subsystem that supports collection and reporting of information about server activity.

Now to figure out the blocking PIDs you can simply query pg_stat_activity.

select pg_blocking_pids(pid) as blocked_by
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

To, get the query corresponding to the blocking PID, you can self-join or use it as a where clause in a subquery.

SELECT query
FROM pg_stat_activity
WHERE pid IN (select unnest(pg_blocking_pids(pid)) as blocked_by from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0);

Note: Since pg_blocking_pids(pid) returns an Integer[], so you need to unnest it before you use it in a WHERE pid IN clause.

Hunting for slow queries can be tedious sometimes, so have patience. Happy hunting.

for postgresql versions earlier than postgresql 9.6 which does not have pg_blocking_pids function，you can use following query to find blocked query and blocking query.

SELECT w.query                          AS waiting_query,
       w.pid                            AS waiting_pid,
       w.usename                        AS waiting_user,
       now() - w.query_start            AS waiting_duration,
       l.query                          AS locking_query,
       l.pid                            AS locking_pid,
       l.usename                        AS locking_user,
       t.schemaname || '.' || t.relname AS tablename,
       now() - l.query_start            AS locking_duration
FROM pg_stat_activity w
         JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted
         JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted
         JOIN pg_stat_activity l ON l2.pid = l.pid
         JOIN pg_stat_user_tables t ON l1.relation = t.relid
WHERE w.waiting;