defmodule EctoPSQLExtras.Blocking do
@behaviour EctoPSQLExtras
def info do
%{
title: "Queries holding locks other queries are waiting to be released",
index: 24,
columns: [
%{name: :blocked_pid, type: :integer},
%{name: :blocking_statement, type: :string},
%{name: :blocking_duration, type: :interval},
%{name: :blocking_pid, type: :integer},
%{name: :blocked_statement, type: :string},
%{name: :blocked_duration, type: :interval}
]
}
end
def query(_args \\ []) do
"""
/* ECTO_PSQL_EXTRAS: Queries holding locks other queries are waiting to be released */
SELECT bl.pid AS blocked_pid,
ka.query AS blocking_statement,
now() - ka.query_start AS blocking_duration,
kl.pid AS blocking_pid,
a.query AS blocked_statement,
now() - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a
ON bl.pid = a.pid
JOIN pg_catalog.pg_locks kl
JOIN pg_catalog.pg_stat_activity ka
ON kl.pid = ka.pid
ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
WHERE NOT bl.granted;
"""
end
end