47 lines
1.3 KiB
SQL
47 lines
1.3 KiB
SQL
WITH T AS
|
|
(SELECT db.datname dbname,
|
|
lower(replace(Q.mode, 'Lock', '')) AS MODE,
|
|
coalesce(T.qty, 0) val
|
|
FROM pg_database db
|
|
JOIN (
|
|
VALUES ('AccessShareLock') ,('RowShareLock') ,('RowExclusiveLock') ,('ShareUpdateExclusiveLock') ,('ShareLock') ,('ShareRowExclusiveLock') ,('ExclusiveLock') ,('AccessExclusiveLock')) Q(MODE) ON TRUE NATURAL
|
|
LEFT JOIN
|
|
(SELECT datname,
|
|
MODE,
|
|
count(MODE) qty
|
|
FROM pg_locks lc
|
|
RIGHT JOIN pg_database db ON db.oid = lc.database
|
|
GROUP BY 1, 2) T
|
|
WHERE NOT db.datistemplate
|
|
ORDER BY 1, 2)
|
|
SELECT json_object_agg(dbname, row_to_json(T2))
|
|
FROM
|
|
(SELECT dbname,
|
|
sum(val) AS total,
|
|
sum(CASE
|
|
WHEN MODE = 'accessexclusive' THEN val
|
|
END) AS accessexclusive,
|
|
sum(CASE
|
|
WHEN MODE = 'accessshare' THEN val
|
|
END) AS accessshare,
|
|
sum(CASE
|
|
WHEN MODE = 'exclusive' THEN val
|
|
END) AS EXCLUSIVE,
|
|
sum(CASE
|
|
WHEN MODE = 'rowexclusive' THEN val
|
|
END) AS rowexclusive,
|
|
sum(CASE
|
|
WHEN MODE = 'rowshare' THEN val
|
|
END) AS rowshare,
|
|
sum(CASE
|
|
WHEN MODE = 'share' THEN val
|
|
END) AS SHARE,
|
|
sum(CASE
|
|
WHEN MODE = 'sharerowexclusive' THEN val
|
|
END) AS sharerowexclusive,
|
|
sum(CASE
|
|
WHEN MODE = 'shareupdateexclusive' THEN val
|
|
END) AS shareupdateexclusive
|
|
FROM T
|
|
GROUP BY dbname) T2
|