More than 3 years have passed since last update.

PostgreSQL 13がやってくる！(6) - psqlの改善あれこれ

Last updated at 2020-05-09Posted at 2020-05-08

はじめに

　にゃーん。
　何もしないまま5月連休が終わってしまって、実ににゃーんです。
とはいえ、PostgreSQL界隈ではそれなりに動きがあって、

PostgreSQL 12文書日本語版の公開
PostgreSQL 13 Release Notes Draft¹の公開

なんかがありました。　今回は、PostgreSQL 13 Release Notes Draftから、psqlの改善項目を紹介しようかと。

PostgreSQL 13のpsql改善概要

　リリースノートを見ると、psqlに関する改善項目は以下の9項目あります。

Add the transaction status (%x) to the default psql prompts (Vik Fearing)
Allow the secondary psql prompt to be same number of spaces as the primary prompt (Thomas Munro)
Allow \g and \gx to change any pset output options for a single command (Tom Lane)
Add psql commands to report operator classes and operator families (Sergey Cherkashin, Nikita Glukhov, Alexander Korotkov)
Show table persistence in psql's \dt+ and related commands (David Fetter)
Improve output of psql \d for TOAST tables (Justin Pryzby)
Adjust display of psql's \e query (Tom Lane)
Add \warn command to psql (David Fetter)
Add the PostgreSQL home page to command-line --help output (Peter Eisentraut)

　以下、それぞれの改善点について、手元の環境で動かして確認してみた。

psqlのプロンプトに関する改善

デフォルトのプロンプトでトランザクションステータスが表示されるようになった

Add the transaction status (%x) to the default psql prompts (Vik Fearing)

　psqlのデフォルトプロンプトに、トランザクションステータスが入るようになったと。トランザクションステータスって何？ということだが、以下の例を見てもらうほうがわかりやすいかも。

testdb=#
testdb=# BEGIN;
BEGIN
testdb=*# INSERT INTO test VALUES (1, 'foo');
INSERT 0 1
testdb=*# ERROR;
ERROR:  syntax error at or near "ERROR"
LINE 1: ERROR;
        ^
testdb=!# ROLLBACK;
ROLLBACK
testdb=#

状態	state	psql prompt
管理者ロールでログインした直後 (トランザクション外)	idle	=#
トランザクション内	idle in transaction	=*#
トランザクション内エラー発生	idle in transaction (aborted)	=!#

　プロンプトを見て、簡易的に現在のセッションの状態を確認できるってわけだ。
　実はこのトランザクション状態をプロンプトに展開する機能自体は、PostgreSQL 7.4の頃から実装されていたりする。
　ただ、デフォルトのPROMPT1, PROMPT2の設定が、%x がつかない設定値になっているため、この機能自体に気づいていない人も多そうだ（自分も今日、初めて知ったｗ）
　PostgreSQL 13では、このPROMPT1, PROMPT2のデフォルト値に%xが設定された。

PostgreSQL 12までのPROMPTのデフォルト設定値

testdb=# \echo :PROMPT1
%/%R%#
testdb=# \echo :PROMPT2
%/%R%#
testdb=#

PostgreSQL 13からのPROMPTのデフォルト設定値

testdb=# \echo :PROMPT1
%/%R%x%#
testdb=# \echo :PROMPT2
%/%R%x%#
testdb=#

PROMPT2の桁揃え

ていうか、PROMPT2って何よ？って思う人も多いかもしれない。PROMPT2(プロンプト2)というのは、psqlのコマンドが完了していない状態で表示されるプロンプトを指す。

プロンプト2は、例えばコマンドがセミコロンで終わっていない、または、引用符が閉じていないなど、コマンドの入力中にさらなる入力が期待される際に発行されます。

(PostgreSQL 12文書から引用)

具体的な例を見たほうが早いかも。
例えば、PROMPT1にこんな設定をして、継続行となるクエリを入力する。

testdb=# \set PROMPT1 '%/%R%x%Looooooong prompt# '
testdb=Looooooong prompt#
testdb=Looooooong prompt# SELECT 'foo
testdb'# bar
testdb'# baz'
testdb-# ;
 ?column?
----------
 foo     +
 bar     +
 baz
(1 row)

PROMPT2には%/%R%x%#が設定されているので、継続行の左端の列が揃わない。いや、自分は別に気にしないけど、気にする人がいるのだろうなあ。
で、PostgreSQL 13からは、PROMPT2に%wを設定することで、継続行の左端をPROMPT1の入力位置に合わせるようになった。

testdb=Looooooong prompt# \set PROMPT2 '%w'
testdb=Looooooong prompt# SELECT 'foo
                          bar
                          baz'
                          ;
 ?column?
----------
 foo     +
 bar     +
 baz
(1 row)

testdb=Looooooong prompt#

psqlメタコマンドに関する改善

　さて、psqlといえば多彩なメタコマンド（多すぎて自分も全ては把握しきれてない）。PostgreSQL 13でも様々なメタコマンドの追加・改善が入っている。

\gx, \g のオプション指定追加

Allow \g and \gx to change any pset output options for a single command (Tom Lane)

そもそも`\g`とか`\gx`って何？

これはpsqlのメタコマンドで、すごく雑にいうと、psql内で直前に実行したコマンドを再度実行するというもの。
\gはexpandedの設定に従って出力する。

testdb=# SELECT * FROM test ;
 id | data
----+------
  1 | foo
  2 | bar
(2 rows)

testdb=# \g
 id | data
----+------
  1 | foo
  2 | bar
(2 rows)

testdb=# \x
Expanded display is on.
testdb=# \g
-[ RECORD 1 ]
id   | 1
data | foo
-[ RECORD 2 ]
id   | 2
data | bar

testdb=#

\gxは強制的にexpanded=onの設定で結果を出力する。

estdb=# SELECT * FROM test ;
 id | data
----+------
  1 | foo
  2 | bar
(2 rows)

testdb=# \gx
-[ RECORD 1 ]
id   | 1
data | foo
-[ RECORD 2 ]
id   | 2
data | bar

testdb=#

PostgreSQL 13の変更点

\gも\gxも以前からあるメタコマンドなんだけど、ではPostgreSQL 13で何が変更されたのかというと、\gや\gxにオプション指定ができるようになった、というものらしい。

PostgreSQL 12の\?の表示

testdb=# \?
General
  \copyright             show PostgreSQL usage and distribution (略)
  \g [FILE] or ;         execute query (and send results to file or |pipe)
(略)
  \gx [FILE]             as \g, but forces expanded output mode
(略)

PostgreSQL 13の\?表示。

testdb=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
(略)
  \g [(OPTIONS)] [FILE]  execute query (and send results to file or |pipe);
                         \g with no arguments is equivalent to a semicolon
(略)
  \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
(略)

ということで、(OPTIONS)が設定可能になったようだけど、具体的に何を指定すると嬉しいか。
例として、\pset null=STRINGあたりは応用が効きそう。

testdb=# TABLE test;
 id | data
----+------
  1 | foo
  2 |
(2 rows)

たとえば、このクエリを実行した直後に、(null=(null)を\gあるいは\gxのオプションとして指定するとちょい嬉しいかも。

testdb=# \g (null=(null))
 id |  data
----+--------
  1 | foo
  2 | (null)
(2 rows)

testdb=# \gx (null=(null))
-[ RECORD 1 ]
id   | 1
data | foo
-[ RECORD 2 ]
id   | 2
data | (null)

testdb=#

演算子クラス/演算子族の表示

Add psql commands to report operator classes and operator families (Sergey Cherkashin, Nikita Glukhov, Alexander Korotkov)

　psqlには様々なカタログ情報を表示するためのメタコマンドが多く存在している。
　PostgreSQL 13からは演算子クラスと演算子族をメタコマンドで表示できるようになった。

testdb=# \?
(略)
  \dAc[+] [AMPTRN [TYPEPTRN]] list operator classes
  \dAf[+] [AMPTRN [TYPEPTRN]] list operator families
  \dAo[+] [AMPTRN [OPFPTRN]] list operators of operator families
families
 \dAp    [AMPTRN [OPFPTRN]] list procedures of operator families
(略)
testdb=#

メタコマンド	内容
\dAc	演算子クラスを表示する。
\dAf	演算子族を表示する。
\dAo	演算子族に含まれる演算子を表示する。
\dAp	演算子族を実装しているプロシージャ名を表示する。

　これらのメタコマンドは引数がない場合、全ての演算子クラスや演算子族を表示する。また、\dAc+のように末尾に+をつけると詳細な情報を表示する。このへんは、既存のカタログ表示のメタコマンドの流儀を踏襲している。

　演算子クラスや演算子族ってなんぞ？という人も多いとは思うけど、このあたりは普通にPostgreSQLのビルトインデータ型を使っている分には、まず意識することのないもの。
　詳しく知りたい人は、PostgreSQL文書の演算子クラスと演算子族あたりを見てもらうといいかも。すごく雑にいうと、拡張機能でインデックスを扱うときに意識しなければいけないものなので、どちらかと言えば拡張機能の開発者向け機能って気もする。

　たとえば、データ型を自作して、そのデータ型用のインデックスを拡張するようなときに、演算子クラスを作成する。以下はpg_fractionという謎ユーザ定義型をCREATE EXTENSIONで登録した環境で演算子クラス/演算子族表示のメタコマンドを実行した例である。

演算子クラスの表示

testdb=# \dAc btree fraction
                   List of operator classes
  AM   | Input type | Storage type | Operator class | Default?
-------+------------+--------------+----------------+----------
 btree | fraction   |              | fraction_ops   | yes
(1 row)

testdb=# \dAc+ btree fraction
                                  List of operator classes
  AM   | Input type | Storage type | Operator class | Default? | Operator family |  Owner
-------+------------+--------------+----------------+----------+-----------------+----------
 btree | fraction   |              | fraction_ops   | yes      | fraction_ops    | postgres
(1 row)

testdb=#

演算子族の表示

testdb=# \dAf btree fraction
         List of operator families
  AM   | Operator family | Applicable types
-------+-----------------+------------------
 btree | fraction_ops    | fraction
(1 row)

testdb=# \dAf+ btree fraction
               List of operator families
  AM   | Operator family | Applicable types |  Owner
-------+-----------------+------------------+----------
 btree | fraction_ops    | fraction         | postgres
(1 row)

testdb=#

演算子族に含まれる演算子の表示

testdb=# \dAf btree fraction
         List of operator families
  AM   | Operator family | Applicable types
-------+-----------------+------------------
 btree | fraction_ops    | fraction
(1 row)

testdb=# \dAf+ btree fraction
               List of operator families
  AM   | Operator family | Applicable types |  Owner
-------+-----------------+------------------+----------
 btree | fraction_ops    | fraction         | postgres
(1 row)

testdb=#

演算子を実装しているプロシージャの表示

estdb=# \dAo btree fraction_ops
     List of operators of operator families
  AM   | Opfamily Name |        Operator
-------+---------------+-------------------------
 btree | fraction_ops  | < (fraction, fraction)
 btree | fraction_ops  | <= (fraction, fraction)
 btree | fraction_ops  | = (fraction, fraction)
 btree | fraction_ops  | >= (fraction, fraction)
 btree | fraction_ops  | > (fraction, fraction)
(5 rows)

testdb=# \dAo+ btree fraction_ops
                        List of operators of operator families
  AM   | Opfamily Name |        Operator         | Strategy | Purpose | Sort opfamily
-------+---------------+-------------------------+----------+---------+---------------
 btree | fraction_ops  | < (fraction, fraction)  |        1 | search  |
 btree | fraction_ops  | <= (fraction, fraction) |        2 | search  |
 btree | fraction_ops  | = (fraction, fraction)  |        3 | search  |
 btree | fraction_ops  | >= (fraction, fraction) |        4 | search  |
 btree | fraction_ops  | > (fraction, fraction)  |        5 | search  |
(5 rows)

testdb=#

演算子を実装しているプロシージャの表示

testdb=# \dAp btree fraction_ops
                     List of procedures of operator families
  AM   | Operator family | Left arg type | Right arg type | Number |  Proc name
-------+-----------------+---------------+----------------+--------+--------------
 btree | fraction_ops    | fraction      | fraction       |      1 | fraction_cmp
(1 row)

testdb=# \dAp+ btree fraction_ops
                     List of procedures of operator families
  AM   | Operator family | Left arg type | Right arg type | Number |  Proc name
-------+-----------------+---------------+----------------+--------+--------------
 btree | fraction_ops    | fraction      | fraction       |      1 | fraction_cmp
(1 row)

testdb=#

　\dApメタコマンドに関しては、現状は+を付けても付加情報は出力されないようだ。

使いみち

　自分のようなフツーの利用者としては、例えば、CREATE INDEXのオプションとして演算子クラスを指定するようなときに、「あれ？この拡張機能の演算子クラスってなんだんだっけ？」というときに使えるかも。
　例えば、PostgreSQLの代表的な日本語n-gram全文検索pg_bigmでは、全文インデックスを作成するときに演算子クラスを指定する。その名前が思い出せない・・・というときも

testdb=# \dx pg_bigm
                                 List of installed extensions
  Name   | Version | Schema |                           Description
---------+---------+--------+------------------------------------------------------------------
 pg_bigm | 1.2     | public | text similarity measurement and index searching based on bigrams
(1 row)

testdb=# \dAc gin text
                  List of operator classes
 AM  | Input type | Storage type | Operator class | Default?
-----+------------+--------------+----------------+----------
 gin | text       |              | gin_bigm_ops   | no
(1 row)

testdb=#

「お、演算子クラスはgin_bigm_opsだったんだな」とわかる。

testdb=# CREATE INDEX data_idx_gin ON test USING gin (data gin_bigm_ops);
CREATE INDEX
testdb=#

めでたしめでたし。

\dメタコマンドでPersistenceが表示された

Show table persistence in psql's \dt+ and related commands (David Fetter)

　PostgreSQLではWALを生成しないテーブル(UNLOGGED TABLE)を作成することができる。自宅のPostgreSQL環境だと、このUNLOGGED TABLEを使うことが多いんだけど、PostgreSQL 12までは、作成したテーブルが、通常のテーブルなのかUNLOGGED TABLEなのかを、一覧表示で確認できなかった（\d テーブル名とかで個別に指定する必要があった)。
　PostgreSQL 13からは、一覧表示の\d+あるいは\dt+で通常のテーブルかUNLOGGED TABLEかを表示してくれるようになった。ちょい嬉しい。

testdb=# CREATE TABLE l_table (id int, data text);
CREATE TABLE
testdb=# CREATE UNLOGGED TABLE ul_table (id int, data text);
CREATE TABLE
testdb=# \d
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | l_table  | table | postgres
 public | ul_table | table | postgres
(2 rows)

testdb=# \d+
                               List of relations
 Schema |   Name   | Type  |  Owner   | Persistence |    Size    | Description
--------+----------+-------+----------+-------------+------------+-------------
 public | l_table  | table | postgres | permanent   | 8192 bytes |
 public | ul_table | table | postgres | unlogged    | 16 kB      |
(2 rows)

testdb=# \dt+
                               List of relations
 Schema |   Name   | Type  |  Owner   | Persistence |    Size    | Description
--------+----------+-------+----------+-------------+------------+-------------
 public | l_table  | table | postgres | permanent   | 8192 bytes |
 public | ul_table | table | postgres | unlogged    | 16 kB      |
(2 rows)

testdb=#

TOASTテーブルの\d表示の性能向上(?)

Improve output of psql \d for TOAST tables (Justin Pryzby)

らしい。性能比較環境作るのが面倒なのでちょい後回し。

\eメタコマンド終了時のクエリの表示を調整

Adjust display of psql's \e query (Tom Lane)

　エディタを終了するときに、クエリがセミコロンまたは\ gで終わっていない場合、クエリバッファの内容が表示されるようになった、とのこと。
　\eメタコマンドでエディタ(うちの環境だとvim)を起動して、SELECT 1と入れてみる。

SELECT 1
~
~
~
~
~
~
~
~
"/tmp/psql.edit.4059.sql" 3L, 12C

この状態で、:xとかで保存終了する。

testdb=# \e
testdb=# SELECT 1
testdb-#

　この状態で、たとえば;を入力してリターンすると、SELECT 1が実行される。

testdb=# \e
testdb=# SELECT 1
testdb-# ;
 ?column?
----------
        1
(1 row)

testdb=#

　地味だけど、エディタ編集内容が実行前に確認できるのは良さげ。

標準エラー出力指定が可能になった。

Add \warn command to psql (David Fetter)

　以前のpsqlでも、\echoメタコマンドや\qechoメタコマンドで、任意の文字列を標準出力に出力することができた。
　PostgreSQL 13からは、\warnメタコマンドで任意のメッセージを標準エラー出力に出力できるようになった。
　例えば、以下のようなpsqlのスクリプトファイルを作成する。

$ cat /tmp/q.txt
\qecho stdout
\warn stderr

このファイルをpsqlに与え、その結果の標準出力に/tmp/out.txtに、標準エラー出力を/tmp/err.txtに書き出す。

$ psql postgres -f /tmp/q.txt 1> /tmp/out.txt 2> /tmp/err.txt
$ cat /tmp/out.txt
stdout
$ cat /tmp/err.txt
stderr
$

　psql用の複雑なスクリプトファイルを作成し、その中で独自のエラーメッセージを埋め込みたいときに役に立ちそうだ。

その他

--help指定地にPostgreSQLホームページの情報が表示が追加された。

Add the PostgreSQL home page to command-line --help output (Peter Eisentraut)

PostgreSQL 12まで。

$ ~/pgsql/pgsql-12/bin/psql --help
psql is the PostgreSQL interactive terminal.
（略）
Report bugs to <pgsql-bugs@lists.postgresql.org>.
$

PostgreSQL 13から。

$ psql --help
psql is the PostgreSQL interactive terminal.
（略）
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
$

ファイルパスのTAB補完がいい感じになった。

TAB補完でファイルパスを補完するときに、単一引用符が消えなくなった。でも、この項目ってリリースノートから漏れてる？

testdb=# CREATE TABLE test (id int, data text);
CREATE TABLE
testdb=# COPY test FROM '/tmp/h

この状態でTABを入力すると、補完されるんだけど、

testdb=# CREATE TABLE test (id int, data text);
CREATE TABLE
testdb=# COPY test FROM '/tmp/hoge.txt'

PostgreSQL 12までは、

[ec2-user@ip-10-0-1-10 ~]$ ~/pgsql/pgsql-12/bin/psql -p 5433 testdb
psql (12.0)
Type "help" for help.

testdb=# COPY test FROM '/tmp/h

この状態でTAB補完すると、

[ec2-user@ip-10-0-1-10 ~]$ ~/pgsql/pgsql-12/bin/psql -p 5433 testdb
psql (12.0)
Type "help" for help.

testdb=# COPY test FROM /tmp/hoge.txt

単一引用符が消えてしまって、にゃーんだった。

結果出力中のエラーをキャッチして報告する

これも、2020-05-06時点のRelease note draftにはなかった項目。篠田さんからコメントがあった内容を確認した。
コミットID b03436994bcc4909dd644fd5ae6d9a9acdf30da5
内容は以下。

psql: Catch and report errors while printing result table
Errors (for example I/O errors or disk full) while printing out result
tables were completely ignored, which could result in silently
truncated output in scripts, for example. Fix by adding some basic
error checking and reporting.

I/Oエラー状況を作るのがちょい面倒なので今回は検証はしない。

おわりに

　GUIではないけど、高機能なクライアントツールpsql、バージョンアップによって、更に使いやすくなってるのは普段psqlを使っている自分には嬉しい話です。
　みんな、もっとpsqlを使おう！

2020-05-06時点のものを参照。まだ、いくつかコメントも入っているものなので、今後、色々修正が入ってくると思います。 ↩

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

PostgreSQL 13がやってくる！(6) - psqlの改善あれこれ

はじめに

PostgreSQL 13のpsql改善概要

psqlのプロンプトに関する改善

デフォルトのプロンプトでトランザクションステータスが表示されるようになった

PROMPT2の桁揃え

psqlメタコマンドに関する改善

\gx, \g のオプション指定追加

そもそも\gとか\gxって何？

PostgreSQL 13の変更点

演算子クラス/演算子族の表示

演算子クラスの表示

演算子族の表示

演算子族に含まれる演算子の表示

演算子を実装しているプロシージャの表示

演算子を実装しているプロシージャの表示

使いみち

\dメタコマンドでPersistenceが表示された

TOASTテーブルの\d表示の性能向上(?)

\eメタコマンド終了時のクエリの表示を調整

標準エラー出力指定が可能になった。

その他

--help指定地にPostgreSQLホームページの情報が表示が追加された。

ファイルパスのTAB補完がいい感じになった。

結果出力中のエラーをキャッチして報告する

おわりに

そもそも`\g`とか`\gx`って何？