transactions

// The long transactions of a PostgreSQL cluster, by SSH and psql on its server: the age of the oldest open
// transaction, of the oldest session idle inside a transaction, of the longest query running, and of the
// oldest prepared transaction. A long transaction holds back the vacuum, and a forgotten prepared one too.
// psql connects over the local socket as the PostgreSQL role named like the SSH account (peer authentication,
// or a ~/.pgpass of that account): no password leaves banshee, none is written on a command line.
need('protocol:ssh');
$port = variable('postgresql:port', 'integer', array(
    'label'   => 'Port',
    'help'    => 'The port of the PostgreSQL cluster, which names its local socket: 5432, or 5433 for a second cluster on the same server.',
    'default' => 5432,
));
$database = variable('postgresql:database', 'text', array(
    'label'   => 'Database',
    'help'    => 'The database psql connects to: any one the role may connect to.',
    'default' => 'postgres',
));

output('transaction', 'number', array('label' => 'Oldest open transaction', 'unit' => 's'));
output('idle-in-transaction', 'number', array('label' => 'Oldest session idle in a transaction', 'unit' => 's'));
output('query', 'number', array('label' => 'Longest query running', 'unit' => 's'));
output('prepared', 'number', array('label' => 'Oldest prepared transaction', 'unit' => 's'));

watch.above('transaction', 'Long transaction', 'transaction', array('warning' => 3600, 'critical' => 14400));
watch.above('idle-in-transaction', 'Session idle in a transaction', 'idle-in-transaction', array('warning' => 300, 'critical' => 1800));
watch.above('query', 'Long query', 'query', array('warning' => 600, 'enabled' => false));
watch.above('prepared', 'Forgotten prepared transaction', 'prepared', array('warning' => 3600));

// One query through psql, which writes one line of JSON. LC_ALL=C keeps its messages in English. A cluster
// whose socket is absent, or that refuses the connection, is not running: critical.
function query($port, $database, $sql) {
    $read = ssh.run('LC_ALL=C psql -X -A -t -q -v ON_ERROR_STOP=1', arguments=array('-p', '' . $port, '-d', $database, '-c', $sql));
    if ($read['exit'] == 2 && list.count(text.match($read['errors'], '(?i)connection refused|no such file or directory')) > 0) {
        fail('critical', 'PostgreSQL is not running', $read['errors']);
    }
    if ($read['exit'] != 0) {
        fail('misconfigured', 'psql failed', $read['errors']);
    }
    return json.read(text.trim($read['output']));
}

// The client backends only, this session of psql left out; 0 when there is none. Reading the sessions of
// other roles needs pg_monitor.
$found = query($port, $database, "SELECT json_build_object(
    'transaction', COALESCE(EXTRACT(EPOCH FROM max(now() - xact_start)), 0),
    'idle_in_transaction', COALESCE(EXTRACT(EPOCH FROM max(now() - state_change) FILTER (WHERE state LIKE 'idle in transaction%')), 0),
    'query', COALESCE(EXTRACT(EPOCH FROM max(now() - query_start) FILTER (WHERE state = 'active')), 0),
    'prepared', (SELECT COALESCE(EXTRACT(EPOCH FROM max(now() - prepared)), 0) FROM pg_prepared_xacts))
FROM pg_stat_activity WHERE backend_type = 'client backend' AND pid <> pg_backend_pid()");

value('transaction', number.round($found['transaction']));
value('idle-in-transaction', number.round($found['idle_in_transaction']));
value('query', number.round($found['query']));
value('prepared', number.round($found['prepared']));