はじめに

にゃーん。趣味でポスグレをやっている者だ。

この記事はPostgreSQL 16 全部ぬこ Advent Calendar 2022 6日目の記事です。

今回は外部テーブルを子テーブルとしたパーティションテーブルに対して、psqlのテーブル表示メタコマンド\d+を使ったときの表示のちょっとした改善内容について書いてみます。

概要

項目	内容
タイトル	[patch] Have psql's \d+ indicate foreign partitions
Topic	Clients
ステータス	commited
Last modified	2022-11-08
概要	子テーブルに外部テーブルを使用した時の`\d+`表示の改善。

変更内容

PostgreSQLのパーティションテーブルの子テーブルとして、外部テーブルを指定することができますが、PostgreSQL 15までのpsqlの\d+メタコマンドでは、外部テーブルでない通常の子テーブルも外部テーブルも区別できない表示になっていました。
PostgreSQL 16ではこれを明確に区別できるように\d+メタコマンドの表示を変更しています。

確認手順

以下の手順でパーティション＋外部テーブルを構築し、\d+ citiesメタコマンドでの表示を行います。

データベースを2つ作成(local, remote)します。
localデータベースには、パーティション親テーブルcities、外部子テーブルcities_kanagawa、外部子テーブルcities_tokyo、通常子テーブルcities_elseの3つの小テーブルを作成します。
citiesはprefというパーティションキーによるリストパーティションとして構成します。
remoteデータベースには、cities_kanagawaテーブルとcities_tokyoテーブルを作成します。このテーブルに対して、localテーブルからpostgres_fdwで接続します。
構築後に、\d+ citiesを実行して表示を確認します。

構築したパーティション＋外部テーブルのイメージはこんな感じになります。

これを、PostgreSQL 15とPostgreSQL 16で比較します。

PostgreSQL 15

PostgreSQL 15ではこんな感じの出力になります。

$ ~/pgsql/pgsql-15/bin/psql part_fdw -c "\d+ pgbench_accounts"
                                   Partitioned table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
 aid      | integer       |           |          |         | plain    |             |              |
 bid      | integer       |           |          |         | plain    |             |              |
 abalance | integer       |           |          |         | plain    |             |              |
 filler   | character(84) |           |          |         | extended |             |              |
Partition key: HASH (aid)
Partitions: a4 FOR VALUES WITH (modulus 4, remainder 3),
            f_a1 FOR VALUES WITH (modulus 4, remainder 0),
            f_a2 FOR VALUES WITH (modulus 4, remainder 1),
            f_a3 FOR VALUES WITH (modulus 4, remainder 2)

f_a1～f_a3は外部テーブル、a4は通常のテーブルですが、特に表示上の区別はありません。

PostgreSQL 16

PostgreSQL 16ではこんな感じの出力になります。

 ~/pgsql/master/bin/psql part_fdw -c "\d+ pgbench_accounts"
                                   Partitioned table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
 aid      | integer       |           |          |         | plain    |             |              |
 bid      | integer       |           |          |         | plain    |             |              |
 abalance | integer       |           |          |         | plain    |             |              |
 filler   | character(84) |           |          |         | extended |             |              |
Partition key: HASH (aid)
Partitions: a4 FOR VALUES WITH (modulus 4, remainder 3),
            f_a1 FOR VALUES WITH (modulus 4, remainder 0), FOREIGN,
            f_a2 FOR VALUES WITH (modulus 4, remainder 1), FOREIGN,
            f_a3 FOR VALUES WITH (modulus 4, remainder 2), FOREIGN

f_a1～f_a3は外部テーブルを示すFOREIGNが末尾に表示されます。a4は通常のテーブルなので末尾にはテーブル種別を示すものは表示されません。
MLを読むと、外部サーバの定義として、ホスト名を設定するとホスト名の情報も出力されるようです。

おわりに

今回はpsqlの\d+メタコマンドのちょっとした改善内容を紹介しました。
パーティション＋外部テーブルによるシャーディング環境を構築する場合の確認が楽になりそうな改善ですね。

参考

検証に使ったSQLスクリプトを以下に示します。

CREATE EXTENSION postgres_fdw;

DROP TABLE pgbench_accounts CASCADE;
DROP TABLE a1;
DROP TABLE a2;
DROP TABLE a3;
DROP TABLE a4;

-- remote tables
CREATE TABLE a1(aid integer, bid integer, abalance integer, filler character(84));
CREATE TABLE a2(aid integer, bid integer, abalance integer, filler character(84));
CREATE TABLE a3(aid integer, bid integer, abalance integer, filler character(84));

-- local parent table
CREATE TABLE pgbench_accounts (aid integer, bid integer, abalance integer, filler character(84))
  PARTITION BY HASH (aid);

CREATE SERVER loopback foreign data wrapper postgres_fdw options (port '16001', dbname 'part_fdw');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;

-- local child foreign tables
CREATE FOREIGN TABLE f_a1
  PARTITION OF pgbench_accounts
  FOR VALUES WITH (MODULUS 4, REMAINDER 0)
  SERVER loopback
  OPTIONS (table_name 'a1');

CREATE FOREIGN TABLE f_a2
  PARTITION OF pgbench_accounts
  FOR VALUES WITH (MODULUS 4, REMAINDER 1)
  SERVER loopback
  OPTIONS (table_name 'a2');

CREATE FOREIGN TABLE f_a3
  PARTITION OF pgbench_accounts
  FOR VALUES WITH (MODULUS 4, REMAINDER 2)
  SERVER loopback
  OPTIONS (table_name 'a3');

-- local table
CREATE TABLE a4
  PARTITION OF pgbench_accounts
  FOR VALUES WITH (MODULUS 4, REMAINDER 3)
;

PG16:[patch] Have psql's \d+ indicate foreign partitions