postgres-long-running-transactions

SELECT
  pid,
  datname,
  usename,
  application_name as appname,
  client_addr,
  state,
  now() - backend_start as conn_age,
  now() - xact_start as xact_age,
  now() - query_start as query_age,
  now() - state_change as last_activity_age,
  wait_event_type,
  wait_event,
  query
FROM
  pg_stat_activity
WHERE
  state <> 'idle'
  AND (now() - xact_start) > COALESCE($1::INTERVAL, interval '5 minutes')
  AND xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY
  xact_age DESC
LIMIT 
  COALESCE($2::int, 20);

tools:
  long_running_transactions:
    kind: postgres-long-running-transactions
    source: postgres-source
    description: "Identifies transactions open longer than a threshold and returns details including query text and durations."

{
  "pid": 12345,
  "datname": "my_database",
  "usename": "dbuser",
  "appname": "my_app",
  "client_addr": "10.0.0.5",
  "state": "idle in transaction",
  "conn_age": "00:12:34",
  "xact_age": "00:06:00",
  "query_age": "00:02:00",
  "last_activity_age": "00:01:30",
  "wait_event_type": null,
  "wait_event": null,
  "query": "UPDATE users SET last_seen = now() WHERE id = 42;"
}