🐘

世の中のPostgreSQLエンジニアのpsql設定

2022/07/20に公開

PostgreSQL

Database

psql

tech

はじめに

先日、この記事（世の中のエンジニアのalias設定）読んで、PostgreSQLエンジニアの私はbashだけでなくpsqlの設定はどうなっているんだと気になったので、GitHubのリポジトリを調査してみました。

psqlrc

psqlとはPostgreSQLのターミナル型フロントエンドで、psqlからSQLを実行したりすることができます。そして、その設定ファイルがpsqlrcになり、以下の特徴があります。

システム全体の設定を行うpsqlrcファイルは、PostgreSQLの実行ファイルを含むディレクトリからの相対パスで../etc/に格納します。このディレクトリは環境変数PGSYSCONFDIRを使って明示的に設定することができます。
ユーザ固有の設定を行う.psqlrcファイルは、実行しているユーザのホームディレクトリに格納します。このファイルは環境変数PSQLRCで明示的に設定することができます。
これらのファイルは.psqlrc-14のようにバージョンを指定することで、特定のバージョンのpsql向けのファイルとすることができます。
詳しくはドキュメントやPostgreSQL Wikiを参考にしてください。

調査

調査方法

GitHub APIを用いてGitHub上のpsqlrcという単語を含むファイルのURLを抽出
ファイルの中身をPostgreSQLに挿入して、よく用いられる設定を解析

GitHub APIを用いてGitHub上の`psqlrc`という単語を含むファイルのURLを抽出

Search APIを用いる。ポイントは以下

GitHub APIは最大1,000件までしか結果を返してくれないのでファイルサイズで細かくスライスしてAPIコール
1ページ当たり100件の結果しか返してくれないので全ページを取得できるようにfor文で繰り返し
短時間にリクエストを送りすぎるとGitHubに怒られるので適度にsleepを挿入

extract_psqlrc.sh

#!/bin/bash

GITHUB_TOKEN=hoge #自身のGitHubのトークンを設定

# API call 0 to 1000 bytes
for size in `seq 0 50 950`;
do
  for page in `seq 10`;
  do
    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
  done
done

# API call over 1000 bytes
for page in `seq 10`;
do
  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
done

# extract html_url
for size in `seq 0 50 1000`;
do
  for page in `seq 10`;
  do
    cat results/${size}-$((size+49))-${page}.json | \
    jq -r ".items[] | .html_url" | \
    sed "s#/blob/#/raw/#g" >> results/url_list.txt
  done
done

ファイルの中身をPostgreSQLに挿入して、よく用いられる設定を解析

上の作業で得られた2,005個のファイルを解析していきます。せっかくなので、PostgreSQLに挿入して解析したいと思います。

テーブルへの挿入などは省略しますが、以下のようなテーブルにpsqlrcの中身を1行ずつ格納します。

CREATE TABLE psqlrc (
  id INT,
  line_num INT,
  statement TEXT,
  PRIMARY KEY (id, line_num)
);

SELECT * FROM psqlrc WHERE id = 1;
 id | line_num |          statement
----+----------+-----------------------------
  1 |        1 | \set QUIET 1
  1 |        2 | \x auto
  1 |        3 | \set VERBOSITY verbose
  1 |        4 | \set HISTCONTROL ignoredups
  1 |        5 | \unset QUIET
(5 rows)

以下のSQLを実行して、statementをスペースで区切ったときの第1項の出現回数のランキングを取ります。（コメント行を除外したり、SETとsetが別扱いされないために小文字に統一しています。）

SELECT
  lower(split_part(statement, ' ', 1)) AS command,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY command
HAVING left(lower(split_part(statement, ' ', 1)), 2) != '--'
ORDER BY count DESC
LIMIT 10;
  command  | count 
-----------+-------
 \set      |  8929
 \pset     |  2565
 \echo     |  1076
 \x        |  1018
 \timing   |   995
 \unset    |   565
 \setenv   |   273
 \r        |   207
 set       |   132
 \encoding |   123
(10 rows)

コマンドの書式と説明を以下にまとめました。

コマンド	書式	説明
`\set`	`\set [ name [ value [ ... ] ] ]`	psql変数`name`を`value`に設定する
`\pset`	`\pset [ option [ value ] ]`	問い合わせ結果のテーブル出力に影響するオプションを設定
`\echo`	`\echo text [ ... ]`	引数を空白で区切り、標準出力に出力し、改行
`\x`	`\x [ on \| off \| auto ]`	拡張テーブル形式モードを設定またはトグル
`\timing`	`\timing [ on \| off ]`	SQL文にかかる時間の表示の有無を`on`または`off`に設定（パラメータの指定がない場合、表示を`on`と`off`の間で切り替え）
`\unset`	`\unset name`	psql変数`name`を削除
`\setenv`	`\setenv name [ value ]`	環境変数`name`を`value`に設定
`\r`	`\r` (`\reset`)	問い合わせバッファをリセット（クリア）
`SET` (SQLコマンド)	省略	実行時パラメータを変更する
`\encoding`	`\encoding [ encoding ]`	クライアント側の文字セット符号化方式を設定（引数を指定しない場合、現在の符号化方式を表示）

それぞれ個別に見ていきましょう。

`\set`

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  | prompt1           |  1013
 \set  | quiet             |   919
 \set  | comp_keyword_case |   860
 \set  | histcontrol       |   823
 \set  | histfile          |   819
 \set  | prompt2           |   766
 \set  | verbosity         |   717
 \set  | histsize          |   261
 \set  | on_error_rollback |   254
 \set  | on_error_stop     |    85
 \set  | locks             |    84
 \set  | uptime            |    83
 \set  | clear             |    83
 \set  | settings          |    70
 \set  | dbsize            |    70
 \set  | activity          |    67
 \set  | tablesize         |    64
 \set  | conninfo          |    64
 \set  | waits             |    63
 \set  | sp                |    59
 \set  | uselesscol        |    59
 \set  | ll                |    57
 \set  | show_slow_queries |    54
 \set  | autocommit        |    46
 \set  | pager             |    43
 \set  | version           |    43
 \set  | echo_hidden       |    42
 \set  | menu              |    40
 \set  | tsize             |    35
 \set  | extensions        |    35
(30 rows)

`\set PROMPT1`, `\set PROMPT2`

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)) = 'prompt1'
ORDER BY count DESC
LIMIT 10;
                                statement                                | count 
-------------------------------------------------------------------------+-------
 \set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '                      |   258
 \set PROMPT1 '%[%033[1m%][%/] # '                                       |    53
 \set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%# ' |    51
 \set PROMPT1 '(%n@%M:%>) [%/] > '                                       |    30
 \set PROMPT1 '%[%033[1m%]%M/%/%R%[%033[0m%]%# '                         |    24
 \set PROMPT1 '%~%x%# '                                                  |    23
 \set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%R%#%x '              |    23
 \set PROMPT1 '%n@%M %~>'                                                |    18
 \set PROMPT1 '%M:%> %n@%/%R%#%x '                                       |    16
 \set PROMPT1 '%n@%M:%>%x %/# '                                          |    12
(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)) = 'prompt2'
ORDER BY count DESC
LIMIT 10;
                   statement                   | count 
-----------------------------------------------+-------
 \set PROMPT2 '[more] %R > '                   |   305
 \set PROMPT2 ''                               |    97
 \set PROMPT2 '... > '                         |    47
 \set PROMPT2 '... # '                         |    31
 \set PROMPT2 '%M %n@%/%R %# '                 |    24
 \set PROMPT2 '%R%x%# '                        |    15
 \set PROMPT2 '%R%# '                          |    12
 \set PROMPT2 '%[%033[1;33m%]%R%#%[%033[0m%] ' |    12
 \set PROMPT2 '> '                             |    12
 \set PROMPT2 '%R> '                           |     8
(10 rows)

PROMPT1、PROMPT2、PROMPT3はpsqlのプロンプトの設定をする変数で、

PROMPT1：psqlが新しいコマンドを受け付ける際に発行される通常のプロンプト
PROMPT2：コマンドがセミコロンで終わっていない、または、引用符が閉じていないなど、コマンドの入力中にさらなる入力が期待される際に発行されるプロンプト
PROMPT3：SQLのCOPY FROM STDINコマンドを実行中で、端末上で行の値の入力が必要な際に発行されるプロンプト

であり、PROMPT1とPROMPT2を設定している人が多いようです。

rockyユーザ（スーパーユーザ）、postgresデータベースにUnixドメインソケットで接続している場合のプロンプトは以下のようになります。

PROMPT1	表示内容
%/%R%x%# （デフォルト）	postgres=# （デフォルト）
%[%033[1m%]%M %n@%/%R%[%033[0m%]%#	[local] rocky@postgres=#
%[%033[1m%][%/] #	[postgres] #
%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%#	postgres=#
(%n@%M:%>) [%/] >	(rocky@[local]:5432) [postgres] >
%[%033[1m%]%M/%/%R%[%033[0m%]%#	[local]/postgres=#

PROMPT2	表示内容
%/%R%x%# （デフォルト）	postgres(# （デフォルト）
[more] %R >	[more] ( >
''	表示しない
... >	... >
... #	... #
%M %n@%/%R %#	[local] rocky@postgres( #

接続ユーザ、データベース、ポート、ホスト名などを表示する設定にしている人が多いようです。

`\set QUIET`

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)) = 'quiet'
ORDER BY count DESC
LIMIT 10;
      statement      | count 
---------------------+-------
 \set QUIET 1        |   641
 \set QUIET 0        |    78
 \set QUIET OFF      |    42
 \set QUIET ON       |    42
 \set QUIET on       |    37
 \set QUIET yes      |    27
 \set QUIET off      |    26
 \set QUIET          |    18
 \set QUIET 1\r      |     3
 \set QUIET :QUIETRC |     2
(10 rows)

変数QUIETはpsqlのオプション-q (--quiet)と同じ効力をもち、psqlがメッセージ出力なしで処理を行うように設定できます。

例えば、CREATE TABLEを実行するときのpsqlの出力を見てみると、psqlがCREATE TABLEというテキストを出力していますが、\set QUIET ONとすると、その出力がなくなることがわかります。

postgres=# CREATE TABLE t1 (i INT);
CREATE TABLE
postgres=# \set QUIET ON
postgres=# CREATE TABLE t2 (i INT);
postgres=#

ただ、そのような使い方をしているというよりも、psqlrc全体を見てみると、psqlrcの初めに\set QUIET ONとして、psqlrcの最後に\set QUIET OFFとして、psqlrc内の処理を出力しないようにしているようです。

`\set COMP_KEYWORD_CASE`

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)) = 'comp_keyword_case'
ORDER BY count DESC
LIMIT 10;
               statement               | count 
---------------------------------------+-------
 \set COMP_KEYWORD_CASE upper          |   818
 \set COMP_KEYWORD_CASE lower          |    26
 \set COMP_KEYWORD_CASE preserve-upper |     5
 \set COMP_KEYWORD_CASE preserve-lower |     4
 \set COMP_KEYWORD_CASE upper\r        |     4
 \set COMP_KEYWORD_CASE 'upper'        |     2
 \set comp_keyword_case lower          |     1
(7 rows)

変数COMP_KEYWORD_CASEはSQLのタブ補完を行うときに大文字を使うか小文字を使うかを設定することができます。

upper：大文字
lower：小文字
preserve-upper（デフォルト）：入力済みの文字の大文字小文字を引き継ぐが、何も入力されていない場合は大文字
preserve-lower：入力済みの文字の大文字小文字を引き継ぐが、何も入力されていない場合は小文字

常に大文字でSQLのタブ補完をしている人が多いようです。

`\set HISTCONTROL`, `\set HISTFILE`, `\set HISTSIZE`

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)) = 'histcontrol'
ORDER BY count DESC
LIMIT 10;
           statement           | count 
-------------------------------+-------
 \set HISTCONTROL ignoredups   |   748
 \set HISTCONTROL ignoreboth   |    60
 \set HISTCONTROL ignorespace  |    11
 \set HISTCONTROL ignoredups\r |     3
 \set HISTCONTROL none         |     1
(5 rows)

変数HISTCONTROLでコマンドの履歴リストの設定をすることができます。

ignorespace：空白文字から始まる行は履歴リストに入らない
ignoredups：直前の履歴と同じ行は履歴リストに入らない
ignoreboth：ignorespaceとignoredupsの両方
none（デフォルト）：全ての行が履歴リストに入る

\set HISTCONTROL ignoredupsで直前の履歴と同じ行は履歴リストに入れないように設定している人が多いようです。

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)) = 'histfile'
ORDER BY count DESC
LIMIT 10;
                                         statement                                          | count 
--------------------------------------------------------------------------------------------+-------
 \set HISTFILE ~/.psql_history- :DBNAME                                                     |   514
 \set HISTFILE `[[ -z $PSQL_HISTFILE ]] && echo $HOME/.psql_history || echo $PSQL_HISTFILE` |    46
 \set HISTFILE ~/.psql_history- :HOST - :DBNAME                                             |    39
 \set HISTFILE ~/.psql/history- :DBNAME                                                     |    26
 \set HISTFILE ~/psql_history- :DBNAME                                                      |    18
 \set HISTFILE ~/.psql_history-:DBNAME                                                      |    14
 \set HISTFILE ~/.history/psql- :HOST - :DBNAME                                             |    11
 \set HISTFILE <%= ENV['OPENSHIFT_DATA_DIR'] %>/.psql_history- :DBNAME                      |    10
 \set HISTFILE ~/.psql_history                                                              |     9
 \set HISTFILE ~/.psql_history/ :DBNAME                                                     |     8
(10 rows)

変数HISTFILEで履歴を保存するファイル名を設定することができます。

ファイル名にデータベース名やホスト名を設定している人が多いようです。

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)) = 'histsize'
ORDER BY count DESC
LIMIT 10;
       statement       | count 
-----------------------+-------
 \set HISTSIZE 2000    |   109
 \set HISTSIZE 10000   |    31
 \set HISTSIZE 100000  |    30
 \set HISTSIZE 5000    |    23
 \set HISTSIZE 20000   |    13
 \set HISTSIZE 1000000 |    10
 \set HISTSIZE 1000    |     5
 \set HISTSIZE 12000   |     4
 \set HISTSIZE -1      |     4
 \set HISTSIZE 6000    |     4
(10 rows)

変数HISTSIZEでコマンド履歴に保存するコマンドの最大数（デフォルト500）を設定することができます。

コマンド履歴に保存するコマンドの最大数を増やす設定にしている人が多いようです。

`\set VERBOSITY`

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)) = 'verbosity'
ORDER BY count DESC
LIMIT 10;
        statement         | count 
--------------------------+-------
 \set VERBOSITY verbose   |   691
 \set VERBOSITY terse     |    15
 \set VERBOSITY default   |     6
 \set VERBOSITY verbose\r |     3
 \set VERBOSITY 'terse'   |     1
 \set VERBOSITY verbose;  |     1
(6 rows)

変数VERBOSITYでエラー報告の冗長性を制御することができます。

\set VERBOSITY verboseでエラーの詳細を表示するようにしている人が多いようです。

`\set ON_ERROR_ROLLBACK`

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)) = 'on_error_rollback'
ORDER BY count DESC
LIMIT 10;
                                                        statement                                                         | count 
--------------------------------------------------------------------------------------------------------------------------+-------
 \set ON_ERROR_ROLLBACK interactive                                                                                       |   227
 \set ON_ERROR_ROLLBACK on                                                                                                |    14
 \set ON_ERROR_ROLLBACK                                                                                                   |     4
 \set ON_ERROR_ROLLBACK 1                                                                                                 |     2
 \set ON_ERROR_ROLLBACK off                                                                                               |     2
 \set ON_ERROR_ROLLBACK 'interactive'                                                                                     |     2
 \set ON_ERROR_ROLLBACK 'on'\r                                                                                            |     1
 \set ON_ERROR_ROLLBACK 'on'                                                                                              |     1
 \set ON_ERROR_ROLLBACK off|on|interactive -- interactive lets you fix your fat fingers... Prefer to redo the whole thing |     1
(9 rows)

変数ON_ERROR_ROLLBACKでトランザクションブロック内でのエラー時の挙動を変更することができます。

on：ある文がエラーとなった時に、そのエラーは無視され、トランザクションは継続するようになります
interactive：対話式セッション内の場合にのみエラーは無視されます
off（デフォルト）：エラーになると、トランザクション全体をアボートします

\set ON_ERROR_ROLLBACK interactiveで対話式セッション内のトランザクションブロック内エラーは無視するように設定しているようです。

`\set ON_ERROR_STOP`

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)) = 'on_error_stop'
ORDER BY count DESC
LIMIT 10;
        statement        | count 
-------------------------+-------
 \set ON_ERROR_STOP on   |    69
 \set ON_ERROR_STOP      |     8
 \set ON_ERROR_STOP 1    |     5
 \set ON_ERROR_STOP true |     2
 \set ON_ERROR_STOP off  |     1
(5 rows)

\set ON_ERROR_STOP onとすることで、エラー後のコマンド処理を即座に停止することができます。対話モードではpsqlはコマンドプロンプトに戻り、それ以外ではpsqlは終了しエラーコード3を返します。

`\pset`

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)) = '\pset'
ORDER BY count DESC
LIMIT 30;
 item1 |          item2           | count 
-------+--------------------------+-------
 \pset | null                     |  1201
 \pset | linestyle                |   374
 \pset | border                   |   313
 \pset | pager                    |   289
 \pset | format                   |   144
 \pset | unicode_header_linestyle |    64
 \pset | unicode_column_linestyle |    57
 \pset | unicode_border_linestyle |    57
 \pset | expanded                 |    22
 \pset | numericlocale            |     9
 \pset | columns                  |     8
 \pset | footer                   |     8
 \pset | fieldsep                 |     6
 \pset | tuples_only              |     5
 \pset | pager_min_lines          |     3
 \pset | title                    |     3
 \pset | recordsep                |     2
(17 rows)

`\pset null`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement           
HAVING
  lower(split_part(statement, ' ', 1)) = '\pset' AND
  lower(split_part(statement, ' ', 2)) = 'null'
ORDER BY count DESC
LIMIT 10;
      statement      | count 
---------------------+-------
 \pset null '[NULL]' |   416
 \pset null '¤'      |   142
 \pset null ¤        |   127
 \pset null '(null)' |    98
 \pset null 'NULL'   |    93
 \pset null '[null]' |    51
 \pset null [null]   |    31
 \pset null NULL     |    28
 \pset null ∅        |    26
 \pset null '∅'      |    20
(10 rows)

null値の代わりに表示する文字列を設定し、null値を見やすくしているようです。

`\pset linestyle`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement           
HAVING
  lower(split_part(statement, ' ', 1)) = '\pset' AND
  lower(split_part(statement, ' ', 2)) = 'linestyle'
ORDER BY count DESC
LIMIT 10;
         statement         | count 
---------------------------+-------
 \pset linestyle unicode   |   320
 \pset linestyle 'unicode' |    44
 \pset linestyle ascii     |     5
 \pset linestyle u         |     2
 \pset linestyle unicode\r |     2
 \pset linestyle 'ascii'   |     1
(6 rows)

境界線の表示形式をUnicodeにしているようです。（デフォルトはASCII）

境界線の表示形式をUnicodeにして上のSQLを実行すると以下のようになります。

         statement         │ count 
───────────────────────────┼───────
 \pset linestyle unicode   │   320
 \pset linestyle 'unicode' │    44
 \pset linestyle ascii     │     5
 \pset linestyle u         │     2
 \pset linestyle unicode\r │     2
 \pset linestyle 'ascii'   │     1
(6 rows)

`\pset border`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement           
HAVING
  lower(split_part(statement, ' ', 1)) = '\pset' AND
  lower(split_part(statement, ' ', 2)) = 'border'
ORDER BY count DESC
LIMIT 10;
                     statement                      | count 
----------------------------------------------------+-------
 \pset border 2                                     |   250
 \pset border 1                                     |    48
 \pset border 0                                     |     7
 \pset border 3                                     |     3
 \pset border 2\r                                   |     2
 \pset border 1 -- Makes the borders a little nicer |     1
 \pset border 4                                     |     1
 \pset border 7                                     |     1
(8 rows)

表示するテーブルが持つ境界線を変更しているようです。デフォルトは1です。

境界線を変更して上のSQLを実行すると以下のようになります。

border=0

                    statement                      count 
-------------------------------------------------- -----
\pset border 2                                       250
\pset border 1                                        48
\pset border 0                                         7
\pset border 3                                         3
\pset border 2\r                                       2
\pset border 1 -- Makes the borders a little nicer     1
\pset border 4                                         1
\pset border 7                                         1
(8 rows)

border=2

+----------------------------------------------------+-------+
|                     statement                      | count |
+----------------------------------------------------+-------+
| \pset border 2                                     |   250 |
| \pset border 1                                     |    48 |
| \pset border 0                                     |     7 |
| \pset border 3                                     |     3 |
| \pset border 2\r                                   |     2 |
| \pset border 1 -- Makes the borders a little nicer |     1 |
| \pset border 4                                     |     1 |
| \pset border 7                                     |     1 |
+----------------------------------------------------+-------+
(8 rows)

`\pset pager`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement           
HAVING
  lower(split_part(statement, ' ', 1)) = '\pset' AND
  lower(split_part(statement, ' ', 2)) = 'pager'
ORDER BY count DESC
LIMIT 10;
      statement       | count 
----------------------+-------
 \pset pager off      |   181
 \pset pager always   |    69
 \pset pager on       |    28
 \pset pager          |     4
 \pset pager on\r     |     2
 \pset pager auto     |     2
 \pset pager always\r |     1
 \pset pager 1        |     1
 \pset pager 0        |     1
(9 rows)

\pset pager offでページャをオフにしたり、\pset pager alwaysで常にページャを使用する設定にしているようです。デフォルトは\pset pager onです。

`\pset format`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement           
HAVING
  lower(split_part(statement, ' ', 1)) = '\pset' AND
  lower(split_part(statement, ' ', 2)) = 'format'
ORDER BY count DESC
LIMIT 10;
       statement        | count 
------------------------+-------
 \pset format wrapped   |   127
 \pset format aligned   |    12
 \pset format           |     2
 \pset format csv       |     2
 \pset format unaligned |     1
(5 rows)

\pset format wrappedで出力形式をwrapped書式にして、幅の広いデータ値を複数行に折り返して対象の列幅に合うように出力するようにしているようです。デフォルトは\pset format alignedです。

`\echo`

SELECT
  statement,
  COUNT(statement) AS count
FROM psqlrc
GROUP BY lower(split_part(statement, ' ', 1)), statement           
HAVING lower(split_part(statement, ' ', 1)) = '\echo'
ORDER BY count DESC
LIMIT 30;
                          statement                           | count 
--------------------------------------------------------------+-------
 \echo 'Administrative queries:\n'                            |    35
 \echo '\nCurrent Host Server Date Time : '`date` '\n'        |    33
 \echo                                                        |    29
 \echo 'Development queries:\n'                               |    28
 \echo '\t\t\t\\h\t\t-- Help with SQL commands'               |    20
 \echo '\t\t\t:uselesscol\t-- Useless columns'                |    20
 \echo '\t\t\t:activity\t-- Server activity'                  |    20
 \echo '\t\t\t:conninfo\t-- Server connections'               |    20
 \echo '\t\t\t:uptime\t\t-- Server uptime'                    |    20
 \echo 'Type :extensions to see the available extensions. \n' |    20
 \echo '\t\t\t:locks\t\t-- Lock info'                         |    20
 \echo '\t\t\t:clear\t\t-- Clear screen'                      |    19
 \echo '\t\t\t:tablesize\t-- Tables Size'                     |    19
 \echo 'Type :version to see the PostgreSQL version. \n'      |    19
 \echo '\t\t\t:menu\t\t-- Help Menu'                          |    19
 \echo 'Type \\q to exit. \n'                                 |    19
 \echo '\t\t\t\\?\t\t-- Help with psql commands\n'            |    19
 \echo '\t\t\t:sp\t\t-- Current Search Path'                  |    19
 \echo '\t\t\t:dbsize\t\t-- Database Size'                    |    19
 \echo '\t\t\t:ll\t\t-- List\n'                               |    19
 \echo '\t\t\t:settings\t-- Server Settings'                  |    19
 \echo '\t\t\t:waits\t\t-- Waiting queires'                   |    18
 \echo 'Welcome to PostgreSQL! \n'                            |    15
 \echo '\t:activity\t-- Server activity'                      |    11
 \echo '\t:dbsize\t\t-- Database Size'                        |    11
 \echo '\t:tablesize\t-- Tables Size'                         |    11
 \echo '\t:uptime\t\t-- Server uptime'                        |    11
 \echo '\t:conninfo\t-- Server connections'                   |    11
 \echo '\t:settings\t-- Server Settings'                      |    11
 \echo '\t\\?\t\t-- Help with psql commands\n'                |    10
(30 rows)

psql起動時に様々な説明文を表示しているようです。

`\x`

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)) = '\x'
ORDER BY count DESC
LIMIT 30;
 item1 | item2  | count 
-------+--------+-------
 \x    | auto   |   987
 \x    |        |    11
 \x    | off    |    10
 \x    | on     |     9
 \x    | auto\r |     1
(5 rows)

\x autoで行の幅によって自動的に拡張テーブル形式モードにするかどうかを設定しているようです。

`\timing`

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)) = '\timing'
ORDER BY count DESC
LIMIT 30;
  item1  | item2 | count 
---------+-------+-------
 \timing |       |   833
 \timing | on    |   159
 \timing | off   |     2
 \timing | on\r  |     1
(4 rows)

\timingまたは\timing onでSQL文の実行時間を表示しているようです。

`\unset`

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)) = '\unset'
ORDER BY count DESC
LIMIT 30;
 item1  |   item2    | count 
--------+------------+-------
 \unset | quiet      |   554
 \unset | timing     |     3
 \unset | quiet\r    |     3
 \unset | quietrc    |     2
 \unset | singlestep |     2
 \unset | quite      |     1
(6 rows)

\set QUIETに書きましたが、psqlrcの初めに\set QUIET ONとして、psqlrcの最後に\set QUIET OFFとして、psqlrc内の処理を出力しないようにしているようです。

`\setenv`

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)) = '\setenv'
ORDER BY count DESC
LIMIT 30;
  item1  |           item2            | count 
---------+----------------------------+-------
 \setenv | pager                      |   129
 \setenv | less                       |    89
 \setenv | editor                     |    41
 \setenv | psql_editor                |     9
 \setenv | psql_pager                 |     2
 \setenv | psql_editor_linenumber_arg |     2
 \setenv | echo                       |     1
(7 rows)

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)) = 'pager'
ORDER BY count DESC
LIMIT 10;
                               statement                               | count 
-----------------------------------------------------------------------+-------
 \setenv PAGER 'less -SX'                                              |    28
 \setenv PAGER less                                                    |    26
 \setenv PAGER pspg                                                    |    14
 \setenv PAGER 'less -S'                                               |    11
 \setenv PAGER 'pspg --no-mouse -bX --no-commandbar --no-topbar'       |     5
 \setenv PAGER 'less'                                                  |     4
 \setenv PAGER 'less -XS'                                              |     4
 \setenv PAGER 'pspg -FX -s 17 --no-mouse --no-bars --only-for-tables' |     2
 \setenv PAGER 'less -FXE'                                             |     2
 \setenv PAGER '/usr/bin/less'                                         |     2
(10 rows)

環境変数PAGERやPSQL_PAGERを設定することでページャにlessやpspgを使用しているようです。また、環境変数LESSを設定することでlessのオプションを設定できるようです。

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を使用しているようです。

`\r`

psqlのメタコマンドかと思いきや、改行コードが引っかかっただけな気がします…。

`SET`

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書式の出力にしているようです。

`\encoding`

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)) = '\encoding'
ORDER BY count DESC
LIMIT 30;
   item1   |   item2   | count 
-----------+-----------+-------
 \encoding | unicode   |   105
 \encoding | utf8      |     6
 \encoding | latin1    |     5
 \encoding | utf-8     |     2
 \encoding | sql_ascii |     2
 \encoding | 'utf8'    |     1
 \encoding | latin1\r  |     1
 \encoding |           |     1
(8 rows)

\encoding unicodeでエンコーディングをUnicodeに設定している人が多いようです。

まとめ

psqlrcの設定を一切していなかった自分としては、色々な設定を知るきっかけになってよかったです。
個人的には複数のPostgreSQLを起動したり、接続したりする機会が多いので、プロンプトは独自にカスタマイズしてみようと思いました。

この記事を参考にして快適なpsqlライフを送っていただけると幸いです。

はじめに

psqlrc

調査

調査方法

GitHub APIを用いてGitHub上のpsqlrcという単語を含むファイルのURLを抽出

ファイルの中身をPostgreSQLに挿入して、よく用いられる設定を解析

\set

\set PROMPT1, \set PROMPT2

\set QUIET

\set COMP_KEYWORD_CASE

\set HISTCONTROL, \set HISTFILE, \set HISTSIZE

\set VERBOSITY

\set ON_ERROR_ROLLBACK

\set ON_ERROR_STOP

\pset

\pset null

\pset linestyle

\pset border

\pset pager

\pset format

\echo

\x

\timing

\unset

\setenv

\r

SET

\encoding

まとめ

Discussion