[psql] Update config

1 files changed, 61 insertions, 0 deletions

diff --git a/files/.psqlrc b/files/.psqlrc
index 92ce556..69304f7 100644
--- a/files/.psqlrc
+++ b/files/.psqlrc
@@ -3,6 +3,9 @@
 
 \set QUIET 1
 
+\set VERBOSITY verbose
+\set ON_ERROR_ROLLBACK interactive
+
 \pset null '[NULL]'
 \pset border 2
 \pset linestyle unicode
@@ -20,6 +23,64 @@
 \set HISTSIZE 20000
 \set HISTCONTROL ignoreboth
 
+\set COMP_KEYWORD_CASE upper
+
 \unset QUIET
 
+--
+-- Taken from: https://github.com/lbolla/dotfiles/blob/master/psql/.psqlrc
+--
+
+-- Handy queries
+-- Use like:
+-- > :locks
+-- Kill hanged queries with:
+-- select pg_terminate_backend(<blocked_pid>);
+
+-- \set locks 'SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocked_activity.query_start as blocked_statement_start, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;'
+-- \set locks 'SELECT pl.locktype, pl.mode, psa.pid, psa.datname, psa.usename, psa.application_name, psa.query_start, substring(psa.query from 1 for 100) as query FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid order by psa.query_start, psa.datname, psa.usename;'
+\set locks_by_database 'SELECT psa.datname, psa.usename, psa.application_name, count (*) FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid group by datname, usename, application_name order by datname, usename, application_name;'
+
+-- \set active_locks 'SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype, pg_locks.database, pg_locks.relation, pg_locks.page, pg_locks.tuple, pg_locks.virtualtransaction, pg_locks.pid, pg_locks.mode, pg_locks.granted FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid WHERE relname !~ \'^pg_\' and relname <> \'active_locks\'';
+
+-- Disk usage
+-- From https://wiki.postgresql.org/wiki/Disk_Usage
+
+\set largest_dbs 'SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner, CASE WHEN pg_catalog.has_database_privilege(d.datname, \'CONNECT\') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE \'No Access\' END AS Size FROM pg_catalog.pg_database d ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, \'CONNECT\') THEN pg_catalog.pg_database_size(d.datname) ELSE NULL END DESC LIMIT 20;'
+\set largest_relations 'SELECT nspname as "schema", relname as "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (\'pg_catalog\', \'information_schema\') AND C.relkind <> \'i\' AND nspname !~ \'^pg_toast\' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20;'
+\set largest_indexes 'SELECT nspname, c.relname AS ctablename, ipg.relname AS indexname, pg_size_pretty(pg_relation_size(c.oid)) AS "size", x.indnatts AS number_of_columns, case when indisunique then \'y\' else \'n\' end as unique, idx_scan as number_of_scans, idx_tup_read as tuples_read, idx_tup_fetch as tuples_fetched FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_namespace N ON (N.oid = c.relnamespace) JOIN pg_class ipg ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid WHERE N.nspname NOT IN (\'pg_catalog\', \'pg_toast\', \'information_schema\') ORDER BY pg_relation_size(c.oid) DESC LIMIT 20;'
+
+\set connections 'SELECT datname,usename,pid,client_addr,application_name,state,query_start,substring(query from 1 for 40) as query FROM pg_stat_activity order by query_start, state, datname, usename;'
+\set connections_by_database 'SELECT datname, usename, application_name, count(*) FROM pg_stat_activity group by datname, usename, application_name order by datname, usename, application_name, count(*);'
+\set connections_by_app 'SELECT application_name, count(*) FROM pg_stat_activity group by application_name order by application_name, count(*);'
+\set dangling_connections 'SELECT datname,usename,pid,client_addr,application_name,state,query_start,substring(query from 1 for 40) as query FROM pg_stat_activity where state != \'active\' and query != \'COMMIT\' and query != \'ROLLBACK\' and query != \'<insufficient privilege>\' order by datname, usename, query_start;'
+
+-- Note: multiline aliases
+-- \set some_alias `cat ~/.psqlrc-aliases/some_client/some_alias.sql
+
+-- See https://stackoverflow.com/questions/26489244/how-to-detect-query-which-holds-the-lock-in-postgres
+\set blocked_queries 'SELECT COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item, now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid, blockeda.query as blocked_query, blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid, blockinga.query as blocking_query, blockingl.mode as blocking_mode FROM pg_catalog.pg_locks blockedl JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid JOIN pg_catalog.pg_locks blockingl ON(( (blockingl.transactionid=blockedl.transactionid) OR (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)) AND blockedl.pid != blockingl.pid) JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid WHERE NOT blockedl.granted AND blockinga.datname = current_database();'
+\set blocking_queries 'select pid, usename, pg_blocking_pids(pid) as blocked_by, query as blocked_query from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;'
+\set long_running_queries 'SELECT pid, now() - pg_stat_activity.query_start AS duration, query AS query FROM pg_stat_activity WHERE pg_stat_activity.query <> ''''::text AND now() - pg_stat_activity.query_start > interval ''5 minutes'' ORDER BY now() - pg_stat_activity.query_start DESC;'
+
+--
+-- Taken from https://github.com/dlamotte/dotfiles/blob/master/psqlrc
+--
+
+\set uptime 'select now() - backend_start as uptime from pg_stat_activity where pid = pg_backend_pid();'
+\set settings 'select name, setting,unit,context from pg_settings;'
+\set uselesscol 'SELECT nspname, relname, attname, typname, (stanullfrac*100)::int AS null_percent, case when stadistinct >= 0 then stadistinct else abs(stadistinct)*reltuples end AS \"distinct\", case 1 when stakind1 then stavalues1 when stakind2 then stavalues2 end AS \"values\" FROM pg_class c JOIN pg_namespace ns ON (ns.oid=relnamespace) JOIN pg_attribute ON (c.oid=attrelid) JOIN pg_type t ON (t.oid=atttypid) JOIN pg_statistic ON (c.oid=starelid AND staattnum=attnum) WHERE nspname NOT LIKE E\'pg\\\\_%\' AND nspname != \'information_schema\' AND relkind=\'r\' AND NOT attisdropped AND attstattarget != 0 AND reltuples >= 100 AND stadistinct BETWEEN 0 AND 1 ORDER BY nspname, relname, attname;'
+
+\set index_size 'SELECT relname AS name, pg_size_pretty(sum(relpages*1024)) AS size FROM pg_class WHERE reltype=0 GROUP BY relname ORDER BY sum(relpages) DESC;'
+\set index_usage 'SELECT relname, CASE idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * idx_scan / (seq_scan + idx_scan))::text END percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY n_live_tup DESC;'
+\set index_usage_adv 'SELECT * FROM (SELECT stat.relname AS table, stai.indexrelname AS index, CASE stai.idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * stai.idx_scan / (stat.seq_scan + stai.idx_scan))::text || ''%'' END hit_rate, CASE stat.idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * stat.idx_scan / (stat.seq_scan + stat.idx_scan))::text || ''%'' END all_index_hit_rate, ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) FROM generate_subscripts(idx.indkey, 1) AS k ORDER BY k) AS cols, stat.n_live_tup rows_in_table FROM pg_stat_user_indexes AS stai JOIN pg_stat_user_tables AS stat ON stai.relid = stat.relid JOIN pg_index AS idx ON (idx.indexrelid = stai.indexrelid)) AS sub_inner ORDER BY rows_in_table DESC, hit_rate ASC;'
+\set index_unused 'SELECT schemaname || ''.'' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'
+
+\set locks 'select pg_stat_activity.pid, pg_class.relname, pg_locks.transactionid, pg_locks.granted, substr(pg_stat_activity.query,1,30) as query_snippet, age(now(),pg_stat_activity.query_start) as "age" from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_stat_activity.query <> ''<insufficient privilege>'' and pg_locks.pid=pg_stat_activity.pid and pg_locks.mode = ''ExclusiveLock'' order by query_start;'
+
+-- Load local config if exists
+\if `test -e "$HOME/.psqlrc.local" && echo 1 || echo 0`
+  \i `echo $HOME/`.psqlrc.local
+\endif
+
 -- vim: ft=sql :