PostgreSQL 18がやってくる(4) psqlの改善

# はじめに
にゃーん。
今回はPostgreSQLの対話的ターミナル``psql``のPostgreSQL 18での改善項目について調べてみた。

# 調査方法
* 調査対象の改善項目は2025-04-5時点の[Commitfest 2025-03](https://commitfest.postgresql.org/52/)に上がっている項目で``Status=Commited``かつ、``Text=psqlタ``のものとした（4項目）。
  * この探し方だと、``psql``という文言は含まないけど、実は``psql``の改善だったという項目が漏れてしまう・・・やっぱりClientsカテゴリの項目は一通りチェックしないとダメっぽい。
* 本記事ではCommitログのみで挙がっている改善項目は対象外（サボった）。
  * [pgsql-commiter]()のMLからpsqlで引っ掛けるとCommitfest以外の項目（マテビューのTAB補完とか）等々もいくつか引っかかるが今回は割愛。
* 実機検証は[Commit 683df3f](https://github.com/postgres/postgres/commit/683df3f4de00bf50b20eae92369e006badf7cd57)(2024-04-06)のmasterブランチのコードをビルドした版を使った。

# 改善項目概要
Commitfestに挙がっていたpsqlに関する改善項目を列挙する。

* psql meta-command conninfo+
conninfoメタコマンドの改善
* Allow default \watch interval in psql to be configured
\watch メタコマンドのデフォルト間隔値の設定
* Add Pipelining support to psql
パイプラインモードのサポート
* Better title output for psql \dt \di \dv \dm \ds \dE
カタログ表示系メタコマンドのタイトル表示改善

# 改善項目詳細
Commitfestで挙がっていた項目の内容をもう少し詳しく調べてみた。

## conninfoメタコマンドの改善
* [psql meta-command conninfo+](https://commitfest.postgresql.org/patch/4814/)

これは``\conninfo``に詳細表示オプション``\conninfo+``を追加した、という改善のようだ。

``conninfo``はPostgreSQL 16からpsqlで使用可能なメタコマンド。
現在のデータベース接続に関する情報を出力する。
PostgreSQL 17までは以下のような1行の文字列として情報を出力していた。

```sql
$ ~/pgsql/pgsql-17/bin/psql -p 17001 -U testuser testdb
psql (17.0)
Type "help" for help.

testdb=> \conninfo
You are connected to database "testdb" as user "testuser" via socket in "/tmp" at port "17001".
testdb=>
```

PostgreSQL 18からは接続に関する情報を``Parameter``,``Value``の列として、各情報を別レコードの形式で出力するように改善された。

```sql
$ psql -p 18001 testdb -U testuser
psql (18devel)
Type "help" for help.

testdb=> \conninfo
     Connection Information
      Parameter       |  Value
----------------------+----------
 Database             | testdb
 Client User          | testuser
 Socket Directory     | /tmp
 Server Port          | 18001
 Options              |
 Protocol Version     | 3
 Password Used        | false
 GSSAPI Authenticated | false
 Backend PID          | 28426
 TLS Connection       | false
 Superuser            | off
 Hot Standby          | off
(12 rows)
```

``\conninfo``で出力される``Parameter``の種類は接続方法によって異なる。
例えば上記の例はUnixドメインソケットによる接続(``-h hostname``を付けない接続)の例だが、``-h hostname``を付与した接続の場合は、以下のように``Parameter``の内容も異なる（``Socket Directory``行がなくなり、代わりに``Host``行と``Host Address``行が追加されている）。
```sql
$ psql -p 18001 -h localhost testdb -U testuser
psql (18devel)
Type "help" for help.

testdb=> \conninfo
      Connection Information
      Parameter       |   Value
----------------------+-----------
 Database             | testdb
 Client User          | testuser
 Host                 | localhost
 Host Address         | 127.0.0.1
 Server Port          | 18001
 Options              |
 Protocol Version     | 3
 Password Used        | false
 GSSAPI Authenticated | false
 Backend PID          | 29418
 TLS Connection       | false
 Superuser            | off
 Hot Standby          | off
(13 rows)
```
``Parameter``の出力順序はアルファベット順ではなく、``\conninfo``機能（``exec_command_conninfo()``関数の実装）で固定となっている。

:::note info
もともと、この機能が提案されたときには、``\conninfo``は従来のようにテキスト1行で出力、``\conninfo+``を指定したときに、``Parameter``ごとに別の行で出力するという提案だったようだが、MLでの議論の結果、``\conninfo``で別の行に出力するようになったらしい（つまりPostgreSQL 17までの出力形式とは非互換）。
また``conninfo+``自体は現時点では実装されていない。
```sql
testdb=> \conninfo+
invalid command \conninfo+
Try \? for help.
```
:::

## \watch メタコマンドのデフォルト間隔値の設定
* [Allow default \watch interval in psql to be configured](https://commitfest.postgresql.org/patch/5353/)

psqlには特別に定義された「変数」がある。（[Variables](https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-VARIABLES)参照）
PostgreSQL 18では、この変数として``WATCH_INTERVAL``が追加された。
この``WATCH_INTERVAL``はデフォルトでは2が設定されている。
```sql
postgres@postgres=# \echo :WATCH_INTERVAL
2
postgres@postgres=#
```
このため``\watch``メタコマンドを引数なしで実行すると2秒間隔で直前のSQLコマンドを実行する。
以下にデフォルト状態での``watch``メタコマンドの実行例を示す。デフォルトだと(約)2秒間隔で、``SELECT now()``が実行されているのがわかる。

```sql
postgres@postgres=# \t
Tuples only is on.
postgres@postgres=# SELECT now();
 2025-04-06 09:48:06.070375+09

postgres@postgres=# \watch
 2025-04-06 09:48:11.181377+09

 2025-04-06 09:48:13.181536+09

 2025-04-06 09:48:15.181516+09
```

``WATCH_INTERVAL``変数の値を変更する場合は、psqlの``\set``コマンドを使う。
``\set``コマンドで``WATCH_INTERVAL``の値を0.5(秒)に設定し、SQLコマンド実行後に``watch``メタコマンドを実行した例を以下に示す。

```sql
postgres@postgres=# \t
Tuples only is on.
postgres@postgres=# \echo :WATCH_INTERVAL
2
postgres@postgres=# \set WATCH_INTERVAL 0.5
postgres@postgres=# SELECT now();
 2025-04-06 09:57:11.355264+09

postgres@postgres=# \watch
 2025-04-06 09:57:18.007455+09

 2025-04-06 09:57:18.507585+09

 2025-04-06 09:57:19.00757+09

 2025-04-06 09:57:19.507603+09

 2025-04-06 09:57:20.007538+09

```
``\watch``メタコマンドによって約0.5秒間隔で``SELECT now()``が実行されている。

:::note info
``\set w``まで入力した状態で、TABキーを押すと、``\set WATCH_INTERVAL ``と補完してくれるので、あとは間隔の数値を入力するだけでOK。
（これに頼りすぎると変数名を覚えなくなるという諸刃の剣ではあるが）
:::

:::note info
PsotgreSQL 17以前の版では``\echo :WATCH_INTERVAL``を実行してもエラーにはならず、``:WATCH_INTERVAL``と出力されます。
```sql
postgres@postgres=# \echo :WATCH_INTERVAL
:WATCH_INTERVAL
postgres@postgres=#
```
:::

## パイプラインモードのサポート
* [Add Pipelining support to psql](https://commitfest.postgresql.org/patch/5407/)

パイプラインモードのサポートというのは、PostgreSQL 14から利用可能になった、libpq拡張プロトコルを使った実行モードの一つ。このモードは
* 以前に送信された問い合わせの結果を読み込まなくても問い合わせを送信できる。
* パイプラインモードを利用すると、1つのネットワークトランザクションで複数の問い合わせ/結果を送受信できる。
* クライアントはサーバを待つ時間が少なくなる。 

というもの。
このパイプラインモードは別のクライアントツール``pgbench``（PostgreSQLに対してベンチマーク試験を行う）では、PostgreSQL 14から使用可能だった（自分も以前調査したことがある。[ぜんぜんわからない。俺たちは雰囲気でpgbenchを使っていた。](https://qiita.com/nuko_yokohama/items/237316117dc34c24ae05)参照）

PostgreSQL 18では、``pgbench``と同様のメタコマンドを``psql``でも使用可能になった。
PostgreSQL 18の``psql``では以下のパイプラインに関するメタコマンドを提供している。
|メタコマンド名|パラメータ|機能|
|:--|:--|:--|
|``\startpipeline`` |（なし）|パイプラインを開始する。|
|``\sendpipeline`` |（なし）|現在のクエリバッファをパイプラインに追加する。 |
|``\syncpipeline`` |（なし）|送信バッファをフラッシュすることなく同期メッセージを送信する。 |
|``\endpipeline`` |（なし）|パイプラインを終了する。|
|``\flushrequest`` |（なし）|パイプラインに``\flush``コマンドを追加し、同期を発行したりパイプラインを終了したりせずに、``\getresults``で結果を読み取り可能にする。 |
|``\flush`` |（なし）|未送信のデータを手動でプッシュするために使用する。 |
|``\getresults`` |``[ number_results ] #`` |\getresultsはオプションのnumber_resultsパラメータ(``#``)を受け付ける。指定された場合、最初の number_results 個の保留中の結果のみが読み取られる。指定がない場合、または 0 の場合は、保留中のすべての結果が読み取られる。 |

たとえば、以下の3つのSELECT文
```sql
SELECT 1, now();
SELECT pg_sleep(3);
SELECT 2, now();
```
をパイプラインなし実行した場合は、以下のようになる。
```sql
$ psql -a -p 18001 testdb -U testuser -f no-pipeline-0.sql
\timing
Timing is on.
SELECT 1, clock_timestamp();
 ?column? |        clock_timestamp
----------+-------------------------------
        1 | 2025-04-06 15:48:08.098753+09
(1 row)

Time: 0.503 ms
SELECT 2,pg_sleep(3);
 ?column? | pg_sleep
----------+----------
        2 |
(1 row)

Time: 3003.577 ms (00:03.004)
SELECT 3, clock_timestamp();
 ?column? |        clock_timestamp
----------+-------------------------------
        3 | 2025-04-06 15:48:11.102694+09
(1 row)
```
3つ目のSELECT文は2つ目の``SELECT pg_sleep(3)``が実行される3秒後に実行される。

同じ3つのSELECT分をパイプラインモードで実行した場合には以下のようになる。
```sql
$ psql -a -p 18001 testdb -U testuser -f pipeline-0.sql
\timing
Timing is on.
\startpipeline
Time: 0.000 ms
SELECT 1, clock_timestamp();
Time: 0.000 ms
SELECT 2,pg_sleep(3);
Time: 0.000 ms
SELECT 3, clock_timestamp();
Time: 0.000 ms
\endpipeline
 ?column? |        clock_timestamp
----------+-------------------------------
        1 | 2025-04-06 15:48:42.308991+09
(1 row)

 ?column? | pg_sleep
----------+----------
        2 |
(1 row)

 ?column? |        clock_timestamp
----------+-------------------------------
        3 | 2025-04-06 15:48:45.312447+09
(1 row)

Time: 3004.142 ms (00:03.004)
```
3つのSELECT文は（見た目上は）待ち時間なくすぐにプロンプトを返却する。
そして、``\endpipeline``メタコマンドのあとで、パイプライン全体のSELECT文を実行する。
そのあと、各SELECT文の結果が順次出力される。
``\timing``メタコマンドで返却される時間はパイプライン全体の時間となる。

動作方法はわかったのだが。実運用でこの機能を使うケースが実はあんまり想定できていない・・・。

:::note info
この機能の[提案メール](https://www.postgresql.org/message-id/flat/CAO6_XqroE7JuMEm1sWz55rp9fAYX2JwmcP_3m_v51vnOFdsLiQ@mail.gmail.com)を読んだ感じでは、パイプラインモードの試験のときに、``ppgbench``を使ったTAPテストしかできなかったので、``psql``を使って簡単にテストできるようにするのが目的のようにも読める・・・。
:::

## カタログ表示系メタコマンドのタイトル表示改善
* [Better title output for psql \dt \di \dv \dm \ds \dE](https://commitfest.postgresql.org/patch/5540/)

psqlにはテーブルの一覧や、テーブル内の列一覧を表示する等のカタログ表示系メタコマンドが多数用意されている。
PostgreSQL 17まではどのオブジェクト種別であってもタイトル行は``List of relations``を表示していた。（以下はテーブルの例）
```sql
\dt test_t
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | test_t | table | postgre
(1 row)

```

PostgreSQL 18ではオブジェクト種別に応じて適切なタイトル行に修正された。

|メタコマンド|表示対象のオブジェクト種別|修正後のタイトル文字列|
|:--|:--|:--|
|\dt|テーブル |``List of tables``|
|\di |インデックス |``List of indexes``|
|\dv |ビュー |``List of views``|
|\dm |マテリアライズド・ビュー |``List of materialized views``|
|\ds |シーケンス |``List of sequences``|
|\dE |外部テーブル |``List of foreign tables``|

PostgreSQL 18での``\dt``,``\di``,``\ds``,``\dv``,``\dm``の実行例を以下に示す。
（外部テーブル(``\dE``)は作るのが少し面倒なので今回は割愛）

```sql
$ psql -p 18001 -U postgres testdb -a -f d_metacommand.sql
\dt test_t
           List of tables
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | test_t | table | postgres
(1 row)

\di test_t_id_idx
                  List of indexes
 Schema |     Name      | Type  |  Owner   | Table
--------+---------------+-------+----------+--------
 public | test_t_id_idx | index | postgres | test_t
(1 row)

\ds test_t_seq_num_seq
                 List of sequences
 Schema |        Name        |   Type   |  Owner
--------+--------------------+----------+----------
 public | test_t_seq_num_seq | sequence | postgres
(1 row)

\dv test_v
           List of views
 Schema |  Name  | Type |  Owner
--------+--------+------+----------
 public | test_v | view | postgres
(1 row)

\dm test_mv
           List of materialized views
 Schema |  Name   |       Type        |  Owner
--------+---------+-------------------+----------
 public | test_mv | materialized view | postgres
(1 row)
```

# おわりに
今回はCommitfestに挙がっていた4つの``psql``の改善項目を調べてみた。
Commitfestに挙がっていない``psql``の改善項目（バグFix等含む）は、別途余力があったら調べてみる。

# 追記
[Noriyoshi Shinoda](https://qiita.com/plusultra)さんからのコメント。
``psql``キーワードだけでCommitfestを探すと結構漏らしてしまうな・・・。
暇をみて、それぞれの項目を確認しよう。

>パイプラインモードでは以下の変数が使えます。
PIPELINE_SYNC_COUNT
PIPELINE_COMMAND_COUNT
PIPELINE_RESULT_COUNT
プリペア文の定義を操作する以下のメタコマンドが追加されました。
\parse
\bind_named
\close
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d55322b0da60a8798ffdb8b78ef90db0fb5be18e
\dfx とか \lx とか拡張モードの出力を強制する機能
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=978f38c771fb3a19fdd5cb73cb662441eb9e551c
変数SERVICE
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=477728b5d6fa16461b81cd22b0568fec1eab97ac
パーティション・テーブルのアクセスメソッドを表示する \dP+
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=978f38c771fb3a19fdd5cb73cb662441eb9e551c