PostgreSQL 15がやってくる(3) psqlの改善

はじめに

にゃーん。
今回は、みんな大好きpsqlのPostgreSQL 15改善項目を調べてみた。
注：まだbeat1時点での調査内容なので、今後変更される可能性はあり。

改善項目概要

PostgreSQL 15でも色々とpsqlへの改善項目が入っている。

• psqlの \copyコマンドの性能向上
• psqlコマンド \getenv の追加
• psqlの\dl/, lo_listの'+'オプションによるオブジェクト権限表示の追加
• \dconfig コマンドの追加
• \weatch コマンドがページャに対応
• クエリ内のダブルハイフンのコメントをサーバに送信するようになった
• ダブルハイフンのコメントマーカーを挿入するようにpsqlのreadline機能を改善
• 複数問い合わせ結果をpsqlが全て表示するようになった
• タブ補完を改良
• psqlのサポートをPostgreSQL 9.2以降が動作するサーバに限定
• psql の \password コマンドを改善する（リリースノート未記載）
• psqlの絵文字の幅の計算の問題を改善（リリースノート未記載）

改善項目の詳細

以下、改善項目の詳細を見ていく。
PostgreSQL 14と比較したほうがわかりやすいものは、両方のバージョンでの挙動を記載している。

psqlの \copyコマンドの性能向上

psqlにはcopyというメタコマンドが用意されている。
これは名前のとおり、一括ロードを行うCOPYコマンドをクライアント(psql)から認識できる場所に置いたファイル/プログラムから読み取り可能にするものである。

どのくらい\copyコマンドによる一括ロードやダンプが高速になったのか、手元の環境で比較してみた。

測定用のテーブル

以下のようにUNLOGGED TABLE（WAL書き込み影響を排除したかったので）を定義する。

CREATE UNLOGGED TABLE test (id int, num_data numeric, txt_data text);

このテーブルに以下のようにテキトーな値を1000000件挿入する。

INSERT INTO test VALUES (generate_series(1, 1000000), random(), md5(CLOCK_TIMESTAMP()::text));

ロードしたテーブルをCOPY TOコマンドでテキトーな場所にダンプしておく。

測定

で、このダンプしたファイルを、

• psqlの\copy
• SQLコマンドのCOPY
  を使って、TRUNCATEして空にしたテーブルにロードしてみる。ロード後にはまたTRUNCATEして空にしておく。
  ロード処理の時間はpsqlの\timingをONにしておいて取得する。
  これを3回測定して平均値をとってみる。

結果

たしかに\psqlの処理時間をみると有意にPostgreSQL 15beta1のほうが早くなっている。
なお、SQLコマンドCOPYについても、PostgreSQL 15beta1では少し早くなっているが、その差分は小さい。
この結果からも、\copy自体の性能が向上していると判断できる。

参考情報

この改善は、Commitfest 2021-07でコミットされた。
In psql \copy from, send data to server in larger chunks

psqlコマンド \getenv の追加

psqlを実行する環境の環境変数をpsql変数に設定するpsqlコマンド\getenvが追加された。
PostgreSQL 15のpsqlを起動してヘルプ\?を表示すると以下のようなコマンドがヘルプに表示されるようになる。

test-# \?
General
  \copyright             show PostgreSQL usage and distribution terms
（中略）
Operating System
  \cd [DIR]              change the current working directory
  \getenv PSQLVAR ENVVAR fetch environment variable
  \setenv NAME [VALUE]   set or unset environment variable
  \timing [on|off]       toggle timing of commands (currently off)
  \! [COMMAND]           execute command in shell or start interactive shell

\getenvコマンドは2つの引数をもつ。

• 最初の引数PSQLVARは任意のpsql変数を指定する。
• 2番目の引数ENVVARにはpsqlを起動した環境に設定されている環境変数（実装は見ていないけどgetenvシステムコールで取得できるもの？）を指定する。

例えばbash上に以下のような環境変数が設定されているとする。

$ export VOICE="にゃーん"
$ echo $VOICE
にゃーん
$

この環境上でpsqlを起動して、以下のように\getenvコマンドでpsql変数voiceに環境変数の値を設定してみる。
設定したpsql変数は、クエリ内でも使うことができる。

test=# \getenv voice VOICE
test=# SELECT :'voice';
 ?column?
----------
 にゃーん
(1 row)

test=#

もうちょい実用的な使い方としては、環境変数にテーブル名をセットしてから、\getenvを使ったスクリプトを実行するとか、条件分岐(\if等)で評価する変数を環境変数から取得するようにして、環境変数設定値によって動作を変えるとか、そんな用途に使えるのかもしれない。

参考情報

この改善は、Commitfestリストからは見つからなかった。
MLを探すと、pgsql-commitersのこのメールが該当するものっぽい。
pgsql: Add a \getenv command to psql.

psqlの\dl, \lo_listの'+'オプションによるオブジェクト権限表示の追加

\dl,lo_listはラージオブジェクトの一覧を表示するpsqlコマンドである。
PostgreSQL 15では、+オプションを追加することで、各ラージオブジェクトへのアクセス権限(SELECT/UPDATE)をもつユーザの情報を表示可能になった。

PostgreSQL 15

postgres=# \dl
         Large objects
  ID   |  Owner   | Description
-------+----------+-------------
 10001 | postgres |
(1 row)

postgres=# \dl+
                     Large objects
  ID   |  Owner   |  Access privileges   | Description
-------+----------+----------------------+-------------
 10001 | postgres | postgres=rw/postgres+|
       |          | user_a=w/postgres   +|
       |          | user_b=r/postgres    |
(1 row)

postgres=# \lo_list
         Large objects
  ID   |  Owner   | Description
-------+----------+-------------
 10001 | postgres |
(1 row)

postgres=# \lo_list+
                     Large objects
  ID   |  Owner   |  Access privileges   | Description
-------+----------+----------------------+-------------
 10001 | postgres | postgres=rw/postgres+|
       |          | user_a=w/postgres   +|
       |          | user_b=r/postgres    |
(1 row)

postgres=#

なお、PostgreSQL 14のpsqlでも\dl+コマンドは受け付けるが、表示される情報は\dlと同じものになる。

参考情報

この改善は、Commitfest 2021-03でコミットされた。
psql: \dl+ to list large objects privileges

\dconfig コマンドの追加

個人的に今回の改善項目で一番気に入っているもの。
\dconfigコマンドはサーバ変数を表示するpsqlのコマンド。
サーバ変数名と設定値を表示する機能として、古くからPostgreSQLコマンドSHOWがあるが、SHOWと\dconfigの大きな違いは、\dconfigはパターンマッチをしてくれることだ。

たとえば、max_parallel_workers_per_gatherというパラメータ（長い＆覚えにくい）を表示したい場合、以下のようなパターンマッチ文字で引くことができる。

test=# \dconfig max*para*gather
    List of configuration parameters
            Parameter            | Value
---------------------------------+-------
 max_parallel_workers_per_gather | 2
(1 row)

test=#

なお、SHOWコマンドでは、上記のようなパターンマッチは使えない。にゃーん。

test=# SHOW max*para*gather;
ERROR:  syntax error at or near "*"
LINE 1: SHOW max*para*gather;
                ^

パラメータ名をきちんと覚えられない自分にとっては実にありがたい。SHOWで個別のパラメータ値を表示するときに、長いパラメータ名を入れるの面倒だし。
ただ、これによってますますパラメータ名がうろ覚えになってしまいそう・・・ｗ

ただ個人的にちょい謎だと思ったのは、なんでこのpsqlコマンド名は\showではなく\dconfigという名前になったんだろう、ということ。

参考情報

この改善項目は、Commitfestのリストには載っていない。
hackers-mlを探すと2021-04-07のメールが初出のようだ。
pgsql: psql: add \dconfig command to show server's configuration parame
このため、Commitfest 2022-03のリストにもないのだろう。
もしかすると、このCommitfest 2022-03のこのコミットが関係しているのかもしれない。
fix psql pattern handling

\weatch コマンドがページャに対応

psqlには\watchコマンドという機能がある。これは直前にpsqlから実行されたコマンドを、引数に指定した秒数ごとに繰り返し実行する、というもので、SQLを使ったアドホックな監視をするときに良く使っている。
このコマンドは従来はページャに対応していなかったが、PostgreSQL 15からはPAGERに指定したコマンドによるページング制御が可能になった。

\watchコマンドの結果をページャ用のコマンドで制御するには、環境変数PSQL_WATCH_PAGERにページャ用のコマンド（例えばless）を設定し、その状態psqlを実行すると使用できるようになる。

例えば、bash上でexport PSQL_WATCH_PAGER="less"を設定してpsqlを起動し、20行の結果を生成するSELECT文を実行する。

$ export PSQL_WATCH_PAGER="less"
$ ~/pgsql/pgsql-15b1/bin/psql -p 10015 postgres
psql (15beta1)
Type "help" for help.

postgres=# SELECT generate_series(1,20), clock_timestamp();
 generate_series |        clock_timestamp
-----------------+-------------------------------
               1 | 2022-05-28 15:32:19.803739+09
               2 | 2022-05-28 15:32:19.803751+09
               3 | 2022-05-28 15:32:19.803752+09
（中略）
              13 | 2022-05-28 15:32:19.80376+09
              14 | 2022-05-28 15:32:19.803761+09
--More--

この状態で、\watchコマンドを実行すると、\watchコマンドの出力結果はlessコマンドによってページ制御される(lessのプロンプト:になっていることに注目）。

postgres=# \watch 1


   Sat 28 May 2022 03:34:35 PM JST (every 1s)

 generate_series |        clock_timestamp
-----------------+-------------------------------
               1 | 2022-05-28 15:34:35.048501+09
               2 | 2022-05-28 15:34:35.048508+09
               3 | 2022-05-28 15:34:35.048509+09
（中略）
              10 | 2022-05-28 15:34:35.048515+09
              11 | 2022-05-28 15:34:35.048516+09
              12 | 2022-05-28 15:34:35.048517+09
:

参考情報

この改善は、Commitfest 2021-07でコミットされた。
allow to set a pager for psql's watch command

クエリ内のダブルハイフンのコメントをサーバに送信するようになった

PostgreSQLのクエリはコメントとしてダブルハイフン(--)も使うことができる。
ダブルハイフン以降、行末(改行コード)までは全てコメントとみなされる。

SELECT 1 -- 1を返す``

PostgreSQL 14まではこのダブルハイフンによるコメントはサーバ送信前にpsql内で除去されていた。
PostgreSQL 15からはダブルハイフンによるコメント文字列もサーバへ送信されるようになった。

たとえば、log_statement='all'が設定されているときに、PostgreSQL 14までは

test=# SELECT 'nuko', -- にゃーん
2;
 ?column? | ?column?
----------+----------
 nuko     |        2
(1 row)

test=#

というクエリを実行しても、ダブルハイフンコメント中のにゃーんはサーバログには記録されなかった。

PostgreSQL 14でのサーバログ

2022-05-28 10:17:42.067 JST [20709] LOG:  statement: SELECT 'nuko',
        2;

しかし、PostgreSQL 15からは、ダブルハイフンコメント中のにゃーんもサーバログに記録されるようになる。
(pgaudit等の監査拡張機能を使う場合も同様である）

PostgreSQL 15でのサーバログ

2022-05-28 10:10:49.932 JST [20606] LOG:  statement: SELECT 'nuko', -- にゃーん
        2;

なので、PostgreSQL 15以降のpsqlを使う場合には、ダブルハイフンコメントの中に変なコメントを書かないように気をつけねばならない。にゃーん。

参考情報

この改善項目を直接指しているCommitfestの項目は見つけられなかった。
この項目が関係指定そうだが、深追いはしていない。
Correct handling of blank/commented lines in PSQL interactive-mode history

ダブルハイフンのコメントマーカーを挿入するようにpsqlのreadline機能を改善

現状、改善内容を把握できず。
リリースノートを読むと

Adjust psql's readline meta-# to insert a double-hyphen comment marker (Tom Lane)
Previously an unhelpful pound marker was inserted.

と書かれているのだが、これはどういう状態での問題を解決したのかリリースノート記述からだけだとよくわからない。MLも追いきれなかった。
PostgreSQL 14.3とPostgreSQL 15 beta1でダブルハイフンつき、かつ複数行にまたがるクエリを実行して、その後にreadlineによるコマンド履歴を表示させたけど、違いがよくわからず・・・。

参考情報

この改善は、Commitfest 2022-01でコミットされたと思われる。
Correct handling of blank/commented lines in PSQL interactive-mode history

複数問い合わせ結果をpsqlが全て表示するようになった

PostgreSQL 14までは、複数の問い合わせをpsqlから実行した場合、最後の問い合わせの結果のみを表示していた。
ここでいう「複数の問い合わせ」というのがなかなか理解できなかったが、どうやら、以下のように、\;で区切られたクエリのことを指すらしい。
SELECT 1 \; SELECT 2 \; SELECT 3;

上記のクエリをPostgreSQL 14で実行した場合、最後のSELECT 3の結果のみが表示される。

test=# SELECT 1 \; SELECT 2 \; SELECT 3;
 ?column?
----------
        3
(1 row)

test=#

PostgreSQL 15では以下のように全ての問い合わせの結果が表示されるようになった。

postgres=# SELECT 1 \; SELECT 2 \; SELECT 3;
 ?column?
----------
        1
(1 row)

 ?column?
----------
        2
(1 row)

 ?column?
----------
        3
(1 row)

postgres=#

なお、この機能はpsql変数SHOW_ALL_RESULTS(これもPostgreSQL 15から導入されたpsql変数)を無効化することでPostgreSQL 14までの挙動（最後の問い合わせのみを表示）にすることもできる。

postgres=# \set SHOW_ALL_RESULTS off
postgres=# SELECT 1 \; SELECT 2 \; SELECT 3;
 ?column?
----------
        3
(1 row)

postgres=#

参考情報

この改善は、Commitfest 2022-03でコミットされた。
psql - add SHOW_ALL_RESULTS option

タブ補完を改良

psqlの使いやすい点の一つとして、SQL構文のタブ補完、データベースオブジェクトのタブ補完が豊富なことが挙げられる。これはreadlineの機能を使っている。
（逆にいうと、readlineに対応していない、psqlというのは正直言ってとても使いづらい。Windows版PostgreSQLについているpsqlのことだよっ！）

リリースノートでは「タブ補完を改良」としか書かれておらず、具体的にどんな改良がされたのかは、リリースノートから読み取ることはできない・・・。
しかたないので、Commitfestでコミットされたpsqlのタブ補完に関する項目を洗い出していく。

今回それっぽいTAB補完改善の項目は以下。

• CREATE PULICATION
• ALTER TABLE .. VALIDATE CONSTRAINT
• 大文字(upper charcter)入力時のTAB補完に対応

CREATE PULICATION

対応するCommitfest項目（Commitfest 2021-07）は以下。
psql tab auto-complete for CREATE PUBLICATION

このメールにあった幾つかのケースを試してみたが、PostgreSQL 14.3とPostgreSQL 15 beta1でTAB補完の挙動に差がなかった。
PostgreSQL 14.3だと、既にバックパッチされている？

ALTER TABLE .. VALIDATE CONSTRAINT

対応するCommitfest項目（Commitfest 2022-01）は以下。
psql: exclude valid constraints in tab-complete for ALTER TABLE .. VALIDATE CONSTRAINT

これもPostgreSQL 14.3とPostgreSQL 15 beta1でTAB補完の挙動に差がなかった。
PostgreSQL 14.3だと、既にバックパッチされている？

大文字(upper charcter)入力時のTAB補完に対応

対応するCommitfest項目（Commitfest 2022-01）は以下。
Support tab completion for upper character inputs in psql

議論メールは追いかけきれていないけど、SET/RESET/SHOWの3コマンドのコマンド名をが小文字であっても、大文字であってもTAB補完してくれる、という解釈でいいのかな。

ただ、これもPostgreSQL 14.3とPostgreSQL 15 beta1でTAB補完の挙動に差がなかった（展開されるパラメータそのものは15で増えているので、その違いはあるけれど）。
PostgreSQL 14.3だと、既にバックパッチされている？

参考情報

この改善は、以下のCommitfest契機でコミットされた。

• psql tab auto-complete for CREATE PUBLICATION
• psql: exclude valid constraints in tab-complete for ALTER TABLE .. VALIDATE CONSTRAINT
• Support tab completion for upper character inputs in psql

psqlのサポートをPostgreSQL 9.2以降が動作するサーバに限定

この修正については、Commitfestのリストからは見つけられなかった。
MLを探すと、pgsql-commiters MLのこの内容が該当するものっぽい。
pgsql: Remove psql support for server versions preceding 9.2.

PostgreSQL 9.1自体はとうの昔にEOLになっているので、これで困るケースはそれほどなさそうには思うが、PostgreSQL 15からは古すぎるバージョンのサーバに接続しようとすると警告される、ということか。

実際にPostgreSQL 9.1.24のサーバを立てて、PostgreSQL 15 beta1で接続すると、Some psql features might not work.といった警告が表示されるようになった。
ただ警告は表示されるのみで接続自体が拒否されるというわけではない。

$ /home/ec2-user/pgsql/pgsql-9.1/bin/pg_ctl -D /tmp/pg091 -l logfile start
server starting
[ec2-user@ip-10-0-1-10 postgresql-9.1.24]$ ~/pgsql/pgsql-15b1/bin/psql -p 10091 postgres
psql (15beta1, server 9.1.24)
WARNING: psql major version 15, server major version 9.1.
         Some psql features might not work.
Type "help" for help.

postgres=# SELECT version();
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.24 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-13), 64-bit
(1 row)

postgres=#

psql の \password コマンドを改善する

この改善は、Commitfest 2021-11でコミットされた。
Improving psql's \password command
理由は良くわからないが、PostgreSQL 15 beta1のリリースノートには掲載されていない項目だ。

自分の理解（手元で試した感じ）だと、

• PostgreSQL 14までは、\passwordでパスワード変更を試みている最中には、CTRL+Cでの中断はできない。
• PostgreSQL 15では、\passwordでパスワード変更を試みている最中に、CTRL+Cでの中断が可能になった

と解釈しているけど、それであっているのかな・・・（MLの議論は追いきれていない・・・）

psqlの絵文字の幅の計算の問題を改善

これも改善内容をまだ確認できず・・・
リリースノートには記載されていなかったが、Commitfestでコミットされていた項目には入っていた。

とりあえず、絵文字を含むクエリをpsqlで実行（psqlｎ実行環境はPutty日本語版+独自パッチ版？を使った）してみたが、PostgreSQL 14.3とPostgreSQL 15 beta1での違いは分からなかった。（どっちも微妙にずれている）

PostgreSQL 14.3

$ ~/pgsql/pgsql-15b1/bin/psql -U postgres -p 10014 test -e -f emoji.sql
CREATE TEMP TABLE hoge (n text, i text, v text);
CREATE TABLE
INSERT INTO hoge VALUES
('elephant', '🐘', 'paon'),
('cat', '😺', 'nya-n'),
('dolphin', '🐬', 'kyu-')
;
INSERT 0 3
SELECT * FROM hoge;
    n     | i  |   v
----------+----+-------
 elephant | 🐘 | paon
 cat      | 😺 | nya-n
 dolphin  | 🐬 | kyu-
(3 rows)

PostgreSQL 15 beta1

$ ~/pgsql/pgsql-15b1/bin/psql -U postgres -p 10015 test -e -f emoji.sql
CREATE TEMP TABLE hoge (n text, i text, v text);
CREATE TABLE
INSERT INTO hoge VALUES
('elephant', '🐘', 'paon'),
('cat', '😺', 'nya-n'),
('dolphin', '🐬', 'kyu-')
;
INSERT 0 3
SELECT * FROM hoge;
    n     | i  |   v
----------+----+-------
 elephant | 🐘 | paon
 cat      | 😺 | nya-n
 dolphin  | 🐬 | kyu-
(3 rows)

参考情報

この改善は、Commitfest 2021-09でコミットされた。
badly calculated width of emoji in psql

おわりに

リリースノートに記載されていない項目があったり、Commitfestに記載されていない改善項目があったり、既にPostgreSQL 14.3では対応ずみ？な項目もあったりしたので、今回の調査は意外に難航した・・・。

psqlは、PostgreSQLを使う時にいつもお世話になっているユーティリティなんだけど、未だに進化を続けているというのは凄いことだなあ、と毎回バージョンアップ時に思っている。
これが便利すぎるから、pgAdmin4等のGUI系クライアントインタフェースを使わなくなってしまうんだよね・・・。