Queries

SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*application='PgHero'*/

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40)
 /*application='PgHero'*/

SELECT n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped AND a.attnum > $1 AND pg_get_expr(d.adbin, d.adrelid) LIKE $2 AND n.nspname NOT LIKE $3 /*application='PgHero'*/

SELECT n.nspname AS schema, c.relname AS relation, CASE c.relkind WHEN $1 THEN $2 WHEN $3 then $4 ELSE $5 END AS type, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ($6, $7) AND n.nspname !~ $8 AND c.relkind IN ($9, $10, $11) ORDER BY pg_table_size(c.oid) DESC, 2 ASC /*application='PgHero'*/

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, total_plan_time + total_exec_time AS total_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $2 AND pg_database.datname = current_database() ) ( SELECT query, query_hash, query_stats.user, total_time, calls FROM query_stats ORDER BY "total_time" DESC LIMIT $3 ) UNION ALL ( SELECT $4, $5, $6, SUM(total_time), $7 FROM query_stats ) /*application='PgHero'*/

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typelem IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64)
 /*application='PgHero'*/

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typtype IN ($1, $2, $3)
 /*application='PgHero'*/

SELECT n.nspname AS schema, c.relname AS table, $1 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_left FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind = $2 AND ($3 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < $4 ORDER BY 3, 1, 2 /*application='PgHero'*/

SELECT schemaname AS schema, relname AS table, indexrelname AS index, pg_relation_size(i.indexrelid) AS size_bytes, idx_scan as index_scans FROM pg_stat_user_indexes ui INNER JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan <= $1 AND pg_relation_size(i.indexrelid) >= $2 ORDER BY pg_relation_size(i.indexrelid) DESC, relname ASC /*application='PgHero'*/

SELECT pg_database_size(current_database()) /*application='PgHero'*/

SELECT t.oid, t.typname
FROM pg_type as t
WHERE t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
 /*application='PgHero'*/

SELECT pid, state, application_name AS source, age(NOW(), COALESCE(query_start, xact_start)) AS duration, wait_event IS NOT NULL AS waiting, query, COALESCE(query_start, xact_start) AS started_at, EXTRACT($1 FROM NOW() - COALESCE(query_start, xact_start)) * $2 AS duration_ms, usename AS user, backend_type FROM pg_stat_activity WHERE state <> $3 AND pid <> pg_backend_pid() AND datname = current_database() AND NOW() - COALESCE(query_start, xact_start) > interval $4 AND query <> $5 ORDER BY COALESCE(query_start, xact_start) DESC /*application='PgHero'*/

SELECT state, COUNT(*) AS connections FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC, 1 /*application='PgHero'*/

SELECT n.nspname AS schema, c.relname AS sequence, has_sequence_privilege(c.oid, $1) AND (c.relpersistence <> $2 OR NOT pg_is_in_recovery()) AS readable FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = $3 AND n.nspname NOT IN ($4, $5) /*application='PgHero'*/

SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3,$4,$5,$6,$7,$8,$9,$10) ORDER BY 1, 2 /*application='PgHero'*/

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, total_plan_time + total_exec_time AS total_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $2 AND pg_database.datname = current_database() ) ( SELECT query, query_hash, query_stats.user, total_time, calls FROM query_stats ORDER BY "calls" DESC LIMIT $3 ) UNION ALL ( SELECT $4, $5, $6, SUM(total_time), $7 FROM query_stats ) /*application='PgHero'*/

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, total_plan_time + total_exec_time AS total_time, (total_plan_time + total_exec_time) / calls AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $2 AND pg_database.datname = current_database() ) ( SELECT query, query_hash, query_stats.user, total_time, calls FROM query_stats ORDER BY "average_time" DESC LIMIT $3 ) UNION ALL ( SELECT $4, $5, $6, SUM(total_time), $7 FROM query_stats ) /*application='PgHero'*/

SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = ANY (current_schemas($1)) AND c.relname = $2 AND c.relkind IN ($3,$4) /*application='PgHero'*/

SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2,$3,$4,$5,$6,$7,$8,$9) ORDER BY 1, 2, 3 /*application='PgHero'*/

SELECT schemaname AS schema, relname AS table, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, n_dead_tup AS dead_rows, n_live_tup AS live_rows FROM pg_stat_user_tables ORDER BY 1, 2 /*application='PgHero'*/

SELECT nsp.nspname AS schema, rel.relname AS table, con.conname AS name, fnsp.nspname AS referenced_schema, frel.relname AS referenced_table FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid LEFT JOIN pg_catalog.pg_class frel ON frel.oid = con.confrelid LEFT JOIN pg_catalog.pg_namespace nsp ON nsp.oid = con.connamespace LEFT JOIN pg_catalog.pg_namespace fnsp ON fnsp.oid = frel.relnamespace WHERE con.convalidated = $1 /*application='PgHero'*/

SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2,$3,$4,$5,$6,$7,$8) ORDER BY 1, 2, 3 /*application='PgHero'*/

SELECT pg_stat_activity.pid, datname AS database, usename AS user, application_name AS source, client_addr AS ip, state, ssl FROM pg_stat_activity LEFT JOIN pg_stat_ssl ON pg_stat_activity.pid = pg_stat_ssl.pid ORDER BY pg_stat_activity.pid /*application='PgHero'*/

SET SESSION timezone TO 'UTC'

SELECT slot_name, database, active FROM pg_replication_slots /*application='PgHero'*/

SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3,$4,$5,$6,$7,$8,$9) ORDER BY 1, 2 /*application='PgHero'*/

SET client_min_messages TO 'warning' /*application='PgHero'*/

SELECT * FROM pg_stat_statements LIMIT $1 /*application='PgHero'*/

SELECT schemaname AS schema, tablename AS table, attname AS column, null_frac, n_distinct FROM pg_stats WHERE schemaname = $1 AND tablename IN ($2,$3,$4,$5) ORDER BY 1, 2, 3 /*application='PgHero'*/

SELECT pid, state, application_name AS source, age(NOW(), COALESCE(query_start, xact_start)) AS duration, wait_event IS NOT NULL AS waiting, query, COALESCE(query_start, xact_start) AS started_at, EXTRACT($1 FROM NOW() - COALESCE(query_start, xact_start)) * $2 AS duration_ms, usename AS user, backend_type FROM pg_stat_activity WHERE state <> $3 AND pid <> pg_backend_pid() AND datname = current_database() ORDER BY COALESCE(query_start, xact_start) DESC /*application='PgHero'*/

UPDATE auth_sessions SET revoked_at=$1::TIMESTAMP WITH TIME ZONE, revoke_reason=$2::VARCHAR, updated_at=now() WHERE auth_sessions.revoked_at IS NULL AND auth_sessions.expires_at < $3::TIMESTAMP WITH TIME ZONE

CREATE EXTENSION IF NOT EXISTS pg_stat_statements

SHOW search_path /*application='PgHero'*/

SELECT pid, phase FROM pg_stat_progress_vacuum WHERE datname = current_database() /*application='PgHero'*/

SET standard_conforming_strings = on /*application='PgHero'*/

SET intervalstyle = iso_8601 /*application='PgHero'*/

SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3,$4,$5,$6) ORDER BY 1, 2 /*application='PgHero'*/

select current_schema()

SHOW server_version_num /*application='PgHero'*/

SELECT
    typname AS name, oid, typarray AS array_oid,
    oid::regtype::text AS regtype, typdelim AS delimiter
FROM pg_type t
WHERE t.oid = to_regtype($1)
ORDER BY t.oid

SHOW max_connections /*application='PgHero'*/

SELECT pg_is_in_recovery() /*application='PgHero'*/

select pg_catalog.version()

BEGIN

SHOW max_wal_size /*application='PgHero'*/

SHOW huge_pages /*application='PgHero'*/

SHOW maintenance_work_mem /*application='PgHero'*/

SHOW wal_buffers /*application='PgHero'*/

SHOW work_mem /*application='PgHero'*/

SHOW checkpoint_completion_target /*application='PgHero'*/

SHOW effective_cache_size /*application='PgHero'*/

SHOW shared_buffers /*application='PgHero'*/

SET idle_in_transaction_session_timeout = 60000

SHOW jit /*application='PgHero'*/

SHOW random_page_cost /*application='PgHero'*/

SET statement_timeout = 30000

SHOW min_wal_size /*application='PgHero'*/

SHOW server_version /*application='PgHero'*/

SHOW default_statistics_target /*application='PgHero'*/

SHOW effective_io_concurrency /*application='PgHero'*/

SHOW wal_compression /*application='PgHero'*/

COMMIT

show transaction isolation level

show standard_conforming_strings

SAVEPOINT "_pg3_1"

RELEASE "_pg3_1"

ROLLBACK