Database Automation

Reveals blocked queries and the root blocking transaction PID during automated DDL operations or unindexed foreign key updates.

-- Find blocked processes, blocking PIDs, and executing SQL
SELECT 
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Monitors active table locks held by schema migration tools (gh-ost, pt-osc, pg_repack) and tracks average hold duration.

-- Monitor DDL migration locks and transaction duration
SELECT 
  l.locktype, 
  l.mode, 
  l.granted, 
  count(*) AS active_locks, 
  round(avg(EXTRACT(epoch FROM (clock_timestamp() - a.query_start))), 2) AS avg_sec
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE a.query ILIKE '%ALTER TABLE%' OR a.query ILIKE '%CREATE INDEX%'
GROUP BY l.locktype, l.mode, l.granted;