はじめに
にゃーん。趣味でポスグレをやっている者だ。
この記事はPostgreSQL アドベントカレンダー 2022の17日目の記事です。
昨日のアドベントカレンダーはKei Morishitaさんの今年１年、alloyDBに魅せられた話という記事でした。
AlloyDB、無料お試し期間の間にもっときちんと使っておけば良かったなあ・・・
この記事の概要
この記事は、差分を即時更新するマテリアライズド・ビュー機能を提供するPostgreSQL拡張機能pg_ivmの最新バージョン (1.3)をちょいと試した内容を書いています。
pg_ivm自体は最初の版がリリースされた直後（pg_ivm(IMMV)を使ってみた(1)参照）や、バージョン1.1リリース後（pg_ivm(IMMV)を使ってみた(2) 1.1での改善点など参照）に検証してました。
少し前に新しい版(1.3)がリリースされたというのもあったので、pg_ivmを久々に使って検証した内容を書きたいと思います。
pg_ivmとは
pg_ivmはIncremental View Maintenance(IVM)をPostgreSQLで実現するための拡張機能です。
Incremental View Maintenanceというのは、雑に一言でいうと「更新に追随するMaterilized View」を実現するものです。
pgsql-ivmとの関係
Incremental View Mainrtenanceの機能は数年前から、SRA OSS LCCの方が主体となって、PostgreSQL本体のコードをForkして開発を続けています。
https://github.com/sraoss/pgsql-ivm
ただ、この機能はまだ本体にコミットされていないため、上記のリポジトリからソースを入手してビルドをする必要があり、利用する敷居が高いです。
そこで、Incremental View Mainrtenanceの機能を拡張機能として独立させ、PostgreSQLの安定版バージョンに組み込んで使ったもらおう、ということでこの拡張機能がリリースされたようです。
(という話をPostgreSQL Conference 2022の長田さんの講演で聞いた）
pg_ivmのバージョンと対応PostgreSQLバージョン
pg\ivm現時点での最新バージョンは1.3(2022-09-30 リリース)です。
RPMパッケージ/yumリポジトリも公開されており、本記事を作成した時点では、PostgreSQL 13～15対応のパッケージがあることは確認しました。
なお、本記事では手元の環境でPostgreSQL 15用にビルドしたものを使用して検証をします。
簡単な動作検証
インストール
今回はリポジトリをcloneして、その上でビルドを行いPostgreSQL 15環境にpg_ivmをビルドインストールします。
git pull https://github.com/sraoss/pg_ivm
make USE_PGXS=1
make USE_PGXS=1 install
データベースへの組み込み
pg_ivmを使う場合には、shared_preload_librariesの設定は不要ですが、使用するデータベースにCREATE EXTENSIONコマンドで組み込む必要があります。
以下はivmというデータベースにpg_ivmを組み込む例です。
$ psql ivm -c "CREATE EXTENSION pg_ivm"
CREATE EXTENSION
$ psql ivm -c "\dx"
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+--------------------------------------------
pg_ivm | 1.3 | pg_catalog | incremental view maintenance on PostgreSQL
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
$
pg_ivmが提供する関数
pg_ivmでは以下のIMMV(Incrementally Maintainable Materialized View)を管理するSQL関数を提供しています。
$ psql testdb -c "\df *immv*"
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------------------------+------------------+---------------------+------
pg_catalog | IVM_prevent_immv_change | trigger | | func
pg_catalog | create_immv | bigint | text, text | func
pg_catalog | get_immv_def | text | immvrelid regclass | func
pg_catalog | refresh_immv | bigint | text, boolean | func
(4 rows)
ユーザが使うのは以下の3つの関数です。
| 関数名 | 機能 |
|---|---|
| create_immv | 指定したクエリの実行結果を元にIMMVを作成する。 |
| get_immv_def | 指定したIMMVの元になったクエリ情報を表示する。 |
| refresh_immv | create_immvで指定したクエリを再実行して、IMMVの内容をリフレッシュする。 REFRESH MATERIALIZED VIEW相当の関数。 |
IMMVを作成する
IMMVの検証のために、以下の簡単なテーブルを用意します。
=# \d shops
Table "public.shops"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
pref | text | | |
name | text | | |
Indexes:
"shops_pkey" PRIMARY KEY, btree (id)
=# \d reviews
Table "public.reviews"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
sid | integer | | |
menu | text | | |
review | text | | |
Indexes:
"reviews_pkey" PRIMARY KEY, btree (id)
shopsテーブルのidとreviewsテーブルのsidは結合キーとなります。
次に、shopsテーブルとreviewsテーブルにこんな感じのデータを登録しておきます。
$ psql ivm -c "TABLE shops"
id | pref | name
-----+----------+---------------
101 | 神奈川県 | ぬこぬこ亭
102 | 東京都 | テラ盛軒 本店
103 | 神奈川県 | テラ盛軒 本店
(3 rows)
$ psql ivm -c "TABLE reviews"
id | sid | menu | review
------+-----+--------------+------------------------
1001 | 101 | ニャンマー麺 | 猫舌殺しだけど美味しい
1002 | 103 | ペタ盛り | 食べきれない
1003 | 103 | エクサ盛り | これが限界量
(3 rows)
$
この2つのテーブルをJOINして検索するクエリを例を示します。
=# SELECT s.name, r.menu,r.review FROM shops s JOIN reviews r ON (s.id = r.sid);
name | menu | review
---------------+--------------+------------------------
ぬこぬこ亭 | ニャンマー麺 | 猫舌殺しだけど美味しい
テラ盛軒 本店 | ペタ盛り | 食べきれない
テラ盛軒 本店 | エクサ盛り | これが限界量
(3 rows)
=#
上記のクエリを使ったMATERIALIZED VIEWとIMMVを作成します。
まず、MATERIALIZED VIEWから。
$ psql ivm -e -f create_mv_s_r.sql
CREATE MATERIALIZED VIEW test_mv AS
SELECT s.name, r.menu,r.review
FROM shops s JOIN reviews r ON (s.id = r.sid)
;
SELECT 3
$
IMMVを作成する場合、作成用のSQLコマンドはないので、SQL関数create_immvを使います。
第1引数には作成するIMMVの名前、第2引数にはクエリ文字列を指定します。1
vi create_immv_s_r.sql
$ psql ivm -e -f create_immv_s_r.sql
SELECT create_immv('test_immv', $$
SELECT s.name, r.menu,r.review FROM shops s JOIN reviews r ON (s.id = r.sid)
$$)
;
psql:create_immv_s_r.sql:4: NOTICE: could not create an index on immv "test_immv" automatically
DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT: Create an index on the immv for efficient incremental maintenance.
create_immv
-------------
3
(1 row)
$
IMMVは実際にはマテリアライズド・ビューではなく、テーブルとして生成されるので、psqlの\dコマンドで確認します。
=# \d test_immv
Table "public.test_immv"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
name | text | | |
menu | text | | |
review | text | | |
=#
test_immvを作成する元になったクエリの情報は、get_immv_def関数で取得できます。
=# SELECT get_immv_def('test_immv');
get_immv_def
------------------------------------------
SELECT s.name, +
r.menu, +
r.review +
FROM (shops s +
JOIN reviews r ON ((s.id = r.sid)))
(1 row)
=#
IMMVへの検索と更新
さっきのセクションでマテリアライズド・ビューとIMMVを作成したので、まず検索してみます。
$ psql ivm -c "TABLE test_mv"
name | menu | review
---------------+--------------+------------------------
ぬこぬこ亭 | ニャンマー麺 | 猫舌殺しだけど美味しい
テラ盛軒 本店 | ペタ盛り | 食べきれない
テラ盛軒 本店 | エクサ盛り | これが限界量
(3 rows)
$
当然ですが、同じ結果が返却されます。
次にクエリに使ったテーブルに対して1件レコードを挿入し、その後にマテリアライズド・ビューとIMMVを検索してみます。
$ psql ivm -c "INSERT INTO reviews VALUES (1004, 102, 'ヨタ盛り', 'この量は無理！');"
INSERT 0 1
$ psql ivm -c "TABLE test_mv"
name | menu | review
---------------+--------------+------------------------
ぬこぬこ亭 | ニャンマー麺 | 猫舌殺しだけど美味しい
テラ盛軒 本店 | ペタ盛り | 食べきれない
テラ盛軒 本店 | エクサ盛り | これが限界量
(3 rows)
$ psql ivm -c "TABLE test_immv"
name | menu | review
---------------+--------------+------------------------
ぬこぬこ亭 | ニャンマー麺 | 猫舌殺しだけど美味しい
テラ盛軒 本店 | ペタ盛り | 食べきれない
テラ盛軒 本店 | エクサ盛り | これが限界量
テラ盛軒 本店 | ヨタ盛り | この量は無理！
(4 rows)
$
マテリアライズド・ビューは元のテーブルが更新されてもREFRESH MATERIALIZED VIEWコマンドを実行しないと内容は変わりません。
しかし、IMMVはリフレッシュ相当の操作(refresh_immv関数の実行)を行わなくても、元テーブルの更新内容に応じて結果が変化しています。
IMMVの削除
pg_immvでは、IMMVを削除するためのSQL関数は提供していません。IMMVを削除するには、通常のテーブルと同様のDROP TABLEコマンドを実行します。
=# \dt test_immv
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | test_immv | table | postgres
(1 row)
=# DROP TABLE test_immv ;
DROP TABLE
=# \dt test_immv
Did not find any relation named "test_immv".
=#
PostgreSQLのいろいろな機能と組み合わせてみる
ここまで、pg_ivmを簡単に使ってみましたが、PostgreSQL自体にはいろんな機能があります。じゃあ、pg_ivmをPostgreSQLのいろいろな機能と組み合わせて使ったらどうなるのか？というのは、ぽすぐれ使いならみんな気になるところ。
じゃあ、やってみましょうか、
トリガ
pg_imv更新の検知のために、トリガを使っています。また、作成したIMMVに対しては内部的にクエリを生成して実行することで差分を反映しているらしいです。
じゃあ、IMMVを生成するクエリで使っているテーブルに、トリガによる更新をがかかったらどうなるのでしょう？
クエリ参照元を更新するトリガを設定する
reviews_audit というテーブルを追加します。このテーブルはこういう構造になっています。
$ psql ivm -c "\d reviews_audit"
Table "public.reviews_audit"
Column | Type | Collation | Nullable | Default
-----------+-----------------------------+-----------+----------+---------
operation | character(1) | | not null |
stamp | timestamp without time zone | | not null |
rid | integer | | |
$
reviewsテーブルが更新されると、reviews_auditに、operationとその時のタイムスタンプ、reviews.id の値を挿入するトリガ関数とトリガを定義します。
CREATE OR REPLACE FUNCTION reviews_audit_func() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO reviews_audit SELECT 'D', now(), OLD.id;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO reviews_audit SELECT 'U', now(), NEW.id;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO reviews_audit SELECT 'I', now(), NEW.id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER reviews_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON reviews
FOR EACH ROW EXECUTE FUNCTION reviews_audit_func();
CREATE TRIGGER
$
いったん、shopsテーブル, reviewsテーブルをTRUNCATEして、また同じレコードを挿入します。
$ psql ivm -e -f insert-1.sql
INSERT INTO shops VALUES
(101, '神奈川県','ぬこぬこ亭'),
(102, '東京都','テラ盛軒 本店'),
(103, '神奈川県','テラ盛軒 本店')
;
INSERT 0 3
INSERT INTO reviews VALUES
(1001, 101, 'ニャンマー麺', '猫舌殺しだけど美味しい'),
(1002, 103, 'ペタ盛り', '食べきれない'),
(1003, 103, 'エクサ盛り', 'これが限界量')
;
INSERT 0 3
$ psql ivm -c "TABLE reviews"
id | sid | menu | review
------+-----+--------------+------------------------
1001 | 101 | ニャンマー麺 | 猫舌殺しだけど美味しい
1002 | 103 | ペタ盛り | 食べきれない
1003 | 103 | エクサ盛り | これが限界量
(3 rows)
$ psql ivm -c "TABLE reviews_audit"
operation | stamp | rid
-----------+----------------------------+------
I | 2022-11-26 15:52:09.098195 | 1001
I | 2022-11-26 15:52:09.098195 | 1002
I | 2022-11-26 15:52:09.098195 | 1003
(3 rows)
$
reviewsテーブルだけでなく、トリガ経由でreviews_auditテーブルにもレコードが格納されていることがわかります。
次に、shopsテーブル、reviewsテーブル、reviews_auditテーブルを結合させたクエリからIMMVを作成します。
$ psql ivm -e -f create_immv_s_r_ra.sql
SELECT create_immv( 'audit_immv', $$
SELECT s.name, r.menu, ra.operation, ra.stamp
FROM
(shops s JOIN reviews r ON (s.id = r.sid))
JOIN reviews_audit ra ON (r.id = ra.rid)
$$ )
;
psql:create_immv_s_r_ra.sql:7: NOTICE: could not create an index on immv "audit_immv" automatically
DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT: Create an index on the immv for efficient incremental maintenance.
create_immv
-------------
3
(1 row)
$
作成された、audit_immvの内容を確認します。
$ psql ivm -c "TABLE audit_immv"
name | menu | operation | stamp
---------------+--------------+-----------+----------------------------
ぬこぬこ亭 | ニャンマー麺 | I | 2022-11-26 15:52:09.098195
テラ盛軒 本店 | ペタ盛り | I | 2022-11-26 15:52:09.098195
テラ盛軒 本店 | エクサ盛り | I | 2022-11-26 15:52:09.098195
(3 rows)
$
正しく生成されているようですね。
さて、この状態で、reviewsテーブルにもう１件のレコードを追加します。
個のレコード追加により、reviews_auditテーブルにはトリガ経由でoperationが'I'の列が追加されます。
そして、audit_immv にも正しく情報が反映されるはず。
$ psql ivm -c "INSERT INTO reviews VALUES (1004, 101, 'にゃんにゃん麺', '結構辛口')"
INSERT 0 1
$
reviewsrテーブル、reviews_auditテーブル、audit_immvの内容を確認します。
$ psql ivm -c "TABLE reviews"
id | sid | menu | review
------+-----+----------------+------------------------
1001 | 101 | ニャンマー麺 | 猫舌殺しだけど美味しい
1002 | 103 | ペタ盛り | 食べきれない
1003 | 103 | エクサ盛り | これが限界量
1004 | 101 | にゃんにゃん麺 | 結構辛口
(4 rows)
$ psql ivm -c "TABLE reviews_audit"
operation | stamp | rid
-----------+----------------------------+------
I | 2022-11-26 15:52:09.098195 | 1001
I | 2022-11-26 15:52:09.098195 | 1002
I | 2022-11-26 15:52:09.098195 | 1003
I | 2022-11-26 16:01:28.958416 | 1004
(4 rows)
$ psql ivm -c "TABLE audit_immv"
name | menu | operation | stamp
---------------+----------------+-----------+----------------------------
ぬこぬこ亭 | ニャンマー麺 | I | 2022-11-26 15:52:09.098195
テラ盛軒 本店 | ペタ盛り | I | 2022-11-26 15:52:09.098195
テラ盛軒 本店 | エクサ盛り | I | 2022-11-26 15:52:09.098195
ぬこぬこ亭 | にゃんにゃん麺 | I | 2022-11-26 16:01:28.958416
(4 rows)
$
結論：ベーステーブルに対して、トリガによる更新を行っても、IMMVは正しく動作する。
Materialized View
IMMV作成時に指定するクエリ内でテーブルの代わりに、Materialized Viewを使ったらどうなるのでしょうか？
これはcreate_immv実行時にエラーにしています。
現状は、VIEWやMATERIALIZED VIEWを使ったクエリは対応していないようですね。pg_ivm実装方式を想像すると、MATERIALIZED VIEWを含むクエリのサポートは難しいのだろうなあ・・・（素人考えでは通常のVIEWは許容しても良さそうに思えるけど）。
$ psql ivm -e -f create_immv_s_r-by_mv.sql
SELECT create_immv('test_immv', $$
SELECT * FROM test_mv
$$)
;
psql:create_immv_s_r-by_mv.sql:4: ERROR: VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view
$
この制約については、pg_ivmのREADMEにも書かれています。
The base tables must be simple tables. Views, materialized views, inheritance parent tables, partitioned tables, partitions, and foreign tables can not be used.
IMMVのネストは可能？
そういえば、IMMVは実装上は通常のテーブルとして作成されています（cities_immvのtypeはtableになっている）。
だとしたら、IMMVをベーステーブルとしたクエリから別のIMMVは作成できるのでは？
=# SELECT create_immv('cities_immv2', 'SELECT * FROM cities_immv');
ERROR: including IMMV in definition is not supported on incrementally maintainable materialized view
=#
ドキュメント上は明記されていない（IMMVは"must be simple tables"ではない、ということかもしれないけど）けど、現状はIMMVをベーステーブルにしたクエリからIMMVは作成できないようです。
IMMVからIMMVは作成できない、としっかりエラーメッセージに書かれていますね。
Foreign Table
READMEにも書かれているように、ベーステーブルとして外部テーブルを使った場合にも、create_immvでエラーになります。
Partition Table
PostgreSQL 10以降、CREATE TABLEコマンドにより、パーティションテーブルが作成できるようになりました。
パーティションテーブルは親（レコード実体がないテーブル）と子（レコード実体があるテーブル）に分かれています。
検索時には親テーブルを指定すると、親テーブルに属する子テーブルの内容をAppendして返却してくれます。
また、親テーブルにDMLによる更新をかけた場合には、パーティションキーの定義に従って、妥当な子テーブルに振り分けて挿入・更新・削除が行われます。
じゃあ、パーティション親テーブルを検索をかけるクエリをIMMVの作成時に指定したらどうなるのでしょうか？
親テーブルにはレコードの実体はないので、たぶんcreate_immv実行時に「パーティションテーブルは未サポートだよ！」と怒られるんじゃないかなと予想してますが・・・。
こんな感じの簡単なリストパーティションを定義します。
$ psql ivm -e -f partition-basetable.sql
CREATE TABLE cities (id int, pref text, city text) PARTITION BY LIST ( pref );
CREATE TABLE
CREATE TABLE cities_kanagawa PARTITION OF cities FOR VALUES IN ( '神奈川県' );
CREATE TABLE
CREATE TABLE cities_tokyo PARTITION OF cities FOR VALUES IN ( '東京都' );
CREATE TABLE
CREATE TABLE cities_shizuoka PARTITION OF cities FOR VALUES IN ( '静岡県' );
CREATE TABLE
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------------------+----------
public | cities | partitioned table | postgres
public | cities_kanagawa | table | postgres
public | cities_shizuoka | table | postgres
public | cities_tokyo | table | postgres
(4 rows)
親テーブルcitiesをベーステーブルとして使うクエリを使って、IMMVを作成してみます。
=# SELECT create_immv('cities_immv', $$ SELECT id, pref,city FROM cities $$);
ERROR: partitioned table is not supported on incrementally maintainable materialized view
=#
想定どおり、create_immv実行時にチェックしてエラーになってますね。
なお、pg_ivmのissueにはパーティションテーブルのサポートに関する項目は上がっていました。
→ Support to partitioned tables #20
Unlogged Table
PostgreSQLにはUnlogged Tableという更新時にWALを生成しない特殊なテーブルを作成することができます。
WALを生成しない分、更新自体は高速になりますが、クラッシュリカバリ時にはテーブル内容がTRUNCATEされます。
自宅で作っているPostgreSQL環境では、インポート用データから復旧できればOKというぬるいRPOなので、よくこのUnlogged Tableを使っています。
で、IMMVでUnlogged Tableって使えるのかしらん？
まず、簡単なUnlogged Tableを定義して適当にデータを入れておきます。
$ psql ivm -c "CREATE UNLOGGED TABLE cities (id int, pref text, city text)"
CREATE TABLE
$ psql ivm -c "INSERT INTO cities VALUES (101, '島根県','松江市')"
INSERT 0 1
$ psql ivm -c "TABLE cities"
id | pref | city
-----+--------+--------
101 | 島根県 | 松江市
(1 row)
IMMVを作成してベーステーブルにデータを挿入し、再度IMMVの内容を確認します。
$ psql ivm -c "SELECT create_immv('cities_immv', 'SELECT * FROM cities')"
NOTICE: could not create an index on immv "cities_immv" automatically
DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT: Create an index on the immv for efficient incremental maintenance.
create_immv
-------------
1
(1 row)
$ psql ivm -c "TABLE cities_immv"
id | pref | city
-----+--------+--------
101 | 島根県 | 松江市
(1 row)
$ psql ivm -c "INSERT INTO cities VALUES (102, '神奈川県','町田市')"
INSERT 0 1
$ psql ivm -c "TABLE cities_immv"
id | pref | city
-----+----------+--------
101 | 島根県 | 松江市
102 | 神奈川県 | 町田市
(2 rows)
Unlogged Tableはベーステーブルとして特に制限はかかっていないようですね。
で、ここでPostgreSQLをクラッシュ（手順上はimmediateモードで停止）して再起動します。
$ pg_ctl stop -mi -D /data/pgdata/15
waiting for server to shut down.... done
server stopped
$ pg_ctl start -D /data/pgdata/15 -l /tmp/pg15.log
waiting for server to start.... done
server started
起動後にベーステーブルとIMMVを見てみると・・・
$ psql ivm -c "TABLE cities"
id | pref | city
----+------+------
(0 rows)
$ psql ivm -c "TABLE cities_immv"
id | pref | city
-----+----------+--------
101 | 島根県 | 松江市
102 | 神奈川県 | 町田市
(2 rows)
ベーステーブルはUnlogged tableなので、クラッシュリカバリ後はTRUNCATEされています。しかし、クラッシュリカバリ時のTRUNCATEのイベントをIMMVでは拾えないので、IMMVはクラッシュリカバリ前の状態のままになっています。
こういう場合には、refresh_immv関数で手動リフレッシュするしかありません。
$ psql ivm -c "SELECT refresh_immv('cities_immv',true)"
refresh_immv
--------------
0
(1 row)
$ psql ivm -c "TABLE cities_immv"
id | pref | city
----+------+------
(0 rows)
この対応はpg_ivmの機構では難しそうので、Unlogged Tableをベーステーブルとして使用した環境で、クラッシュリカバリした場合にはrefresh_immv関数でリフレッシュするという運用対処しかできなさそうです。
ロジカルレプリケーション
ベーステーブルに対するロジカルレプリケーションの反映は、pg_ivm1.3では対応していないようです。issueには上がっています。
Logical Replication and IMV #36
ステートメントレベルトリガではなく、行レベルトリガを使って対応できるかも、というコメントもついていますね。
ALTER TABLE
PostgreSQLに限らず、一度定義したテーブルの定義情報を変更したいというケースはあると思います。PostgreSQLでもALTER TABLEコマンドでテーブルの定義を変更できます。
じゃあIMMVのベーステーブルや、作成されたIMMVに対してALTER TABLEをかけたらどうなるのかしらん？
この検証では比較のためにマテリアライズド・ビューでの挙動も見てみます。
例によって、簡単なテーブルを定義して適当にデータを入れておきます。
psql ivm -c "CREATE TABLE cities (id int, pref text, city text)"
CREATE TABLE
$ psql ivm -c "INSERT INTO cities VALUES (101, '島根県','松江市')"
INSERT 0 1
$
citiesテーブルをベーステーブルとしたMATERILAIZED VIEWとIMMVを作成します。
$ psql ivm -c "CREATE MATERIALIZED VIEW cities_mv AS SELECT * FROM cities"
SELECT 1
$ psql ivm -c "SELECT create_immv('cities_immv', 'SELECT * FROM cities')"
NOTICE: could not create an index on immv "cities_immv" automatically
DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.
HINT: Create an index on the immv for efficient incremental maintenance.
create_immv
-------------
1
(1 row)
列名の変更
この状態で、ALTER TABLEコマンドでベーステーブルcitiesのpref列の名前を、prefecturesに変更します。
=# ALTER TABLE cities RENAME COLUMN pref TO prefectures;
ALTER TABLE
=#
ALTER TABLE自体は問題なくできちゃいますね。
では、マテリアライズド・ビューやIMMVの定義は書き換わっているのかというと・・・
=# \d+ cities_mv
Materialized view "public.cities_mv"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
pref | text | | | | extended | | |
city | text | | | | extended | | |
View definition:
SELECT cities.id,
cities.prefectures AS pref,
cities.city
FROM cities;
Access method: heap
マテリアライズド・ビューはcities.prefectures AS prefというクエリに置換されているようです。
で、IMMVは
=# SELECT get_immv_def('cities_immv');
get_immv_def
--------------------------
SELECT id, +
prefectures AS pref,+
city +
FROM cities
(1 row)
こちらも同様にクエリが書き換えられてますね。ベーステーブルの列名変更は問題なさそうです。
一応、ベーステーブルに1行追加して、IMMVへの反映を確認します。
=# TABLE cities_immv ;
id | pref | city
-----+--------+--------
101 | 島根県 | 松江市
(1 row)
=# INSERT INTO cities (id, prefectures, city) VALUES (102, '神奈川県', '横浜市');
INSERT 0 1
=# TABLE cities_immv ;
id | pref | city
-----+----------+--------
101 | 島根県 | 松江市
102 | 神奈川県 | 横浜市
(2 rows)
=#
問題なさそうですね。
列の削除
今度はもうちょっと意地の悪いことをしてみます。
citiesには現在3つの列(id, prefectures, city)がありますが、ここからprefectures列を削除しちゃいましょう。
=# ALTER TABLE cities DROP COLUMN prefectures ;
ERROR: cannot drop column prefectures of table cities because other objects depend on it
DETAIL: materialized view cities_mv depends on column prefectures of table cities
table cities_immv depends on column prefectures of table cities
HINT: Use DROP ... CASCADE to drop the dependent objects too.
=#
お、ALTER TABLEは失敗しますね。これはcitiesとcities_mv/cities_immv間に依存関係があるため、PostgreSQL本体の機能としてガードされているようです。良かった。
自作のデータ型
PostgreSQLではユーザ定義データ型を登録して使用できます。
では、ユーザ定義データ型を使ったクエリからIMMVは作成できるのか？というのは気になるところ。
今回は以前に作成した(pg_fraction)が提供している分数型に対してpg_ivm 1.3からサポートされたMAX関数を使って試してみます。
（ビルド手順やデータベースへの組み込みの記述は割愛）
fraction型の列をもつテーブルを定義して、テキトーにデータを挿入しておきます。
=# CREATE TABLE test (id int primary key, data fraction);
CREATE TABLE
=# INSERT INTO test VALUES (1, '1/2'),(2,'2/3'),(3,'1/4');
INSERT 0 3
=# SELECT * FROM test ORDER BY data ASC;
id | data
----+------
3 | 1/4
1 | 1/2
2 | 2/3
(3 rows)
=#
まず、単なる検索クエリを引数にして、IMMVを作成してみます。
=# SELECT create_immv('test_simple_immv', $$ SELECT id, data FROM test $$);
NOTICE: created index "test_simple_immv_index" on immv "test_simple_immv"
create_immv
-------------
3
(1 row)
=#
シンプルなクエリであれば問題なく作成できるようですね。
次は集約演算(MAX)を試してみます（pg_fractionは一応、MIN, MAX, SUMは対応している）。
=# SELECT max(data) FROM test;
max
-----
2/3
(1 row)
=# SELECT create_immv('test_max', $$ SELECT max(data) FROM test $$);
ERROR: aggregate function max(fraction) is not supported on incrementally maintainable materialized view
=#
やっぱり自作のデータ型の集合演算には対応していないようです。
なお、組み込みデータ型(integer等)のMAXには対応しているので、組み込みデータ型のみを使っているなら問題はないと思います。
=# SELECT create_immv('test_max_id', $$ SELECT max(id) FROM test $$);
create_immv
-------------
1
(1 row)
=# SELECT max FROM test_max_id;
max
-----
3
(1 row)
=#
おまけ：ORDER BY + LIMITで足掻いてみる
せや！MAX()はORDER BY DESC LIMIT 1で代替できるやん！
=# SELECT create_immv('test_max', $$ SELECT data FROM test ORDER BY data DESC LIMIT 1 $$);
ERROR: ORDER BY clause is not supported on incrementally maintainable materialized view
=#
残念！IMMVではORDER BYが使えないので、そっちで引っかかります。にゃーん。
まとめ
IMMVとPostgreSQLの色々な機能の組み合わせについては、現状(1.3)ではこういう制約があることがわかりました。
| 機能名 | 1.3での動作 |
|---|---|
| MATERIALIZED VIEW | 未対応。ベーステーブルとして使用できない。 |
| IMMV | 未対応。ベーステーブルとして使用できない。 |
| Partition Table | 未対応。ベーステーブルとして使用できない。 |
| Foreign Table | 未対応。ベーステーブルとして使用できない。 |
| Unlogged Table | ベーステーブルとして利用可能。 ただし、クラッシュリカバリ時には手動でのリフレッシュが必要。 |
| ロジカルレプリケーション SUNSCRIBER |
未対応。issueは上がっている。 |
| ALTER TABLE 列名変更 |
変更可能。 定義用のクエリも書き換わっている。 |
| ALTER TABLE 列削除 |
PostgreSQL本体機能の依存関係チェックにより列削除は不可。 |
| 自作データ型 | 自作データ型のシンプルな検索は対応。 |
| 自作データ側の集約 | 未対応。メッセージに明記している。 |
おわりに
今回の記事作成にあたってpg_ivmの最新バージョンをいろいろ使ってみましたが、やっぱりこのモジュールは面白いです。
差分更新するマテリアライズド・ビューが欲しい！というPostgreSQL案件もあると思うので、興味のある人は使ってみて、改善提案のissueやpull requestを出すのもいいかもしれませんね（自分も今回の記事を書くために検証してバグレポートのissueを上げました）。
明日は@kingtomo1122さんの記事です。毎年、PostgreSQLの変な使い方を書いている人なので、今年も楽しみですねｗ
あと、PostgreSQLアドベントカレンダーと並行して、PostgreSQL 16 全部ぬこアドベントカレンダーもやっているので、興味のある方は見ていただけると幸いです。