얼마 전 pgcli의 불편한 동작을 개선했던 내용을 짧게 공유해보려고 한다. pgcli는 PostgreSQL 관리자가 많이 사용하고 있는 커맨드라인 오픈소스이다. 네임드 쿼리와 자동완성 기능이 편리해서 나름 인기있는 cli 도구이다.
이러한 pgcli에 named query quiet mode 기능을 추가했다. pgcli 사용자라면 네임드 쿼리를 유용하게 사용하고 있을텐데, 그 네임드 쿼리의 불편한 부분을 일부 개선했다.
기능을 설명하기에 앞서 먼저 네임드 쿼리(named query)가 무엇인지부터 살펴보도록 하겠다.
네임드 쿼리(named query)
pgcli의 네임드 쿼리는 psql의 매크로와 기능이 비슷하지만 이해하기 쉽게 직관적으로 매크로를 작성할 수 있는 기능이다. pgcli를 설치하면 $HOME/.config/pgcli 경로에 config 파일이 생성되는데, 이 파일에 네임드 쿼리를 작성하면 된다.
config 파일에 네임드 쿼리를 작성하려면 좌변에는 pgcli에서 사용할 단축어, 오른쪽에는 SQL문을 입력해주면 된다(psql과 동일하다).
psf = "SELECT pid, datname AS database_name, usename AS user_name, state, query, query_start, backend_start, client_addr, application_name FROM pg_stat_activity ORDER BY query_start DESC"
변수가 필요한 경우 $ 특수문자를 사용하면 된다.
gv = "SELECT name, setting FROM pg_settings WHERE name LIKE '%$1%'"
그리고 아래처럼 긴 SQL 문장도 홑따옴표만 맞추어 작성해주면 된다.
pf_autovacuum_progress_queue = '''with table_opts as (
select
pg_class.oid,
relname,
nspname,
array_to_string(reloptions, '') as relopts
from pg_class
join pg_namespace ns on relnamespace = ns.oid
), vacuum_settings as (
select
oid,
relname,
nspname,
case
when relopts like '%autovacuum_vacuum_threshold%' then regexp_replace(relopts, '.*autovacuum_vacuum_threshold=([0-9.]+).*', E'\\1')::int8
else current_setting('autovacuum_vacuum_threshold')::int8
end as autovacuum_vacuum_threshold,
case
when relopts like '%autovacuum_vacuum_scale_factor%' then regexp_replace(relopts, '.*autovacuum_vacuum_scale_factor=([0-9.]+).*', E'\\1')::numeric
else current_setting('autovacuum_vacuum_scale_factor')::numeric
end as autovacuum_vacuum_scale_factor,
case when relopts ~ 'autovacuum_enabled=(false|off)' then false else true end as autovacuum_enabled
from table_opts
), p as (
select *
from pg_stat_progress_vacuum
)
select
--vacuum_settings.oid,
coalesce(
coalesce(nullif(vacuum_settings.nspname, 'public') || '.', '') || vacuum_settings.relname, -- current DB
format('[something in "%I"]', p.datname)
) as table,
round((100 * psat.n_dead_tup::numeric / nullif(pg_class.reltuples, 0))::numeric, 2) as dead_tup_pct,
pg_class.reltuples::numeric,
psat.n_dead_tup,
'vt: ' || vacuum_settings.autovacuum_vacuum_threshold
|| ', vsf: ' || vacuum_settings.autovacuum_vacuum_scale_factor
|| case when not autovacuum_enabled then ', DISABLED' else ', enabled' end as "effective_settings",
case
when last_autovacuum > coalesce(last_vacuum, '0001-01-01') then left(last_autovacuum::text, 19) || ' (auto)'
when last_vacuum is not null then left(last_vacuum::text, 19) || ' (manual)'
else null
end as "last_vacuumed",
coalesce(p.phase, '~~~ in queue ~~~') as status,
p.pid as pid,
case
when a.query ~ '^autovacuum.*to prevent wraparound' then 'wraparound'
when a.query ~ '^vacuum' then 'user'
when a.pid is null then null
else 'regular'
end as mode,
case when a.pid is null then null else coalesce(wait_event_type ||'.'|| wait_event, 'f') end as waiting,
round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS scanned_pct,
round(100.0 * p.heap_blks_vacuumed / nullif(p.heap_blks_total, 0), 1) AS vacuumed_pct,
p.index_vacuum_count,
case
when psat.relid is not null and p.relid is not null then
(select count(*) from pg_index where indrelid = psat.relid)
else null
end as index_count
from pg_stat_all_tables psat
join pg_class on psat.relid = pg_class.oid
join vacuum_settings on pg_class.oid = vacuum_settings.oid
full outer join p on p.relid = psat.relid and p.datname = current_database()
left join pg_stat_activity a using (pid)
where
psat.relid is null
or autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psat.n_dead_tup'''
fx_definition = '''SELECT
n.nspname AS schema,
p.proname AS name,
pg_get_functiondef(p.oid) AS definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.prokind = 'p'
AND p.proname LIKE '%$1%''''
실제 사용할 때는 \n <단축어> 를 입력하여 실행한다. 단축어가 기억나지 않아도 자동 완성 기능을 제공하기 때문에 필요한 단축어를 선택하여 실행할 수 있다.
단축어가 실행하는 SQL 명령문을 확인하고 싶다면 \np <단축어> 를 실행하면 된다.
추가 기능(named query quiet mode)
pgcli의 한 가지 아쉬운 점은 네임드 쿼리를 실행할 때마다 수행된 쿼리문이 결과와 함께 출력된다는 점이었다. 특히 위에서 보여준 pf_autovacuum_progress_queue과 같은 네임드 쿼리는 재귀 쿼리로 작성되었기 때문에 쿼리문의 길이가 상당히 길다. 아래 그림에서 보이듯이 터미널이 쿼리문으로 가득차서 쿼리 실행 결과가 보이지 않는다.
그래서, pgcli에 메타 명령어(\nq)와 옵션(hide_named_query_text) 하나를 추가하여 해당 값에 따라 수행 쿼리가 출력되지 않도록 기능을 개선하였다.
기능 추가 전(\n 명령어)에는 수행 쿼리가 네임드 쿼리를 출력할 때마다 함께 출력되면서 불필요하게 터미널 공간을 채우는 문제가 있었지만,
기능 추가 후(\nq 명령어)에는 네임드 쿼리의 SQL 실행문이 더이상 출력되지 않도록 변경되었다.
메타 명령어인 \nq 명령어는 스위치 방식으로 동작하기 때문에 기본 값으로 되돌아가고 싶은 경우 다시 한 번 \nq 명령어를 실행하면 된다.
추가로, quiet mode는 접속한 세션에만 적용되기 때문에 연결을 종료하면 기본 비활성화(OFF)된다. 만일 영구 적용을 하고 싶다면 config 파일에서 hide_named_query_text 파라미터를 True로 설정하면 된다. 물론 \nq 명령을 실행하여 동적으로 비활성화/활성화 할 수도 있다.
# Hide the query text when executing named queries (\n <name>).
# Only the query results will be displayed.
# Can be toggled at runtime with \nq command.
hide_named_query_text = True
공유 끝.
Database Internals 
댓글 남기기