Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PostgreSQL 14 - Improve psql

PostgreSQL 14 - Improve psql

Improvements to psql in PostgreSQL 14

nuko_yokohama

June 07, 2021
Tweet

More Decks by nuko_yokohama

Other Decks in Technology

Transcript

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

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

  3. psql って何? • PostgreSQL 標準のコマンドラインインタフェース • 対話的な操作だけでなく、ファイルを指定したバッチ的な処理も可能。 • pg_dump のプレイン形式ファイルをリストアするときにも使う。

    • 多くのメタコマンドやタブ補完機能があって、めっさ便利。 – psql に慣れると pgAdmin4 を使う気がしなくなる(個人の感想です)。 • ただし Windows 版 psql テメーはダメだ。 • なので Windows 版では pgAdmin4 を使う。 psql は いいぞ
  4. 今日のお題 • psql メタコマンドに関する改善 – \df,\do の改善 – \d[i|m|t]+ の改善

    – \dt,\di の改善 – \dX の追加 – \dT の改善 – \e の改善 – \connect 改善(謎) • タブ補完 PostgreSQL 14 でも psql の改善がいろいろ入りそう
  5. psql メタコマンドに関する改善 \df,\do の改善

  6. \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 おわかりいただけたであろうか・・・
  7. \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 引数に関数の引数型を与えて 絞り込みができるようになった
  8. \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 で 別の型の引数のものが多いので、 この絞り込み機能はありがたい。
  9. psql メタコマンドに関する改善 \d[i|m|t]+ の改善

  10. \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 おわかりいただけたであろうか・・・
  11. \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 のみだけど・・・
  12. \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 以外のインデックス種別が あるので、ちょい便利。
  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 おわかりいただけたであろうか・・・
  14. \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
  15. psql メタコマンドに関する改善 \dt,\di の改善

  16. \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 おわかりいただけたであろうか・・・
  17. \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 テーブルを 調べたいケースはそんなにないかも しれないけど・・・
  18. psql メタコマンドに関する改善 \dX の追加

  19. \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+ で出力される情報の差はない。
  20. psql メタコマンドに関する改善 \dT の改善

  21. \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 おわかりいただけた であろうか・・・
  22. \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 がヒットするようになった。
  23. psql メタコマンドに関する改善 \e の改善

  24. \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=# おわかりいただけた であろうか・・・
  25. \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=# 保存しない場合には 直前のコマンドが実行される 保存しない場合には コマンドは実行されない
  26. psql メタコマンドに関する改善 \connect の改善(謎)

  27. \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 のパスワードを変更した パスワードプロンプトが出るのを 期待したのだが・・・ にゃーん・・・
  28. TAB 補完の改善

  29. 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 例を紹介します。
  30. 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
  31. 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
  32. まとめ

  33. 今日のお題(再掲) • psql メタコマンドに関する改善 – \df,\do の改善 – \d[i|m|t]+ の改善

    – \dt,\di の改善 – \dX の追加 – \dT の改善 – \e の改善 – \connect 改善(謎) • タブ補完 PostgreSQL 14 でも psql の改善がいろいろ入りそう やはり psql は いいぞ