#!/bin/bash


#echo "# pg_stat_activity"
#echo "
#SELECT * FROM pg_stat_activity;
#" | sudo -u postgres psql

#echo "### Praxisnahe Varianten (das, was man wirklich braucht)"
#echo "# Übersicht wie in MySQL"
#echo "
#SELECT
#    pid,
#    usename,
#    datname,
#    state,
#    client_addr,
#    application_name,
#    backend_start,
#    query_start,
#    query
#FROM pg_stat_activity
#ORDER BY query_start DESC;
#" | sudo -u postgres psql

echo "# Nur aktive Queries (was läuft gerade)"
echo "
SELECT
    pid,
    usename,
    now() - query_start AS runtime,
    state,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY runtime DESC;
" | sudo -u postgres psql

echo "# Laufzeitkiller: die schlimmsten Queries"
echo "
SELECT
    pid,
    usename,
    now() - query_start AS runtime,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes'
ORDER BY runtime DESC;
" | sudo -u postgres psql

echo "# Wer blockiert wen?"
echo "
SELECT
    a.pid AS blocked_pid,
    a.query AS blocked_query,
    b.pid AS blocking_pid,
    b.query AS blocking_query
FROM pg_locks bl
JOIN pg_stat_activity a ON a.pid = bl.pid
JOIN pg_locks kl
  ON kl.locktype = bl.locktype
 AND kl.database IS NOT DISTINCT FROM bl.database
 AND kl.relation IS NOT DISTINCT FROM bl.relation
 AND kl.page IS NOT DISTINCT FROM bl.page
 AND kl.tuple IS NOT DISTINCT FROM bl.tuple
 AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid
 AND kl.classid IS NOT DISTINCT FROM bl.classid
 AND kl.objid IS NOT DISTINCT FROM bl.objid
 AND kl.objsubid IS NOT DISTINCT FROM bl.objsubid
 AND kl.pid != bl.pid
JOIN pg_stat_activity b ON b.pid = kl.pid
WHERE NOT bl.granted;
" | sudo -u postgres psql

#echo "### Sessions beenden (KILL QUERY / KILL CONNECTION)"
#echo "# Query abbrechen (sanft)"
#echo "
#SELECT pg_cancel_backend(pid)
#FROM pg_stat_activity
#WHERE pid = 12345;
#" | sudo -u postgres psql

#echo "# Session hart beenden"
#echo "
#SELECT pg_terminate_backend(pid)
#FROM pg_stat_activity
#WHERE pid = 12345;
#" | sudo -u postgres psql

echo "# Idle in transaction (sehr wichtig!)"
echo "
SELECT
    pid,
    usename,
    now() - query_start AS idle_time,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_time DESC;
" | sudo -u postgres psql