More than 3 years have passed since last update.

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

Last updated at 2021-06-06Posted at 2021-05-31

#はじめに
にゃーん。
PostgreSQL 14でもpsqlの改善項目がいくつか入っているので、今回はそれを調査してみた。
（一部未調査）

概要

2021-05-31時点のPostgreSQL 14リリースノートを見ると、以下の個の修正項目が挙げられている。

\df,\doメタコマンドの改善
\d[i|m|t]+メタコマンドの出力にアクセスメソッドを出力
\dt,\diでTOAST テーブルとそのインデックスを表示
拡張統計オブジェクトを一覧表示する\dXメタコマンドを追加
\dTを修正して、integerのintなど、配列構文とバックエンドの文法エイリアスを解釈する
\eで編集するとき、または\efと\evを使用するとき、エディターが保存せずに終了した場合は内容を無視する
\connect -reuse-previousの改善
タブ補完の改善

改善内容の詳細

`\df`,`\do`メタコマンドの改善

関数と演算子の引数の型も指定できるようになった。

`\df`（引数型による関数の絞り込み）

関数の場合、\dfメタコマンドで一覧を表示するが、同名で引数の型が異なる汎用的な関数の場合、引数の型を指定することで、絞り込みが可能になった。

PostgreSQL 13

testdb=# \df pg_catalog.avg real
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 pg_catalog | avg  | numeric          | bigint              | agg
 pg_catalog | avg  | double precision | double precision    | agg
 pg_catalog | avg  | numeric          | integer             | agg
 pg_catalog | avg  | interval         | interval            | agg
 pg_catalog | avg  | numeric          | numeric             | agg
 pg_catalog | avg  | double precision | real                | agg
 pg_catalog | avg  | numeric          | smallint            | agg
(7 rows)

\df: extra argument "real" ignored
testdb=#

PostgreSQL 13では、\dfに引数を与えても無視されて（全ての引数の型の）関数が表示される。

PostgreSQL 14

testdb=# \df pg_catalog.avg
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 pg_catalog | avg  | numeric          | bigint              | agg
 pg_catalog | avg  | double precision | double precision    | agg
 pg_catalog | avg  | numeric          | integer             | agg
 pg_catalog | avg  | interval         | interval            | agg
 pg_catalog | avg  | numeric          | numeric             | agg
 pg_catalog | avg  | double precision | real                | agg
 pg_catalog | avg  | numeric          | smallint            | agg
(7 rows)

testdb=# \df pg_catalog.avg real
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 pg_catalog | avg  | double precision | real                | agg
(1 row)

testdb=#

引数にデータ型を与えると、そのデータ側の関数の情報のみ表示する。

`\do`（引数型による演算子の絞り込み）

演算子の場合、\doメタコマンド（組み込みの演算子の場合は\doS）で一覧を表示するが、同名で引数の型が異なる汎用的な演算子の場合、引数の型を指定することで、絞り込みが可能になった。
同名の演算子で型が異なるものは、非常に多いので、この絞り込み機能はあると便利そう（特に、自分でデータタイプを作成して、汎用的な演算子を作っている場合、ピンポイントで確認できるようになるので）

PostgreSQL 13

testdb=# \doS || jsonb jsonb
                                           List of operators
   Schema   | Name | Left arg type | Right arg type | Result type |             Description
------------+------+---------------+----------------+-------------+-------------------------------------
 pg_catalog | ||   | anyarray      | anyarray       | anyarray    | concatenate
 pg_catalog | ||   | anyarray      | anyelement     | anyarray    | append element onto end of array
 pg_catalog | ||   | anyelement    | anyarray       | anyarray    | prepend element onto front of array
 pg_catalog | ||   | anynonarray   | text           | text        | concatenate
 pg_catalog | ||   | bit varying   | bit varying    | bit varying | concatenate
 pg_catalog | ||   | bytea         | bytea          | bytea       | concatenate
 pg_catalog | ||   | jsonb         | jsonb          | jsonb       | concatenate
 pg_catalog | ||   | text          | anynonarray    | text        | concatenate
 pg_catalog | ||   | text          | text           | text        | concatenate
 pg_catalog | ||   | tsquery       | tsquery        | tsquery     | OR-concatenate
 pg_catalog | ||   | tsvector      | tsvector       | tsvector    | concatenate
(11 rows)

\doS: extra argument "jsonb" ignored
\doS: extra argument "jsonb" ignored
testdb=#

PostgreSQL 14

testdb=# \doS+ ||
                                                            List of operators
   Schema   | Name |   Left arg type    |   Right arg type   |    Result type     |    Function     |             Description
------------+------+--------------------+--------------------+--------------------+-----------------+-------------------------------------
 pg_catalog | ||   | anycompatible      | anycompatiblearray | anycompatiblearray | array_prepend   | prepend element onto front of array
 pg_catalog | ||   | anycompatiblearray | anycompatible      | anycompatiblearray | array_append    | append element onto end of array
 pg_catalog | ||   | anycompatiblearray | anycompatiblearray | anycompatiblearray | array_cat       | concatenate
 pg_catalog | ||   | anynonarray        | text               | text               | anytextcat      | concatenate
 pg_catalog | ||   | bit varying        | bit varying        | bit varying        | bitcat          | concatenate
 pg_catalog | ||   | bytea              | bytea              | bytea              | byteacat        | concatenate
 pg_catalog | ||   | jsonb              | jsonb              | jsonb              | jsonb_concat    | concatenate
 pg_catalog | ||   | text               | anynonarray        | text               | textanycat      | concatenate
 pg_catalog | ||   | text               | text               | text               | textcat         | concatenate
 pg_catalog | ||   | tsquery            | tsquery            | tsquery            | tsquery_or      | OR-concatenate
 pg_catalog | ||   | tsvector           | tsvector           | tsvector           | tsvector_concat | concatenate
(11 rows)

testdb=# \doS+ || jsonb jsonb
                                       List of operators
   Schema   | Name | Left arg type | Right arg type | Result type |   Function   | Description
------------+------+---------------+----------------+-------------+--------------+-------------
 pg_catalog | ||   | jsonb         | jsonb          | jsonb       | jsonb_concat | concatenate
(1 row)

`\d[i|m|t]+`メタコマンドの出力にアクセスメソッドを出力

PostgreSQL 12から導入されたテーブルアクセスメソッドを、\d+メタコマンドで表示できるようになった。

テーブルの例

PostgreSQL 13

testdb=# CREATE TABLE hoge (id int, data text);
CREATE TABLE
testdb=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | hoge | table | postgres
(1 row)

testdb=# \d+
                             List of relations
 Schema | Name | Type  |  Owner   | Persistence |    Size    | Description
--------+------+-------+----------+-------------+------------+-------------
 public | hoge | table | postgres | permanent   | 8192 bytes |
(1 row)

testdb=#

PostgreSQL 14

testdb=# CREATE TABLE hoge (id int, data text);
CREATE TABLE
testdb=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | hoge | table | postgres
(1 row)

testdb=# \d+
                                     List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+------+-------+----------+-------------+---------------+------------+-------------
 public | hoge | table | postgres | permanent   | heap          | 8192 bytes |
(1 row)

testdb=#

Persistence列の次にAccess method列が追加されている。
テーブルに関して言えば組み込みのテーブルアクセスんメソッドはheapのみなので、普通に使っている分にはそれほど恩恵は受けないが、別のテーブルアクセスメソッドを開発して組み込む場合には便利そうだ。

インデックスの例

インデックスに関しては以前から多様なアクセスメソッドが組み込まれているので、この機能で見分けがつくようになったのは便利かも。

PostgreSQL 13

testdb=# CREATE TABLE foo (id int, data text, ts timestamp);
CREATE TABLE
testdb=# CREATE INDEX foo_id_idx ON foo (id);
CREATE INDEX
testdb=# CREATE INDEX foo_ts_idx ON foo USING brin(ts);
CREATE INDEX
testdb=# \di
               List of relations
 Schema |    Name    | Type  |  Owner   | Table
--------+------------+-------+----------+-------
 public | foo_id_idx | index | postgres | foo
 public | foo_ts_idx | index | postgres | foo
(2 rows)

testdb=# \di+
                                    List of relations
 Schema |    Name    | Type  |  Owner   | Table | Persistence |    Size    | Description
--------+------------+-------+----------+-------+-------------+------------+-------------
 public | foo_id_idx | index | postgres | foo   | permanent   | 8192 bytes |
 public | foo_ts_idx | index | postgres | foo   | permanent   | 48 kB      |
(2 rows)

PostgreSQL 14

testdb=# CREATE TABLE foo (id int, data text, ts timestamp);
CREATE TABLE
testdb=# CREATE INDEX foo_id_idx ON foo (id);
CREATE INDEX
testdb=# CREATE INDEX foo_ts_idx ON foo USING brin(ts);
CREATE INDEX
testdb=# \di
               List of relations
 Schema |    Name    | Type  |  Owner   | Table
--------+------------+-------+----------+-------
 public | foo_id_idx | index | postgres | foo
 public | foo_ts_idx | index | postgres | foo
(2 rows)

testdb=# \di+
                                            List of relations
 Schema |    Name    | Type  |  Owner   | Table | Persistence | Access method |    Size    | Description
--------+------------+-------+----------+-------+-------------+---------------+------------+-------------
 public | foo_id_idx | index | postgres | foo   | permanent   | btree         | 8192 bytes |
 public | foo_ts_idx | index | postgres | foo   | permanent   | brin          | 48 kB      |
(2 rows)

テーブルと同様にPostgreSQL 14では、Persistence列の次にAccess method列が追加される。

自分の場合、これまではインデックス名に(btree以外の場合)アクセスメソッド名も付与して区別していたけど、今後はアクセスメソッド名をインデックス名に含めなくても良くなるかもしれない。

`\dt`,`\di`でTOAST テーブルとそのインデックスを表示

PostgreSQL 13までは、\dtメタコマンドで、pg_toastスキーマ内のTOASTテーブルの一覧を参照できなかった。

testdb=# \dt pg_toast.*
Did not find any relation named "pg_toast.*".
testdb=#

PostgreSQL 14からは\dtメタコマンドでpg_toastスキーマ内のTOASTテーブルの一覧が参照可能になった。

testdb=# \dt pg_toast.*
                 List of relations
  Schema  |      Name      |    Type     |  Owner
----------+----------------+-------------+----------
 pg_toast | pg_toast_1213  | TOAST table | postgres
 pg_toast | pg_toast_1247  | TOAST table | postgres
（中略）
 pg_toast | pg_toast_826   | TOAST table | postgres
(39 rows)

拡張統計情報を一覧表示する`\dX`メタコマンドを追加

拡張統計情報はCREATE STATISTICSで定義するが、その定義した拡張統計情報の一覧を表示する\dxメタコマンドが追加された。

PostgreSQL 13ではこのメタコマンド自体が存在しないため、エラーとなる。

testdb=# \dX
invalid command \dX
Try \? for help.
testdb=#

PostgreSQL 14上で拡張統計情報を作成して、\dxメタコマンドで表示する。

testdb=# CREATE TABLE t1 (a int, b int);
CREATE TABLE
testdb=# INSERT INTO t1 SELECT i/100, i/500 FROM generate_series(1,1000000) s(i);
INSERT 0 1000000
testdb=# CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
CREATE STATISTICS
testdb=# ANALYZE t1;
ANALYZE
testdb=# \dX
                  List of extended statistics
 Schema | Name |  Definition  | Ndistinct | Dependencies | MCV
--------+------+--------------+-----------+--------------+-----
 public | s1   | a, b FROM t1 |           | defined      |
(1 row)

testdb=# \dX+
                  List of extended statistics
 Schema | Name |  Definition  | Ndistinct | Dependencies | MCV
--------+------+--------------+-----------+--------------+-----
 public | s1   | a, b FROM t1 |           | defined      |
(1 row)

testdb=# \dX s1
                  List of extended statistics
 Schema | Name |  Definition  | Ndistinct | Dependencies | MCV
--------+------+--------------+-----------+--------------+-----
 public | s1   | a, b FROM t1 |           | defined      |
(1 row)

testdb=# \dX+ s1
                  List of extended statistics
 Schema | Name |  Definition  | Ndistinct | Dependencies | MCV
--------+------+--------------+-----------+--------------+-----
 public | s1   | a, b FROM t1 |           | defined      |
(1 row)

現状は\dXと\dX+で表示される情報に差異はない。

`\dT`を修正して、`integer`の`int`など、配列構文とバックエンドの文法エイリアスを解釈する

\dTメタコマンド（データ型の情報を表示する）は、引数としてデータ型を指定することができるが、そのデータ型のエイリアスも受け付けるように修正した、ということっぽい。

PostgreSQL 13

testdb-# \dT integer
                           List of data types
   Schema   |  Name   |                   Description
------------+---------+-------------------------------------------------
 pg_catalog | integer | -2 billion to 2 billion integer, 4-byte storage
(1 row)

testdb-# \dT int
     List of data types
 Schema | Name | Description
--------+------+-------------
(0 rows)

testdb-#

integerを指定するとその情報は表示するけど、integerのエイリアスであるintを指定すると何も表示されない。

PostgreSQL 14

testdb=# \dT integer
                           List of data types
   Schema   |  Name   |                   Description
------------+---------+-------------------------------------------------
 pg_catalog | integer | -2 billion to 2 billion integer, 4-byte storage
(1 row)

testdb=# \dT int
                           List of data types
   Schema   |  Name   |                   Description
------------+---------+-------------------------------------------------
 pg_catalog | integer | -2 billion to 2 billion integer, 4-byte storage
(1 row)

testdb=#

integerでもintでも表示されるようになった。

`\e`で編集するとき、または`\ef`と`\ev`を使用するとき、エディターが保存せずに終了した場合は内容を無視する

PostgreSQL 13までは、\eメタコマンドでエディタを起動して、そのエディタ上で保存せずに終了したとき(viだと:q!で終了したときなど)には、直前のコマンドが引き継がれて実行されてしまう。

testdb=# \e
 ?column? |              now
----------+-------------------------------
        1 | 2021-05-31 16:37:15.415341+09
(1 row)

testdb=# \e
 ?column? |              now
----------+-------------------------------
        1 | 2021-05-31 16:37:20.243287+09
(1 row)

testdb=#

上記の例では2回目の\eメタコマンドで起動したエディタを、保存せずに終了したときの挙動となる。

PostgtreSQL 14

testdb=# \e
 ?column? |              now
----------+-------------------------------
        1 | 2021-05-31 16:37:47.245875+09
(1 row)

testdb=# \e
testdb=#

PostgreSQL 14でも同じように2回めの\eメタコマンドで起動したエディタを保存せずに終了する。PostgreSQL 14では2回目の\eメタコマンドは何も実行されない。

なお、2回目の\eメタコマンドで起動したエディタを編集中に保存し、その後に終了（viで言えば、作業途中で:wで保存し、その後に\qする）場合には、途中の:wで保存されたコマンドが実行される。

`\connect -reuse-previous`の改善

TODO:別途再調査

リリースノート抜粋

Improve psql's handling of \connect with -reuse-previous (Tom Lane)

Specifically, properly reuse the password previously specified, and prompt for a new password if the previous one failed.

リリースノートの内容を自分なりに解釈すると、いったん\cで接続した状態で、別ターミナルからそのユーザのパスワードを変更し、再び同じユーザで接続するために\c -reuse-previousを実行すると、パスワードが異なるため認証に失敗し、パスワード再入力のプロンプトが出るのかな、と思ったんだけど、単に認証エラーになるだけでパスワード再入力のプロンプトが出ては来なかった。
確認方法が間違っているのだろうなあ・・・。

タブ補完の改善

ここの詳細はリリースノートには記述されていない。このため、過去のCommitfest(2020-07～202103）から洗い出してみる。
※現時点(2021-05-31)では動作確認は未。

Commitfest 2020-07

ここではTAB補完に関するcommitはない。

Commitfest 2020-09

Commitfest 2020-11

Commitfest 2021-01

ここではTAB補完に関するcommitはない。

Commitfest 2021-03

おわりに

やっぱりpsqlはいいぞ

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 14がやってくる(3) - psqlの改善

概要

改善内容の詳細

\df,\doメタコマンドの改善

\df（引数型による関数の絞り込み）

\do（引数型による演算子の絞り込み）

\d[i|m|t]+メタコマンドの出力にアクセスメソッドを出力

テーブルの例

インデックスの例

\dt,\diでTOAST テーブルとそのインデックスを表示

拡張統計情報を一覧表示する\dXメタコマンドを追加

\dTを修正して、integerのintなど、配列構文とバックエンドの文法エイリアスを解釈する

\eで編集するとき、または\efと\evを使用するとき、エディターが保存せずに終了した場合は内容を無視する

\connect -reuse-previousの改善

タブ補完の改善

Commitfest 2020-07

Commitfest 2020-09

Commitfest 2020-11

Commitfest 2021-01

Commitfest 2021-03

おわりに

`\df`,`\do`メタコマンドの改善

`\df`（引数型による関数の絞り込み）

`\do`（引数型による演算子の絞り込み）

`\d[i|m|t]+`メタコマンドの出力にアクセスメソッドを出力

`\dt`,`\di`でTOAST テーブルとそのインデックスを表示

拡張統計情報を一覧表示する`\dX`メタコマンドを追加

`\dT`を修正して、`integer`の`int`など、配列構文とバックエンドの文法エイリアスを解釈する

`\e`で編集するとき、または`\ef`と`\ev`を使用するとき、エディターが保存せずに終了した場合は内容を無視する

`\connect -reuse-previous`の改善