はじめに
そろそろPostgreSQL 12のベータ版リリースも近づいてきたので、気になった新機能をいろいろ試してみようと思う。
いつもはパラメータ差分とかシステムカタログ差分から調べるところだけど、今回は分かりやすそうな新機能である生成列(GENERATED COLUMNS)から試してみる。
調べるきっかけになったのは、4/1に流れてきた篠田さんのツイート。
GENERATED COLUMNS はこんな感じです。 postgres=> CREATE TABLE tbl (c1 INT, c2 INT, c3 INT GENERATED ALWAYS AS (c1 + c2) STORED);
生成列とは
名前のとおり、式を与えられた値が格納される列を定義することができる。いわゆる仮想列とは似てるけどちょっと違う。
（「生成列」という訳語が正しいのかどうかは自信がないけど）
開発中のPostgreSQL文書のコマンドリファレンスのCREATE TABLEのページを見ると、こんな感じの構文が追加されているようだ。
GENERATED ALWAYS AS ( generation_expr ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
とりあえず使ってみる
生成列の定義
shop_name(店の名前)とbranch(支店名)を結合したfull_nameという生成列を作成する例を試してみる。
（細かく言うと、branchがNULLの場合は、shop_nameを、NULLでない場合はshop_nameとbranchを結合した結果を返却するというもの）
test=# CREATE TABLE tbl (
id INT,
shop_name text,
branch text,
full_name text GENERATED ALWAYS AS
(CASE WHEN branch is NULL THEN shop_name ELSE (shop_name || ' ' || branch) END) STORED
);
CREATE TABLE
test=#
psqlの\dメタコマンドでtblの定義を確認する。
test=# \d tbl
Table "public.tbl"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------------------------------------------
id | integer | | |
shop_name | text | | |
branch | text | | |
full_name | text | | | generated always as ( +
| | | | CASE +
| | | | WHEN branch IS NULL THEN shop_name +
| | | | ELSE (shop_name || ' '::text) || branch+
| | | | END) stored
test=#
Default指定として、生成列指定時の式が表示されている。
データ挿入と検索
作成したtblテーブルにデータを挿入してみる。二郎と天下一品には支店名を指定、吉村家の場合には、支店名をNULLを設定する。
test=# INSERT INTO tbl VALUES
test-# (1, '二郎', '関内店'), (2, '吉村家', NULL), (3, '天下一品', '横浜駅西口店');
INSERT 0 3
挿入できたので検索。
test=# \pset null (null)
Null display is "(null)".
test=# SELECT * FROM tbl;
id | shop_name | branch | full_name
----+-----------+--------------+-----------------------
1 | 二郎 | 関内店 | 二郎 関内店
2 | 吉村家 | (null) | 吉村家
3 | 天下一品 | 横浜駅西口店 | 天下一品 横浜駅西口店
(3 rows)
full_name列に、定義した式の結果が表示されている。
生成列にインデックスを設定
じゃあ、生成列に対してインデックスって設定できるのか？というのを試してみる。
CREATE INDEX文でインデックスを定義して、検索時の実行計画を見てみる。
test=# CREATE INDEX full_name_idx ON tbl (full_name);
CREATE INDEX
test=# SET enable_seqscan = off;
SET
test=# EXPLAIN SELECT * FROM tbl WHERE full_name = '吉村家';
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using full_name_idx on tbl (cost=0.13..8.15 rows=1 width=100)
Index Cond: (full_name = '吉村家'::text)
(2 rows)
test=#
仮想列であってもインデックスはフツーに設定できるもよう。まあ、式インデックスができるんだから、同じように仮想列の式に対してもインデックスは設定できるんだろうな・・・と思い込んでいたのだが実は・・・。
生成列の定義はどこにある？
最初、この機能はメタデータに指定された式を使って検索時に値を生成するもの、だと思いこんでいたが、データファイルの中を見ると、どうやらデータファイル内に生成されたデータ実体が存在するっぽい。
まず、生成列に指定した式は、どこのシステムカタログにあるかを探してみる。
どうやら、pg_attdefというシステムカタログにあるようだ。
確認のため、pg_attributeシステムカタログとpg_classシステムカタログを結合して、生成列が存在するテーブルの列情報を表示してみる。
test=# SELECT c.oid, c.relname, a.attnum, a.attrelid, a.attname, a.attgenerated
FROM pg_attribute a JOIN pg_class c ON (c.oid = a.attrelid)
WHERE relname = 'tbl' AND a.attnum > 0
;
oid | relname | attnum | attrelid | attname | attgenerated
-------+---------+--------+----------+-----------+--------------
16427 | tbl | 1 | 16427 | id |
16427 | tbl | 2 | 16427 | shop_name |
16427 | tbl | 3 | 16427 | branch |
16427 | tbl | 4 | 16427 | full_name | s
(4 rows)
attgenerated列が's'になっている列が生成列を示すものだろう（たぶん）。
この列は列番号(attnum)が4なので、リレーションのoidと列番号を使って、fill_nameの列のデフォルト値(を導く式)を検索してみる。
test=# SELECT ad.oid, pg_get_expr(ad.adbin, ad.adrelid)
FROM pg_class c JOIN pg_attrdef ad ON (c.oid = ad.adrelid)
WHERE ad.adnum = 4
;
oid | pg_get_expr
-------+-----------------------------------------------
16430 | +
| CASE +
| WHEN (branch IS NULL) THEN shop_name +
| ELSE ((shop_name || ' '::text) || branch)+
| END
(1 row)
なんか、CREATE TABLEに指定した式が出力された。なお、関数get_get_expr()を指定せずに、adbinを出力しても、とっても読みづらい式を示す何かが表示されてツライことになる。
なお、この調査をしてわかったが、PostgreSQL 12から、pg_attdefのaddsrc列は削除されたようだ。なので、pg_get_expr(pg_attdefのoid, pg_attdefが属するpg_classのoid)関数を使って、デフォルト値を導出する式を出力する必要がある。
生成列の実体はどこにある？
生成列の定義がどこにあるかはわかった。では、生成列に指定した式によって生成されたデータはどこにあるのか？
ということで、生成列を定義したtestデータベースのtblテーブルのリレーションファイルの内容をhexdumpで表示してみる。
（見やすくするために、TRUNCATE後に、英字のテキストデータを再度INSERTした）
$ psql -U postgres test -c "SELECT * FROM tbl"
id | shop_name | branch | full_name
----+--------------+----------+---------------
1 | Jiro | Kannai | Jiro Kannai
2 | Yoshimura Ya | | Yoshimura Ya
3 | Jiro | Kawasaki | Jiro Kawasaki
(3 rows)
$
データ挿入後1、testデータベースのoidと、そのデータベース内のtblテーブルのリレーションファイル(base/16384/16439)をダンプするとこうなる。
$ psql -U postgres test -c "SELECT oid FROM pg_database WHERE datname = 'test'"
oid
-------
16384
(1 row)
$ psql -U postgres test -c "SELECT pg_relation_filenode('tbl')"
pg_relation_filenode
----------------------
16439
(1 row)
$ hexdump -C /data/12-devel/base/16384/16439
00000000 00 00 00 00 90 f2 6e 01 00 00 00 00 24 00 58 1f |......n.....$.X.|
00000010 00 20 04 20 00 00 00 00 c8 9f 68 00 90 9f 6c 00 |. . ......h...l.|
00000020 58 9f 70 00 00 00 00 00 00 00 00 00 00 00 00 00 |X.p.............|
00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00001f50 00 00 00 00 00 00 00 00 f7 01 00 00 00 00 00 00 |................|
00001f60 00 00 00 00 00 00 00 00 03 00 04 00 02 08 18 00 |................|
00001f70 03 00 00 00 0b 4a 69 72 6f 13 4b 61 77 61 73 61 |.....Jiro.Kawasa|
00001f80 6b 69 1d 4a 69 72 6f 20 4b 61 77 61 73 61 6b 69 |ki.Jiro Kawasaki|
00001f90 f7 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00001fa0 02 00 04 00 03 08 18 0b 02 00 00 00 1b 59 6f 73 |.............Yos|
00001fb0 68 69 6d 75 72 61 20 59 61 1b 59 6f 73 68 69 6d |himura Ya.Yoshim|
00001fc0 75 72 61 20 59 61 00 00 f7 01 00 00 00 00 00 00 |ura Ya..........|
00001fd0 00 00 00 00 00 00 00 00 01 00 04 00 02 08 18 00 |................|
00001fe0 01 00 00 00 0b 4a 69 72 6f 0f 4b 61 6e 6e 61 69 |.....Jiro.Kannai|
00001ff0 19 4a 69 72 6f 20 4b 61 6e 6e 61 69 00 00 00 00 |.Jiro Kannai....|
00002000
$
テーブルファイル内に、生成列のデータ(Jiro KannaiやJiro Kawasaki)が含まれていることがわかる。
なので（試してはないが）、大量のレコードが格納されたテーブル対して、ALTER TABLEで生成列を追加した場合には、システムカタログの変更だけではなく、データ実体も変更される（実質的に全レコードのデータを更新することになる？）ため、かなり処理時間がかかりそうである。
おわりに
まだ、ベータ版リリース前だけど、ざっと生成列について調べてみた。
仮想列と同じノリで使ってはいけないようだ・・・。
例えとして適切かどうかはわからないけど、仮想列と生成列は、ビューとマテリアライズドビュー、みたいなものかな。
-
ダンプする前にCHECKPOINTを実行しておくこと。 ↩