.psqlrc

\set QUIET on
\set ON_ERROR_ROLLBACK interactive
\set ON_ERROR_STOP on
\set VERBOSITY verbose

\set HISTCONTROL ignoredups
\set PROMPT1 '%[\033]0;psql %x%n@%m:%>:%/\007\033[46m%]%`date +%H:%M` %[\033[1m%]%n@%/ %[\033[31m%]%x%[\033[34m%]%R%[\033[0;46m%]%#%[\033[0m%] '
\set PROMPT2 '%[\033]0;psql %x%n@%m:%>:%/\007\033[46m%]%`date +%H:%M` %[\033[1m%]%n@%/ %[\033[31m%]%x%[\033[34m%]%R%[\033[0;46m%]%#%[\033[0m%] '

\setenv PAGER `if test -x /usr/bin/pspg; then echo pspg -b --less-status-bar --ignore-case; else echo less -S; fi`
\pset linestyle unicode
\pset null '∅'

\timing
\unset QUIET

\set e 'EXPLAIN (ANALYZE, BUFFERS)'
\set su 'SET ROLE postgres;'

\set config 'SELECT name, current_setting(name), CASE source WHEN $$configuration file$$ THEN regexp_replace(sourcefile, $$^/.*/$$, $$$$)||$$:$$||sourceline ELSE source END FROM pg_settings WHERE source <> $$default$$;'

\set act_91 'SELECT datname, procpid, usename, waiting AS wait, date_trunc($$second$$, age(clock_timestamp(), xact_start)) AS xact_age, date_trunc($$second$$, age(clock_timestamp(), query_start)) AS query_age, date_trunc($$second$$, age(clock_timestamp(), backend_start)) AS backend_age, host(client_addr) || $$:$$ || client_port AS client, CASE WHEN procpid = pg_backend_pid() THEN $$:act$$ ELSE current_query END FROM pg_stat_activity ORDER BY 1, 2;'
\set act_9 'SELECT datname, pid, usename, waiting AS wait, date_trunc($$second$$, age(clock_timestamp(), xact_start)) AS xact_age, date_trunc($$second$$, age(clock_timestamp(), query_start)) AS query_age, date_trunc($$second$$, age(clock_timestamp(), backend_start)) AS backend_age, host(client_addr) || $$:$$ || client_port AS client, state, CASE WHEN pid = pg_backend_pid() THEN $$:act$$ ELSE query END FROM pg_stat_activity ORDER BY CASE WHEN state = $$idle$$ THEN $$zzz$$ ELSE state END, 1, 2;'
\set act 'SELECT datname, pid, usename, application_name, host(client_addr) || $$:$$ || client_port AS client, date_trunc($$second$$, age(clock_timestamp(), backend_start)) AS backend_age, date_trunc($$second$$, age(clock_timestamp(), xact_start)) AS xact_age, date_trunc($$second$$, age(clock_timestamp(), query_start)) AS query_ag, date_trunc($$second$$, age(clock_timestamp(), state_change)) AS state_ch, state, wait_event_type || $$: $$ || wait_event AS wait_event, CASE WHEN pid = pg_backend_pid() THEN $$:act$$ ELSE query END, backend_xid, backend_xmin FROM pg_stat_activity ORDER BY CASE WHEN state = $$idle$$ THEN $$zzz$$ ELSE state END, 1, 2;'

\set user_count 'SELECT datname, usename, count(*), date_trunc($$second$$, min(backend_start)) AS oldest_backend FROM pg_stat_activity GROUP BY 1, 2 ORDER BY 1, 2;'

\set user_tables 'SELECT CASE WHEN schemaname = $$public$$ THEN $$$$ ELSE schemaname||$$.$$ END || relname AS relname, seq_scan AS seqscan, seq_tup_read AS seqread, idx_scan AS idxscan, idx_tup_fetch AS idxfetch, n_tup_ins AS ins, n_tup_upd AS upd, n_tup_del AS del, n_tup_hot_upd AS hotupd, n_live_tup AS live, n_dead_tup AS dead, date_trunc($$second$$, age(clock_timestamp(), last_vacuum)) AS vacuum, date_trunc($$second$$, age(clock_timestamp(), last_autovacuum)) AS autovacuum, date_trunc($$second$$, age(clock_timestamp(), last_analyze)) AS analyze, date_trunc($$second$$, age(clock_timestamp(), last_autoanalyze)) AS autoanalyze FROM pg_stat_user_tables ORDER BY CASE WHEN schemaname = $$public$$ THEN $$AAAA$$ ELSE schemaname||$$.$$ END || relname;'

\set user_tables92 'SELECT CASE WHEN schemaname = $$public$$ THEN $$$$ ELSE schemaname||$$.$$ END || relname AS relname, seq_scan AS seqscan, seq_tup_read AS seqread, idx_scan AS idxscan, idx_tup_fetch AS idxfetch, n_tup_ins AS ins, n_tup_upd AS upd, n_tup_del AS del, n_tup_hot_upd AS hotupd, n_live_tup AS live, n_dead_tup AS dead, date_trunc($$second$$, age(clock_timestamp(), last_vacuum)) AS vacuum, date_trunc($$second$$, age(clock_timestamp(), last_autovacuum)) AS autovacuum, date_trunc($$second$$, age(clock_timestamp(), last_analyze)) AS analyze, date_trunc($$second$$, age(clock_timestamp(), last_autoanalyze)) AS autoanalyze, vacuum_count AS vc, autovacuum_count AS avc, analyze_count AS ac, autoanalyze_count AS aac FROM pg_stat_user_tables ORDER BY CASE WHEN schemaname = $$public$$ THEN $$AAAA$$ ELSE schemaname||$$.$$ END || relname;'

\set user_indexes 'SELECT CASE WHEN schemaname = $$public$$ THEN $$$$ ELSE schemaname||$$.$$ END || s.relname AS relname, cr.relpages * current_setting($$block_size$$)::bigint AS relsize, indexrelname, ci.relpages * current_setting($$block_size$$)::bigint AS indexsize, idx_scan, idx_tup_read, idx_tup_fetch, CASE WHEN indisprimary THEN $$PK$$ WHEN indisunique THEN $$UNIQ$$ ELSE $$$$ END AS pk FROM pg_stat_user_indexes s JOIN pg_class cr ON (relid = cr.oid) JOIN pg_class ci ON (indexrelid = ci.oid) JOIN pg_index USING (indexrelid) ORDER BY CASE WHEN schemaname = $$public$$ THEN $$AAAA$$ ELSE schemaname||$$.$$ END || s.relname, indexrelname;'
\set unused_indexes 'SELECT CASE WHEN schemaname = $$public$$ THEN $$$$ ELSE schemaname||$$.$$ END || s.relname AS relname, cr.relpages * current_setting($$block_size$$)::bigint AS relsize, indexrelname, ci.relpages * current_setting($$block_size$$)::bigint AS indexsize, idx_scan, idx_tup_read, idx_tup_fetch, CASE WHEN indisprimary THEN $$PK$$ WHEN indisunique THEN $$UNIQ$$ ELSE $$$$ END AS pk FROM pg_stat_user_indexes s JOIN pg_class cr ON (relid = cr.oid) JOIN pg_class ci ON (indexrelid = ci.oid) JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND NOT indisunique ORDER BY CASE WHEN schemaname = $$public$$ THEN $$AAAA$$ ELSE schemaname||$$.$$ END || s.relname, indexrelname;'

\set tablesize 'SELECT CASE WHEN schemaname = $$public$$ THEN $$$$ ELSE schemaname||$$.$$ END || relname AS relname, pg_size_pretty (pg_relation_size(relid)) AS size, pg_relation_size(relid) AS size, pg_size_pretty (pg_total_relation_size(relid)) AS total, pg_total_relation_size(relid) AS total, pg_size_pretty (pg_total_relation_size(relid) - pg_relation_size(relid)) AS extra, pg_total_relation_size(relid) - pg_relation_size(relid) AS extra FROM pg_stat_user_tables ORDER BY CASE WHEN schemaname = $$public$$ THEN $$AAAA$$ ELSE schemaname||$$.$$ END || relname;'

\set indexsize 'SELECT CASE WHEN schemaname = $$public$$ THEN $$$$ ELSE schemaname||$$.$$ END || relname AS relname, indexrelname, pg_size_pretty (pg_relation_size(indexrelid)) AS size, pg_relation_size(indexrelid) AS size FROM pg_stat_user_indexes ORDER BY CASE WHEN schemaname = $$public$$ THEN $$AAAA$$ ELSE schemaname||$$.$$ END || relname, indexrelname;'

\set objsize 'SELECT nspname, (SELECT COUNT(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$r$$) AS "r#", (SELECT SUM(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$r$$) AS r, (SELECT COUNT(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$i$$) AS "i#", (SELECT SUM(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$i$$) AS i, (SELECT COUNT(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$S$$) AS "S#", (SELECT SUM(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$S$$) AS "S", (SELECT COUNT(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$v$$) AS "v#", (SELECT COUNT(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$c$$) AS "c#", (SELECT COUNT(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$t$$) AS "t#", (SELECT SUM(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$t$$) AS t, (SELECT COUNT(pg_relation_size(c.oid)) FROM pg_class c WHERE c.relnamespace = n.oid AND relkind = $$f$$) AS "f#" FROM pg_namespace n ORDER BY nspname;'

\set tableio 'SELECT schemaname, s.relname, relpages * current_setting($$block_size$$)::bigint AS relsize, heap_blks_read AS heapread, heap_blks_hit AS heaphit, CASE WHEN heap_blks_read >= heap_blks_hit AND heap_blks_hit > 0 THEN ROUND (heap_blks_read::numeric/heap_blks_hit, 1) || $$:1$$ WHEN heap_blks_hit > heap_blks_read AND heap_blks_read > 0 THEN $$1:$$ || ROUND (heap_blks_hit::numeric/heap_blks_read, 1) ELSE NULL END AS heapr, idx_blks_read AS idxread, idx_blks_hit AS idxhit, CASE WHEN idx_blks_read >= idx_blks_hit AND idx_blks_hit > 0 THEN ROUND (idx_blks_read::numeric/idx_blks_hit, 1) || $$:1$$ WHEN idx_blks_hit > idx_blks_read AND idx_blks_read > 0 THEN $$1:$$ || ROUND (idx_blks_hit::numeric/idx_blks_read, 1) ELSE NULL END AS idxr, toast_blks_read AS toastread, toast_blks_hit AS toasthit, CASE WHEN toast_blks_read >= toast_blks_hit AND toast_blks_hit > 0 THEN ROUND (toast_blks_read::numeric/toast_blks_hit, 1) || $$:1$$ WHEN toast_blks_hit > toast_blks_read AND toast_blks_read > 0 THEN $$1:$$ || ROUND (toast_blks_hit::numeric/toast_blks_read, 1) ELSE NULL END AS toastr, tidx_blks_read AS tidxread, tidx_blks_hit AS tidxhit, CASE WHEN tidx_blks_read >= tidx_blks_hit AND tidx_blks_hit > 0 THEN ROUND (tidx_blks_read::numeric/tidx_blks_hit, 1) || $$:1$$ WHEN tidx_blks_hit > tidx_blks_read AND tidx_blks_read > 0 THEN $$1:$$ || ROUND (tidx_blks_hit::numeric/tidx_blks_read, 1) ELSE NULL END AS tidxr FROM pg_statio_user_tables s JOIN pg_class c ON (relid = oid) ORDER BY schemaname, relname;'

\set indexio 'SELECT CASE WHEN schemaname = $$public$$ THEN $$$$ ELSE schemaname||$$.$$ END || i.relname AS relname, indexrelname, relpages * current_setting($$block_size$$)::bigint AS idxsize, idx_blks_read AS idxread, idx_blks_hit AS idxhit, CASE WHEN idx_blks_read >= idx_blks_hit AND idx_blks_hit > 0 THEN ROUND (idx_blks_read::numeric/idx_blks_hit, 1) || $$:1$$ WHEN idx_blks_hit > idx_blks_read AND idx_blks_read > 0 THEN $$1:$$ || ROUND (idx_blks_hit::numeric/idx_blks_read, 1) ELSE NULL END AS idxr FROM pg_statio_user_indexes i JOIN pg_class ON (indexrelid = oid) ORDER BY schemaname, i.relname, indexrelname;'

\set schemasize 'SELECT schemaname, pg_size_pretty (SUM (pg_relation_size(relid))::bigint) AS size, SUM (pg_relation_size(relid)) AS size, pg_size_pretty (SUM (pg_total_relation_size(relid))::bigint) AS total, SUM (pg_total_relation_size(relid)) AS total, pg_size_pretty (SUM (pg_total_relation_size(relid) - pg_relation_size(relid))::bigint) AS extra, SUM (pg_total_relation_size(relid) - pg_relation_size(relid)) AS extra FROM pg_stat_user_tables GROUP BY schemaname ORDER BY schemaname;'

\set datsize 'SELECT datname, pg_size_pretty (pg_database_size (datname)) AS size, pg_database_size (datname) AS size FROM pg_database WHERE datallowconn ORDER BY 1;'

\set waiting 'SELECT wait_stm.datname as wdb, array_agg(wait.relation::regclass) AS wtable, wait.pid AS wpid, wait_stm.usename AS wuser, array_agg(wait.locktype||$$/$$||wait.mode) AS wlocktype, wait_stm.current_query AS wquery, other.pid AS opid, other_stm.usename AS ouser, array_agg(other.locktype||$$/$$||other.mode) AS omode, array_agg(other.granted) AS ogranted, other_stm.current_query AS oquery FROM pg_catalog.pg_locks AS wait JOIN pg_catalog.pg_stat_activity AS wait_stm ON ( wait_stm.procpid = wait.pid) JOIN pg_catalog.pg_locks AS other ON ( ( wait."database" = other."database" AND wait.relation = other.relation) OR wait.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON ( other_stm.procpid = other.pid) WHERE NOT wait.granted AND wait.pid <> other.pid GROUP BY 1,3,4,6, 7,8,11 ORDER BY wpid, opid;'

\set waiting92 'SELECT wait_stm.datname as wdb, array_agg(wait.relation::regclass) AS wtable, wait.pid AS wpid, wait_stm.usename AS wuser, array_agg(wait.locktype||$$/$$||wait.mode) AS wlocktype, wait_stm.query AS wquery, other.pid AS opid, other_stm.usename AS ouser, array_agg(other.locktype||$$/$$||other.mode) AS omode, array_agg(other.granted) AS ogranted, other_stm.query AS oquery FROM pg_catalog.pg_locks AS wait JOIN pg_catalog.pg_stat_activity AS wait_stm ON ( wait_stm.pid = wait.pid) JOIN pg_catalog.pg_locks AS other ON ( ( wait."database" = other."database" AND wait.relation = other.relation) OR wait.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON ( other_stm.pid = other.pid) WHERE NOT wait.granted AND wait.pid <> other.pid GROUP BY 1,3,4,6, 7,8,11 ORDER BY wpid, opid;'

-- this intentionally does not check indexprs (covered via indkey = '0' and indexprs isn't a canonical representation anyway)
\set index_duplicates 'SELECT i.indrelid::regclass, pg_get_indexdef(i.indexrelid), pg_get_indexdef(j.indexrelid) FROM pg_index i JOIN pg_index j ON i.indrelid = j.indrelid AND (i.indkey = j.indkey) WHERE i.indexrelid < j.indexrelid;'

\set missing_fk_indexes 'select conrelid::regclass AS relname, conname, pg_get_constraintdef(oid, true) from pg_constraint where contype=$$f$$ and not exists(select * from pg_index, generate_subscripts(conkey,1) as i where indrelid=conrelid and cast(indkey as smallint[]) @> conkey and indkey[0] = conkey[i] and (select opcfamily from pg_opclass where oid=indclass[0]) in (select amopfamily from pg_amop where amopopr=conpfeqop[i])) order by conrelid::regclass::text, conname;'

\set unowned_sequences 'select relname, (select adsrc from pg_depend d join pg_attrdef a on (d.objid = a.oid) where d.classid = $$pg_attrdef$$::regclass and d.refobjid = c.oid and d.refclassid = $$pg_class$$::regclass) from pg_class c where relkind = $$S$$ and not exists (select * from pg_depend d where d.classid = $$pg_class$$::regclass and d.objid = c.oid and refclassid = $$pg_class$$::regclass) order by 1;'

\set statements 'SELECT rolname AS user, datname AS database, calls, total_time, ROUND (total_time::numeric / calls, 3) AS avg_time, rows, query FROM pg_stat_statements s JOIN pg_authid a ON (s.userid = a.oid) JOIN pg_database d ON (s.dbid = d.oid) ORDER BY s.total_time DESC;'

\set extension_upgrades 'select extnamespace::regnamespace schema, extname, extversion, case when extversion = default_version then default_version else default_version || $$ #####$$ end default_version from pg_extension join pg_available_extensions() on (extname = name);'

\set orphaned_files_no_lateral 'WITH files AS (SELECT $$base/$$||(SELECT oid::text FROM pg_database WHERE datname = current_database())||$$/$$||pg_ls_dir AS file FROM pg_ls_dir($$base/$$||(SELECT oid::text FROM pg_database WHERE datname = current_database()))), orphans AS (SELECT * FROM files WHERE file !~ $$PG_VERSION|pg_filenode.map|pg_internal.init$$ AND regexp_replace(file, $$[._].*$$, $$$$) NOT IN (SELECT pg_relation_filepath(oid) FROM pg_class WHERE pg_relation_filepath(oid) IS NOT NULL)) SELECT file, pg_stat_file(file) FROM orphans ORDER BY file;'
\set orphaned_files 'WITH files AS (SELECT $$base/$$||(SELECT oid::text FROM pg_database WHERE datname = current_database())||$$/$$||pg_ls_dir AS file FROM pg_ls_dir($$base/$$||(SELECT oid::text FROM pg_database WHERE datname = current_database()))), orphans AS (SELECT * FROM files WHERE file !~ $$PG_VERSION|pg_filenode.map|pg_internal.init$$ AND regexp_replace(file, $$[._].*$$, $$$$) NOT IN (SELECT pg_relation_filepath(oid) FROM pg_class WHERE pg_relation_filepath(oid) IS NOT NULL)) SELECT * FROM orphans, pg_stat_file(orphans.file) ORDER BY file;'

\set create_function_bargraph 'CREATE OR REPLACE FUNCTION bargraph(len numeric) RETURNS text LANGUAGE sql IMMUTABLE AS $function$SELECT repeat($$█$$, floor(len)::int) || ($${"",▏,▎,▍,▌,▋,▊,▉,█}$$::text[])[1+round((len%1)*8)]$function$;'
\set create_function_exec 'CREATE FUNCTION exec(text) RETURNS text LANGUAGE plpgsql VOLATILE AS $f$BEGIN EXECUTE $1; RETURN $1; END;$f$;'

\set pid 'SELECT pg_backend_pid();'

\set reload_conf 'SELECT pg_reload_conf();'

\set KILL_ALL_USERS 'SELECT usename, pid, pg_terminate_backend(pid) FROM pg_stat_activity JOIN pg_authid a ON (usesysid = a.oid) WHERE NOT rolsuper'

\set DROP_ALL_TABLES 'DO $_$DECLARE n name; err text; BEGIN FOR n IN SELECT quote_ident(schemaname)||$$.$$||quote_ident(relname) FROM pg_stat_user_tables LOOP BEGIN EXECUTE $$DROP TABLE $$||n; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS err = MESSAGE_TEXT; RAISE NOTICE $$DROP TABLE %: %$$, n, err; END; END LOOP; END;$_$'
\set TRUNCATE_ALL_TABLES 'DO $_$DECLARE n name; err text; BEGIN FOR n IN SELECT quote_ident(schemaname)||$$.$$||quote_ident(relname) FROM pg_stat_user_tables LOOP BEGIN EXECUTE $$TRUNCATE $$||n; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS err = MESSAGE_TEXT; RAISE NOTICE $$TRUNCATE %: %$$, n, err; END; END LOOP; END;$_$'

\set DROP_ALL_SCHEMAS 'DO $_$DECLARE n name; err text; BEGIN FOR n IN SELECT quote_ident(nspname) FROM pg_namespace WHERE nspname !~ $$^(pg_|information_schema|public)$$ LOOP BEGIN EXECUTE $$DROP SCHEMA $$||n; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS err = MESSAGE_TEXT; RAISE NOTICE $$DROP SCHEMA %: %$$, n, err; END; END LOOP; END;$_$'

\set DROP_ALL_USERS 'DO $_$DECLARE n name; err text; BEGIN FOR n IN SELECT quote_ident(rolname) FROM pg_authid WHERE NOT rolsuper LOOP BEGIN EXECUTE $$DROP ROLE $$||n; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS err = MESSAGE_TEXT; RAISE NOTICE $$DROP ROLE %: %$$, n, err; END; END LOOP; END;$_$'

--\i ~/credativ/postgres/tools/statsmon/psqlrc_statsmon

\set q '\\quit'