Postgres gists

gistfile1.txt

Postgress ---
---------
1. Terminate open connection except mine.

 SELECT pg_terminate_backend(pg_stat_activity.pid), pg_stat_activity.pid, pg_stat_activity.client_addr, pg_stat_activity.application_name, pg_stat_activity.usename
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid();


finding queires waiting on locks
---------------0

 SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED, a.application_name, a.client_addr,
         a.usename,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid, a.query
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid where a.usename = 'orion' and l.GRANTED = false  ORDER BY a.query_start;


Finding existing locks
---------------

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED, a.application_name, a.client_addr,
         a.usename,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid, a.query
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid where a.usename = 'orion' and l.mode <> 'AccessShareLock' and a.client_addr = '100.92.154.188'
ORDER BY a.query_start;