\set QUIET ON

\set PROMPT1 '%[%033[0;31;31m%]%n@%`hostname`:%>/%/=%#%[%033[0m%] '
\set PROMPT2 '%[%033[0;31;31m%]%/%R%#%[%033[0m%] '

\set PAGER OFF
\set HISTSIZE 2000
\set COMP_KEYWORD_CASE upper
\timing
\encoding unicode
\pset null 'NULL'
\set QUIET OFF


-- Administration queries
\set menu '\\i ~/.psqlrc'

\set settings 'select name, setting,unit,context from pg_settings;'

\set locks   'with  t_wait as (select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted ), t_run as (select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted ), t_overlap as (select r.* from t_wait w join t_run r on (r.locktype is not distinct from w.locktype and r.database is not distinct from w.database and r.relation is not distinct from w.relation and r.page is not distinct from w.page and r.tuple is not distinct from w.tuple and r.virtualxid is not distinct from w.virtualxid and r.transactionid is not distinct from w.transactionid and r.classid is not distinct from w.classid and r.objid is not distinct from w.objid and r.objsubid is not distinct from w.objsubid and r.pid <> w.pid )), t_unionall as (select r.* from t_overlap r union all select w.* from t_wait w ) select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid, string_agg(\'Pid: \'||case when pid is null then \'NULL\' else pid::text end||chr(10)|| \'Lock_Granted: \'||case when granted is null then \'NULL\' else granted::text end||\' , Mode: \'||case when mode is null then \'NULL\' else mode::text end||\' , FastPath: \'||case when fastpath is null then \'NULL\' else fastpath::text end||\' , VirtualTransaction: \'||case when virtualtransaction is null then \'NULL\' else virtualtransaction::text end||\' , Session_State: \'||case when state is null then 'NULL' else state::text end||chr(10)||  \'Username: \'||case when usename is null then \'NULL\' else usename::text end||\' , Database: \'||case when datname is null then \'NULL\' else datname::text end||\' , Client_Addr: \'||case when client_addr is null then \'NULL\' else client_addr::text end||\' , Client_Port: \'||case when client_port is null then \'NULL\' else client_port::text end||\' , Application_Name: \'||case when application_name is null then \'NULL\' else application_name::text end||chr(10)||  \'Xact_Start: \'||case when xact_start is null then \'NULL\' else xact_start::text end||\' , Query_Start: \'||case when query_start is null then \'NULL\' else query_start::text end||\' , Xact_Elapse: \'||case when (now()-xact_start) is null then \'NULL\' else (now()-xact_start)::text end||\' , Query_Elapse: \'||case when (now()-query_start) is null then \'NULL\' else (now()-query_start)::text end||chr(10)|| \'SQL (Current SQL in Transaction): \'||chr(10)||  case when query is null then \'NULL\' else query::text end,chr(10)||\'--------\'||chr(10)  order by (case mode when \'INVALID\' then 0 when \'AccessShareLock\' then 1  when \'RowShareLock\' then 2 when \'RowExclusiveLock\' then 3  when \'ShareUpdateExclusiveLock\' then 4 when \'ShareLock\' then 5 when \'ShareRowExclusiveLock\' then 6 when \'ExclusiveLock\' then 7 when \'AccessExclusiveLock\' then 8  else 0 end  ) desc, (case when granted then 0 else 1 end) ) as lock_conflict  from t_unionall  group by  locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ; '

\set conninfo 'select usename, count(*) from pg_stat_activity group by usename;'

\set activity 'select datname, pid, usename, application_name,client_addr, client_hostname, client_port, query, state from pg_stat_activity;'

\set slowquery 'select pid,usename,application_name,  now() - query_start as query_time,query   from pg_stat_activity where state = \'active\'  and  query not like  \'create%\' and query not like \'CREATE%\' and query not like  \'vacuum%\' and query not like  \'VACUUM%\' and query not like  \'analyze%\' and query not like  \'ANALYZE%\' and query not like  \'pg_start_backup%\' and query not like  \'pg_stop_backup%\' and backend_type = \'client backend\' and (now() - query_start) > interval\'00:00:00.1\' order by query_time desc;'

\set killbytime 'select \'select pg_cancel_backend(pid),usename,application_name, now() - query_start as query_time,query from pg_stat_activity where state =\'\'active\'\'  and query not like \'\'create%\'\'  and query not like \'\'CREATE%\'\' and query not like  \'\'vacuum%\'\' and query not like  \'\'VACUUM%\'\' and query  not like   \'\'analyze%\'\'  and query not like \'\'ANALYZE%\'\'and query not like  \'\'pg_start_backup%\'\' and query not like  \'\'pg_stop_backup%\'\' and backend_type=\'\'client backend\'\'  and (now() - query_start) > interval\'\'00:00:ss.ms\'\'  ; \\watch INT ;   \' KILLSQL  ;'

\set killbyuser 'select \'select pg_cancel_backend(pid),usename,application_name, now() - query_start as query_time,query from pg_stat_activity where state =\'\'active\'\'  and query not like \'\'create%\'\'  and query not like \'\'CREATE%\'\' and query not like  \'\'vacuum%\'\' and query not like  \'\'VACUUM%\'\' and query  not like   \'\'analyze%\'\'  and query not like \'\'ANALYZE%\'\'and query not like  \'\'pg_start_backup%\'\' and query not like  \'\'pg_stop_backup%\'\' and backend_type=\'\'client backend\'\'  and usename=\'\'USERNAME \'\'   ; \\watch INT ;   \' KILLSQL  ;'

\set killbyapp 'select \'select pg_cancel_backend(pid),usename,application_name, now() - query_start as query_time,query from pg_stat_activity where state =\'\'active\'\'  and query not like \'\'create%\'\'  and query not like \'\'CREATE%\'\' and query not like  \'\'vacuum%\'\' and query not like  \'\'VACUUM%\'\' and query  not like   \'\'analyze%\'\'  and query not like \'\'ANALYZE%\'\'and query not like  \'\'pg_start_backup%\'\' and query not like  \'\'pg_stop_backup%\'\' and backend_type=\'\'client backend\'\'  and application_name=\'\'APPNAME\'\'  ; \\watch INT ;   \' KILLSQL  ;'

\set killbysql 'select \'select pg_cancel_backend(pid),usename,application_name, now() - query_start as query_time,query from pg_stat_activity where state =\'\'active\'\'  and query not like \'\'create%\'\'  and query not like \'\'CREATE%\'\' and query not like  \'\'vacuum%\'\' and query not like  \'\'VACUUM%\'\' and query  not like   \'\'analyze%\'\'  and query not like \'\'ANALYZE%\'\'and query not like  \'\'pg_start_backup%\'\' and query not like  \'\'pg_stop_backup%\'\' and backend_type=\'\'client backend\'\'  and query like  \'\'%SQL%\'\'  ; \\watch INT ;   \' KILLSQL  ;'

\set bigtable 'SELECT table_catalog,table_schema,table_name,pg_size_pretty(t_size) table_size, pg_size_pretty(i_size) indexes_size  , pg_size_pretty(tt_size) total_size FROM  (SELECT table_catalog,table_schema,table_name,pg_table_size(schema_table) AS t_size,pg_indexes_size(schema_table) AS i_size,pg_total_relation_size(schema_table) AS tt_size FROM (SELECT table_catalog,(\'"\' || table_schema || \'"."\' || table_name || \'"\') AS schema_table,table_schema,table_name FROM information_schema.tables ) AS all_tables ) AS pretty_sizes order by t_size desc limit 30;'

\set unusertable  'select pg_size_pretty(pg_relation_size(relid)),* from pg_stat_all_tables where  seq_scan=0 and idx_scan=0  and schemaname not in (\'pg_toast\',\'pg_catalog\',\'information_schema\') order by pg_relation_size(relid) desc limit 30; '

\set unuserindex 'select pg_size_pretty(pg_relation_size(indexrelid)),* from pg_stat_all_indexes where  (idx_scan=0 and  idx_tup_read=0 and  idx_tup_fetch=0) and schemaname not in (\'pg_toast\',\'pg_catalog\') order by pg_relation_size(indexrelid) desc limit 30;'

\set tablebloat 'SELECT  current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat, CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages, CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes, CASE WHEN relpages < otta THEN \'0 bytes\'::text ELSE pg_size_pretty((bs*(relpages-otta))::bigint) END AS wastedsize, iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta, ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat, CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages, CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes, CASE WHEN ipages < iotta THEN \'0 bytes\' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize, pg_size_pretty(CASE WHEN relpages < otta THEN  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END  ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint) ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END  END) AS totalwastedbytes FROM (SELECT nn.nspname AS schemaname,cc.relname AS tablename, COALESCE(cc.reltuples,0) AS reltuples,COALESCE(cc.relpages,0) AS relpages,COALESCE(bs,0) AS bs,COALESCE(CEIL((cc.reltuples*((datahdr+ma-(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,  COALESCE(c2.relname,\'?\') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta  FROM pg_class cc JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> \'information_schema\' LEFT JOIN (SELECT ma,bs,foo.nspname,foo.relname,(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM (SELECT ns.nspname, tbl.relname, hdr, ma, bs, SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth, MAX(coalesce(null_frac,0)) AS maxfracsum, hdr+(SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname ) AS nullhdr FROM pg_attribute att JOIN pg_class tbl ON att.attrelid = tbl.oid JOIN pg_namespace ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats s ON s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname,(SELECT (SELECT current_setting(\'block_size\')::numeric) AS bs,CASE WHEN SUBSTRING(SPLIT_PART(v, \' \', 2) FROM \'#"[0-9]+.[0-9]+#"%\' for \'#\') IN (\'8.0\',\'8.1\',\'8.2\') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ \'mingw32\' OR v ~ \'64-bit\' THEN 8 ELSE 4 END AS ma  FROM (SELECT version() AS v) AS foo  ) AS constants  WHERE att.attnum > 0 AND tbl.relkind=\'r\' GROUP BY 1,2,3,4,5  ) AS foo  ) AS rs   ON cc.relname = rs.relname AND nn.nspname = rs.nspname LEFT JOIN pg_index i ON indrelid = cc.oid  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid ) AS sml where (CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END) >= 10240000  order by tbloat desc,wastedbytes desc limit 30 ;'

\set indexbloat 'SELECT current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta, ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages, CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes, CASE WHEN relpages < otta THEN \'zero bytes\'::text  ELSE pg_size_pretty((bs*(relpages-otta))::bigint) END AS wastedsize, iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta, ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes, CASE WHEN ipages < iotta THEN \'0 bytes\' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize, pg_size_pretty(CASE WHEN relpages < otta THEN CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint) ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END END) AS totalwastedbytes FROM (SELECT nn.nspname AS schemaname,cc.relname AS tablename,COALESCE(cc.reltuples,0) AS reltuples,COALESCE(cc.relpages,0) AS relpages,COALESCE(bs,0) AS bs,COALESCE(CEIL((cc.reltuples*((datahdr+ma-(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta, COALESCE(c2.relname,\'?\') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta FROM pg_class cc JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> \'information_schema\' LEFT JOIN (SELECT ma,bs,foo.nspname,foo.relname, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM (SELECT ns.nspname, tbl.relname, hdr, ma, bs,SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth, MAX(coalesce(null_frac,0)) AS maxfracsum, hdr+( SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname ) AS nullhdr FROM pg_attribute att JOIN pg_class tbl ON att.attrelid = tbl.oid JOIN pg_namespace ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats s ON s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname,(SELECT (SELECT current_setting(\'block_size\')::numeric) AS bs, CASE WHEN SUBSTRING(SPLIT_PART(v, \' \', 2) FROM \'#"[0-9]+.[0-9]+#"%\' for \'#\') IN (\'8.0\',\'8.1\',\'8.2\') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ \'mingw32\' OR v ~ \'64-bit\' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo ) AS constants WHERE att.attnum > 0 AND tbl.relkind=\'r\' GROUP BY 1,2,3,4,5 ) AS foo ) AS rs ON cc.relname = rs.relname AND nn.nspname = rs.nspname LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid ) AS sml where (CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END) >= 1024 order by wastedibytes desc limit 30;'

\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;'

\set waits 'SELECT datname, usename, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by, query FROM pg_stat_activity WHERE wait_event IS NOT NULL;'

\set uptime 'select now() - pg_postmaster_start_time() AS uptime;'


-- Development queries:
\set sp 'SHOW search_path;'
\set clear '\\! clear;'
\set ll '\\! ls -lart;'