🐘
世の中のPostgreSQLエンジニアのpsql設定
先日、この記事（ 世の中のエンジニアのalias設定 ）読んで、PostgreSQLエンジニアの私はbashだけでなくpsqlの設定はどうなっているんだと気になったので、GitHubのリポジトリを調査してみました。
psqlrc
という単語を含むファイルのURLを抽出
Search API を用いる。ポイントは以下
for
文で繰り返し
sleep
を挿入
extract_psqlrc.sh
#!/bin/bash
GITHUB_TOKEN=hoge #自身のGitHubのトークンを設定
# API call 0 to 1000 bytes
for size in `seq 0 50 950`;
for page in `seq 10`;
curl -ksS \
-H "Accept: application/vnd.github.v3+json" \
-H "Authorization: token ${GITHUB_TOKEN}" \
-o results/${size}-$((size+49))-${page}.json \
"https://api.github.com/search/code?q=filename:psqlrc+size:${size}..$((size+49))&per_page=100&page=${page}"
sleep 60
# API call over 1000 bytes
for page in `seq 10`;
curl -ksS \
-H "Accept: application/vnd.github.v3+json" \
-H "Authorization: token ${GITHUB_TOKEN}" \
-o results/1000-1049-${page}.json \
"https://api.github.com/search/code?q=filename:psqlrc+size:>1000&per_page=100&page=${page}"
sleep 60
# extract html_url
for size in `seq 0 50 1000`;
for page in `seq 10`;
cat results/${size}-$((size+49))-${page}.json | \
jq -r ".items[] | .html_url" | \
sed "s#/blob/#/raw/#g" >> results/url_list.txt
\set QUIETに書きましたが、psqlrcの初めに\set QUIET ONとして、psqlrcの最後に\set QUIET OFFとして、psqlrc内の処理を出力しないようにしているようです。
https://github.com/okbob/pspg
SELECT
statement,
COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement
HAVING
lower(split_part(statement, ' ', 1)) = '\setenv' AND
lower(split_part(statement, ' ', 2)) = 'editor'
ORDER BY count DESC
LIMIT 10;
statement | count
------------------------------------------+-------
\setenv EDITOR 'nvim' | 5
\setenv EDITOR 'vim' | 5
\setenv EDITOR '/usr/bin/vim' | 5
\setenv EDITOR '/usr/local/bin/vim' | 4
\setenv EDITOR vim | 3
\setenv EDITOR '/usr/bin/nvim' | 2
\setenv EDITOR emacs | 2
\setenv EDITOR nvim | 2
\setenv EDITOR '~/.nix-profile/bin/nvim' | 2
\setenv EDITOR /usr/bin/vim | 1
(10 rows)
環境変数EDITORやPSQL_EDITORを設定することでエディタにNeovimやVim、Emacsを使用しているようです。
psqlのメタコマンドかと思いきや、改行コードが引っかかっただけな気がします…。
SELECT
lower(split_part(statement, ' ', 1)) AS item1,
lower(split_part(statement, ' ', 2)) AS item2,
COUNT(statement) AS count
FROM psqlrc
GROUP BY item1, item2
HAVING lower(split_part(statement, ' ', 1)) = 'set'
ORDER BY count DESC
LIMIT 30;
item1 | item2 | count
-------+--------------------------------------------------------+-------
set | search_path | 47
set | intervalstyle | 41
set | timezone | 9
set | application_name | 7
set | client_min_messages | 6
set | bytea_output | 5
set | client_encoding | 4
set | search_path=public; | 1
set | session | 1
set | search_path=site_config_service,project_service,public | 1
set | work_mem | 1
set | timezone='us/eastern'; | 1
set | default_transaction_read_only | 1
set | search_path=dwh,mart,stage | 1
set | tcp_keepalives_idle | 1
set | maintenance_work_mem='1gb'; | 1
set | search_path=public,postgis | 1
set | work_mem='512mb'; | 1
set | enable_bitmapscan | 1
set | statement_timeout | 1
(20 rows)
SELECT
statement,
COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement
HAVING
lower(split_part(statement, ' ', 1)) = 'set' AND
lower(split_part(statement, ' ', 2)) = 'search_path'
ORDER BY count DESC
LIMIT 10;
statement | count
--------------------------------------------------------+-------
set search_path to pdfbox,mycore,public; | 3
set search_path to setcore,public; | 3
set search_path to curl7,mycore,public; | 3
set search_path to jsonorg,setcore,public; | 3
set search_path to mycore,setcore,public; | 3
set search_path to public,setcore,libxml2; | 3
SET search_path TO setcore,public; | 3
SET search_path TO expat2,mycore,setcore,public; | 3
set search_path to gnuzip,setcore,public; | 3
SET search_path TO arxiv,pdfbox,mycore,setcore,public; | 2
(10 rows)
SELECT
statement,
COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement
HAVING
lower(split_part(statement, ' ', 1)) = 'set' AND
lower(split_part(statement, ' ', 2)) = 'intervalstyle'
ORDER BY count DESC
LIMIT 10;
statement | count
------------------------------------------+-------
set intervalstyle to 'postgres_verbose'; | 39
SET intervalstyle to 'postgres_verbose'; | 1
set intervalstyle to 'postgres_verbose' | 1
(3 rows)
SET search_path TO hogeでスキーマ検索パスを独自のものに設定したり、SET intervalstyle TO 'postgres_verbose'で時間間隔の出力をpostgres_verbose書式の出力にしているようです。
https://www.postgresql.jp/document/current/html/ddl-schemas.html#DDL-SCHEMAS-PATH
https://www.postgresql.jp/document/current/html/datatype-datetime.html#DATATYPE-INTERVAL-OUTPUT