細かすぎて伝わらない PostgreSQL 14 の psql 改善項目

自己紹介 ● ぬこ＠横浜 (@nuko_yokohama) ● にゃーん ● 趣味でポスグレをやってる者だ

psql って何？ ● PostgreSQL 標準のコマンドラインインタフェース ● 対話的な操作だけでなく、ファイルを指定したバッチ的な処理も可能。 ● pg_dump のプレイン形式ファイルをリストアするときにも使う。 ● 多くのメタコマンドやタブ補完機能があって、めっさ便利。 – psql に慣れると pgAdmin4 を使う気がしなくなる（個人の感想です）。 ● ただし Windows 版 psql テメーはダメだ。 ● なので Windows 版では pgAdmin4 を使う。 psql は いいぞ

今日のお題 ● psql メタコマンドに関する改善 – \df,\do の改善 – \d[i|m|t]+ の改善 – \dt,\di の改善 – \dX の追加 – \dT の改善 – \e の改善 – \connect 改善（謎） ● タブ補完 PostgreSQL 14 でも psql の改善がいろいろ入りそう

psql メタコマンドに関する改善 \df,\do の改善

\df,\do の改善 (1) ● 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 | real | agg pg_catalog | avg | numeric | smallint | agg (7 rows) \df: extra argument "real" ignored testdb=# 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 | 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=# ● PostgreSQL 14 おわかりいただけたであろうか・・・

\df,\do の改善 (1) ● 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 | real | agg pg_catalog | avg | numeric | smallint | agg (7 rows) \df: extra argument "real" ignored testdb=# 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 | 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=# ● PostgreSQL 14 引数に関数の引数型を与えて 絞り込みができるようになった

\df,\do の改善 (2) ● 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 | || | jsonb | jsonb | jsonb | concatenate （中略） pg_catalog | || | tsvector | tsvector | tsvector | concatenate (11 rows) \doS: extra argument "jsonb" ignored \doS: extra argument "jsonb" ignored testdb=# 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) ● PostgreSQL 14 演算子の場合、同じ Name で 別の型の引数のものが多いので、 この絞り込み機能はありがたい。

psql メタコマンドに関する改善 \d[i|m|t]+ の改善

\d[i|m|t]+ の改善 (1) ● PostgreSQL 13 まで testdb=# CREATE TABLE hoge (id int, data text); CREATE TABLE testdb=# \d+ List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+------+-------+----------+-------------+------------+------------- public | hoge | table | postgres | permanent | 8192 bytes | (1 row) testdb=# testdb=# CREATE TABLE hoge (id int, data text); CREATE TABLE 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=# ● PostgreSQL 14 おわかりいただけたであろうか・・・

\d[i|m|t]+ の改善 (1) ● PostgreSQL 13 testdb=# CREATE TABLE hoge (id int, data text); CREATE TABLE testdb=# \d+ List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+------+-------+----------+-------------+------------+------------- public | hoge | table | postgres | permanent | 8192 bytes | (1 row) testdb=# testdb=# CREATE TABLE hoge (id int, data text); CREATE TABLE 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=# ● PostgreSQL 14 Access method 列が 追加された。 テーブルに関しては現状は 組み込みの method は heap のみだけど・・・

\d[i|m|t]+ の改善 (2) ● 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 | 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) インデックス (\di+) は従来から btree 以外のインデックス種別が あるので、ちょい便利。

\d[i|m|t]+ の改善 (3) ● PostgreSQL 12 まで testdb=# \d+ bar Table "public.bar" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- id | integer | | | | plain | | data1 | text | | | | extended | | data2 | text | | | | extended | | Access method: heap testdb=# \d+ bar Table "public.bar" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+---------+-----------+----------+---------+----------+-------------+--------------+------------- id | integer | | | | plain | | | data1 | text | | | | extended | pglz | | data2 | text | | | | extended | pglz | | Access method: heap ● PostgreSQL 13 おわかりいただけたであろうか・・・

\d[i|m|t]+ の改善 (3) ● PostgreSQL 12 まで testdb=# \d+ bar Table "public.bar" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+----------+--------------+------------- id | integer | | | | plain | | data1 | text | | | | extended | | data2 | text | | | | extended | | Access method: heap testdb=# \d+ bar Table "public.bar" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+---------+-----------+----------+---------+----------+-------------+--------------+------------- id | integer | | | | plain | | | data1 | text | | | | extended | pglz | | data2 | text | | | | extended | pglz | | Access method: heap ● PostgreSQL 13 TOAST 時の圧縮方式を示す Compression 列が増えた。 pglz or lz4 が表示される。 デフォルトは pglz

psql メタコマンドに関する改善 \dt,\di の改善

\dt,\di の改善 ● PostgreSQL 13 testdb=# \dt pg_toast.* Did not find any relation named "pg_toast.*". testdb=# 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) ● PostgreSQL 14 おわかりいただけたであろうか・・・

\dt,\di の改善 ● PostgreSQL 13 testdb=# \dt pg_toast.* Did not find any relation named "pg_toast.*". testdb=# 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) ● PostgreSQL 14 pg_toast.* 指定が可能になった。 もっとも、 TOAST テーブルを 調べたいケースはそんなにないかも しれないけど・・・

psql メタコマンドに関する改善 \dX の追加

\dX の追加 ● PostgreSQL 14 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+ s1 List of extended statistics Schema | Name | Definition | Ndistinct | Dependencies | MCV --------+------+--------------+-----------+--------------+----- public | s1 | a, b FROM t1 | | defined | (1 row) 拡張統計情報を表示する メタコマンドが追加された。 拡張統計情報自体は PostgreSQL 10 から 実装されている。 ● 今のところ、 \dX, \dX+ で出力される情報の差はない。

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

\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=# \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) ● PostgreSQL 14 おわかりいただけた であろうか・・・

\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=# \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) ● PostgreSQL 14 “integer” のエイリアス” int” でも \dT がヒットするようになった。

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

\e の改善 ● PostgreSQL 13 postgres=# SELECT 1; ?column? ---------- 1 (1 row) postgres=# \e postgres=# SELECT 1; ?column? ---------- 1 (1 row) postgres=# \e ● PostgreSQL 14 エディタ上で保存せずに終わると・・・ SELECT 1; ~ （中略） ~ :q! SELECT 1; ~ （中略） ~ :q! postgres=# \e ?column? ---------- 1 (1 row) postgres=# postgres=# \e postgres=# おわかりいただけた であろうか・・・

\e の改善 ● PostgreSQL 13 postgres=# SELECT 1; ?column? ---------- 1 (1 row) postgres=# \e postgres=# SELECT 1; ?column? ---------- 1 (1 row) postgres=# \e ● PostgreSQL 14 エディタ上で保存せずに終わると・・・ SELECT 1; ~ （中略） ~ :q! SELECT 1; ~ （中略） ~ :q! postgres=# \e ?column? ---------- 1 (1 row) postgres=# postgres=# \e postgres=# 保存しない場合には 直前のコマンドが実行される 保存しない場合には コマンドは実行されない

psql メタコマンドに関する改善 \connect の改善（謎）

\connect の改善（謎） ● beta1 のリリースノートの内容がきちんと理解できていない・・・ 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. $ ~/pgsql/pgsql-14b1/bin/psql -p 10014 -U user_a db_a Password for user user_a: psql (14beta1) Type "help" for help. db_a=> \connect -reuse-previous=on connection to server on socket "/tmp/.s.PGSQL.10014" failed: FATAL: password authentication failed for user "user_a" Previous connection kept db_a=> 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. \connect 実行前に、別端末から user_a のパスワードを変更した パスワードプロンプトが出るのを 期待したのだが・・・ にゃーん・・・

TAB 補完の改善

TAB 補完の改善 ● リリースノートの記述が簡潔すぎて にゃーん Improve tab completion (Vignesh C, Michael Paquier, Justin Pryzby, Georgios Kokolatos, Julien Rouhaud, ADD NAMES) ● Commitfest(2020-07 ～ 2021-03) の commited 項目から” tab completion” を地道に探す。 – Added tab completion for the missing options in copy statement – Tab completion for VACUUM of partitioned tables – tab completion of IMPORT FOREIGN SCHEMA – Feature improvement for FETCH tab completion – Feature improvement of tab completion for DEALLOCATE – Feature improvement for TRUNCATE tab completion – Feature improvement for CLOSE, FETCH, MOVE tab completion 今回は簡単そうな 2 例を紹介します。

TAB 補完の改善 ● PostgreSQL 13 $ ~/pgsql/pgsql-13/bin/psql -p 10013 -U postgres testdb psql (13.1) Type "help" for help. testdb=# COPY foo FROM '/tmp/foo.dat' BINARY CSV DELIMITER ENCODING NULL testdb=# COPY foo FROM '/tmp/foo.dat' $ ~/pgsql/pgsql-14b1/bin/psql -p 10014 -U postgres testdb psql (14beta1) Type "help" for help. testdb=# COPY foo FROM '/tmp/foo.dat' W WHERE WITH ( ● PostgreSQL 14 PostgreSQL 14 beta1 では BINARY, CSV, DELEMITER, ENCODING, NULL に加えて WHERE, WITH( が 表示されるように読めたが・・・ もしかしてバグってる？ Added tab completion for the missing options in copy statement

TAB 補完の改善 ● PostgreSQL 13 testdb=# TRUNCATE T testdb=# TRUNCATE TABLE foo information_schema. pg_catalog. pg_toast. public. testdb=# TRUNCATE TABLE foo testdb=# TRUNCATE TABLE foo $ ~/pgsql/pgsql-14b1/bin/psql -p 10014 -U postgres testdb psql (14beta1) Type "help" for help. testdb=# TRUNCATE T ● PostgreSQL 14 “T” の後には何も補完されない。 Feature improvement for TRUNCATE tab completion. テーブル名の後には何も補完されない。 ここでタブをおすと・・・ testdb=# TRUNCATE TABLE テーブル名の後にも補完してくれる・・・ testdb=# TRUNCATE TABLE foo CASCADE CONTINUE IDENTITY RESTART IDENTITY RESTRICT

まとめ

今日のお題（再掲） ● psql メタコマンドに関する改善 – \df,\do の改善 – \d[i|m|t]+ の改善 – \dt,\di の改善 – \dX の追加 – \dT の改善 – \e の改善 – \connect 改善（謎） ● タブ補完 PostgreSQL 14 でも psql の改善がいろいろ入りそう やはり psql は いいぞ