WITH event_types(wait_event_type) AS(
VALUES
('lwlock')
, ('lock')
, ('bufferpin')
, ('client')
, ('extension')
, ('ipc')
, ('timeout')
, ('io')
)
, events(wait_event) AS(
VALUES
('walwritelock')
, ('wal_insert')
, ('buffer_content')
, ('buffer_io')
, ('lock_manager')
, ('relation')
, ('extend')
, ('page')
, ('tuple')
, ('transactionid')
, ('virtualxid')
, ('speculative token')
, ('object')
, ('userlock')
, ('advisory')
, ('clientread')
, ('datafileextend')
, ('datafileread')
, ('datafilewrite')
, ('slruread')
, ('slruwrite')
)
, states(state) AS(
VALUES
('running')
, ('maintenance')
, ('waiting')
, ('transaction')
, ('idle')
)
, stats AS(
SELECT
pid
, datname
, state
, lower(wait_event_type) wait_event_type
, lower(wait_event) wait_event
, query
FROM
pg_stat_activity
WHERE
pid <> pg_backend_pid()
)
, dbs AS(
SELECT
datname
FROM
pg_database db
WHERE
NOT db.datistemplate
)
SELECT
date_part('epoch', now())::integer ts
, coalesce(s.qty, 0) val
, dbs.datname dbname
, states.state
, true total
FROM
dbs
CROSS JOIN
states
NATURAL LEFT JOIN
(
SELECT
datname
, CASE
WHEN query ~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)' THEN
'maintenance'
WHEN wait_event IS NOT NULL AND
wait_event <> 'clientread' AND
state = 'active' THEN
'waiting'
WHEN state = 'active' THEN
'running'
WHEN state = 'idle' THEN
'idle'
WHEN state IN ('idle in transaction', 'idle in transaction (aborted)') THEN
'transaction'
WHEN state = 'fastpath function call' THEN
'fastpath'
ELSE
'disabled'
END state
, count(*) qty
FROM
stats
GROUP BY
1, 2
) s
UNION
SELECT
date_part('epoch', now())::integer ts
, coalesce(t.qty, 0) val
, dbs.datname dbname
, event_types.wait_event_type
, false total
FROM
dbs
CROSS JOIN
event_types
NATURAL LEFT JOIN
(
SELECT
datname
, wait_event_type
, count(*) qty
FROM
stats
WHERE
wait_event_type IS NOT NULL
GROUP BY
1, 2
) t
UNION
SELECT
date_part('epoch', now())::integer ts
, coalesce(e.qty, 0) val
, dbs.datname dbname
, events.wait_event
, false total
FROM
dbs
CROSS JOIN
events
NATURAL LEFT JOIN
(
SELECT
datname
, wait_event
, count(*) qty
FROM
stats
WHERE
wait_event IS NOT NULL
GROUP BY
1, 2
) e;