PostgreSQL 18がやってくる(5) 外部テーブルへのCOPY FREEZEを防ぐ

はじめに

にゃーん。
しばらくの間、サボっていたPostgreSQL 18ネタを再開することにしました。
今回はリハビリということで小ネタを書きます。

概要

タイトル	Prevent COPY FREEZE on Foreign tables
Topic	Bug Fixes
Last modified	2025-02-06 21:24:21
Emails	Prevent COPY FREEZE on Foreign tables

改修内容は今まで外部テーブルに対するCOPY FROMコマンドでFREEZEオプションが指定できてしまっていたけど、このオプションは外部テーブルだと意味ないから抑止しよう、というものらしい。

そもそもCOPY FREEZEって？

• ファイルとテーブルの間でデータをコピーするSQLコマンドのオプショの一つ。
• COPYのFROMモード（ファイルからテーブルにデータをロードする）で実行する際に、VACUUM FREEZE実行後のように行を凍結させるというオプションである。
• このオプション自体はPostgreSQL 9.3から実装されている。
• マスタデータのように基本的に更新されないテーブルなんかで使うと便利っぽい。

COPY FREEZEのご利益

マスタデータのように更新されないテーブルに対してVACUUM FREEZEをかけると以下のようなご利益がある。

• 特別な凍結XIDを各行に付与するので、以降はXID周回問題を気にせずにすむ。
• 通常のVACUUMをかけてもいい感じに処理をスキップしてくれる（VACUUM処理自体に意味がないから）。

COPY時にFREEZEオプションを有効にすると、データロードが終わった時点で、VACUUM FREEZEがかかった状態になる。

公式ドキュメントに記載があるように、このオプションを付けて実行した場合には、トランザクション完了前であっても、データロード終了後に、他のすべてのセッションから即座にデータが参照可能になる。
これはMVCC可視性に関する一般的な規則に違反しているので、他の業務が動いていない時間帯に実施する等の運用上の考慮は必要かもしれない。

例えば、pgbench_accounts(UNLOGGED, scale=10, 100万件)のテーブルに対して、

• COPY pgbench_accounts FROM 'ファイルパス' (FREEZE off) → VACUUM → VACUUM
• COPY pgbench_accounts FROM 'ファイルパス' (FREEZE on) → VACUUM → VACUUM

を実行した場合、以下のような処理時間になる。

• COPY時にFreezeオプションを付与するとFreezeのための処理が加わるためCOPY処理時間は大きくなる。
• FreezeされたテーブルへのVACUUM処理はかなり高速になる。

パターン	COPY時間	VACUUM(初回)	VACUUM(2回目)
COPY FREEZE off	18070.940 ms	20426.318 ms	565.574 ms
COPY FREEZE on	24212.907 ms	288.179 ms	1.674 ms

あと、FREEZE済みのテーブルへの参照もちょい早くなるっぽい。

• pgbenchの参照のみモードで確認(-c 1 -S -t 100000)
• latency averageを比較
• 4回試行、2回目からの3回の平均値

パターン	latency average	(参考)stddev
Freezeなし	0.156 ms	0.041
Freezeあり	0.110 ms	0.001

PostgreSQL 18での修正点

前置きが長くなったけど、ここからPostgreSQL 18での修正点を記載する。

テーブルに対するCOPY制限

テーブルに対して、COPY FREEZEを実行する場合、実は以下の制約がある。
（以下はPostgreSQL 16文書（日本語版）からの引用）

ロード元のテーブルが現在の副トランザクションで作成または切り詰めされ、開いているカーソルは存在せず、またこのトランザクションで保持される古めのスナップショットが存在しない場合のみ、行は凍結されます。 今のところ、パーティションテーブルではCOPY FREEZEを実行できません。

この中の最初の一文

現在の副トランザクションで作成または切り詰めされ

の部分だが、簡単に言えば、以下の①, ②, ③のパターンでないと、COPY FREEZEは実行できない。

パターン①

BEGIN;
TRUNCATE テーブル名;
COPY テーブル名 FROM ... WITH (FREEZE ON);
...
COMIT;

パターン②

BEGIN;
CREATE TABLE テーブル名 ... ;
COPY テーブル名 FROM ... WITH (FREEZE ON);

パターン②

BEGIN;
CREATE FOREIGN TABLE テーブル名 ... ;
COPY テーブル名 FROM ... WITH (FREEZE ON);

今回の問題は上記のパターン③のときに発生する。
なお、パターン①かつ、テーブルが外部テーブルの場合には、以下のようなエラーになるため本記事で述べる問題は発生しない。
（トランザクション1で外部テーブルを作成し、トランザクション2の中でTRUNCATE→COPY FREEZEを行うケース）

testdb=# CREATE FOREIGN TABLE pgbench_accounts_fdw (aid int, bid int, abalance int, filler char(88)) SERVER local_fdw OPTIONS (table_name 'pgbench_accounts');
CREATE FOREIGN TABLE
testdb=# BEGIN;
BEGIN
testdb=*# TRUNCATE pgbench_accounts_fdw ;
TRUNCATE TABLE
testdb=*#
testdb=*# COPY pgbench_accounts_fdw FROM '/tmp/b.txt' WITH (FREEZE on);
ERROR:  cannot perform COPY FREEZE because the table was not created or truncated in the current subtransaction

これはこれで、エラーメッセージ変じゃね？という気もするのだが、それに目をつぶれば、COPY FREEZE自体は実行されない。

問題があるのは以下に記載するパターン③のケースである。

パターン③の問題とは

パターン③ではCOPY FREEZE自体は成功し、データも実際にロードされる。
しかし、FREEZE指定をしているにも関わらず、外部テーブルの元になるテーブルへはFREEZE処理は行われない、という問題である。

可視性マップを確認する

この問題を確認するために、pg_visibilityという拡張機能を登録する。¹

pg_visibility拡張機能は、AWS Aurora PostgreSQL互換やAzure Database for PostgreSQLでも使用可能です。
（GCPのAlloyDBややOCI PostgreSQLは未調査）

この拡張機能が提供するpg_visibility関数は

指定のリレーションの指定のブロックについて、可視性マップ内の全可視ビットと全凍結ビット、およびそのブロックのPD_ALL_VISIBLEを返します。

というもの。これをわかりやすくするために、可視性マップ内の全可視ビット(all_visible)、全凍結ビット(all_frozen)およびそのブロックのPD_ALL_VISIBLE(pd_all_visible)で集約するSQLを実行する。

まず、最初にテーブルを辛にして、通常のテーブルに対してCOPYを用いてデータをロードし、そのテーブルの可視性マップの概要をpg_visibility関数で表示する。

testdb=# TRUNCATE pgbench_accounts;
TRUNCATE TABLE
testdb=# COPY pgbench_accounts FROM '/tmp/a.txt' WITH ( FREEZE off );
COPY 10
testdb=# SELECT COUNT(*) FROM pgbencH_accounts;
 count
-------
    10
(1 row)

testdb=# SELECT count(*), all_visible, all_frozen, pd_all_visible
testdb-# FROM pg_visibility('pgbench_accounts'::regclass)
testdb-# GROUP BY all_visible, all_frozen, pd_all_visible;
 count | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
     1 | f           | f          | f
(1 row)

testdb=#

このときには、10件のタプル全てがFREEZEされていないので、all_visible,all_frozen,pd_all_visibleのどの列も値はFALSE（f）になっている。

この状態で、pgbench_accountsに対してVACUUM FREEZEを実行し、実行後に可視性マップの様子を見てみる。

testdb=# VACUUM FREEZE pgbench_accounts ;
VACUUM
testdb=# SELECT count(*), all_visible, all_frozen, pd_all_visible
testdb-# FROM pg_visibility('pgbench_accounts'::regclass)
testdb-# GROUP BY all_visible, all_frozen, pd_all_visible;
 count | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
     1 | t           | t          | t
(1 row)

10件のタプル全てがFREEZEされているのでall_visible,all_frozen,pd_all_visibleのどの列も値はTRUE（t）になっている。

問題の再現

この状態でパターン③のケースを使って問題を再現する。
（なお、既にCREATE SERVERコマンド,CREATE USER MAPPINGコマンドによって外部サーバやユーザマッピングは作成している状態である）

トランザクションを開始し、直後にCREATE FOREIGN TABLEコマンドで、pgbench_accountsテーブルを参照する外部テーブルを作成する。

testdb=# BEGIN;
BEGIN
testdb=*# CREATE FOREIGN TABLE pgbench_accounts_fdw (aid int, bid int, abalance int, filler char(88))SERVER local_fdw OPTIONS (table_name 'pgbench_accounts');
CREATE FOREIGN TABLE
testdb=*# SELECT COUNT(*) FROM pgbench_accounts;
 count
-------
    10
(1 row)

testdb=*# SELECT COUNT(*) FROM pgbench_accounts_fdw;
 count
-------
    10
(1 row)

testdb=*# SELECT count(*), all_visible, all_frozen, pd_all_visible
FROM pg_visibility('pgbench_accounts'::regclass)
GROUP BY all_visible, all_frozen, pd_all_visible;
 count | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
     1 | t           | t          | t
(1 row)

このときには、まだ可視性マップの全可視ビット(all_visible)、全凍結ビット(all_frozen)およびそのブロックのPD_ALL_VISIBLE(pd_all_visible)も全てTRUE(t)のままになっている。
この状態で、外部テーブルpgbench_accounts_fdwに対して、COPY FROM FREEZEを実行する。

testdb=*# COPY pgbench_accounts_fdw FROM '/tmp/b.txt' WITH (FREEZE ON);
COPY 10
testdb=*# SELECT COUNT(*) FROM pgbench_accounts_fdw;
 count
-------
    20
(1 row)

testdb=*# SELECT COUNT(*) FROM pgbench_accounts;
 count
-------
    10
(1 row)

PostgreSQL 17では外部テーブルに対するCOPY FROM FREEZEは実行できてしまう。

なお、本バグとは直接関係しないが、このトランザクション内で、外部テーブル（pgbench_accounts_fdw）と外部テーブルの参照元のテーブル（pgbench_accounts）の件数をカウントすると、おや？と思う結果になる。

• pgbench_accounts_fdwは20件
• pgbench_accountsは10件

これが、PostgreSQL文書でも警告されていた「MVCC可視性に関する一般的な規則に違反」による奇妙な問題なのだろう。
なお、このトランザクションをCOMMITすると、pgbench_accounts側の件数も20件になる。

この状態で、pg_visibility関数を実行すると、COPY時にFREEZEオプションを付与して実行したにもかかわらず、全可視ビット(all_visible)、全凍結ビット(all_frozen)およびそのブロックのPD_ALL_VISIBLE(pd_all_visible)はFLASE（f）になってしまう。

testdb=*# SELECT count(*), all_visible, all_frozen, pd_all_visible
FROM pg_visibility('pgbench_accounts'::regclass)
GROUP BY all_visible, all_frozen, pd_all_visible;
 count | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
     1 | f           | f          | f
(1 row)

これが、PostgreSQL 17までの問題であった。

PostgreSQL 18での修正点

この問題はPostgreSQL 18で外部テーブルに対するCOPY FREEZEを許容しない、という方針で対応された。

PostgreSQL 18でも同様にVACUUM FREEZEしたテーブルを用意する。

testdb=# TRUNCATE pgbench_accounts ;
TRUNCATE TABLE
testdb=# COPY pgbench_accounts FROM '/tmp/a.txt' ;
COPY 10
testdb=# VACUUM FREEZE pgbench_accounts ;
VACUUM
testdb=# SELECT count(*), all_visible, all_frozen, pd_all_visible
FROM pg_visibility('pgbench_accounts'::regclass)
GROUP BY all_visible, all_frozen, pd_all_visible;
 count | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
     1 | t           | t          | t
(1 row)

この状態で、PostgreSQL 17と同様にトランザクション開始→外部テーブル作成→外部テーブルへのCOPY FREEZEを実行する。

testdb=# BEGIN;
BEGIN
testdb=*# CREATE FOREIGN TABLE pgbench_accounts_fdw (aid int, bid int, abalance int, filler char(88))SERVER local_fdw OPTIONS (table_name 'pgbench_accounts');
CREATE FOREIGN TABLE
testdb=*# SELECT count(*), all_visible, all_frozen, pd_all_visible
FROM pg_visibility('pgbench_accounts'::regclass)
GROUP BY all_visible, all_frozen, pd_all_visible;
 count | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
     1 | t           | t          | t
(1 row)

testdb=*# COPY pgbench_accounts_fdw FROM '/tmp/b.txt' WITH (FREEZE ON);
ERROR:  cannot perform COPY FREEZE on a foreign table
testdb=!# ROLLBACK;
ROLLBACK
testdb=#

PostgreSQL 18ではERROR: cannot perform COPY FREEZE on a foreign tableというエラーとなり、COPYコマンドは失敗する。
トランザクションもロールバックされるので、可視性マップの状態も変更はない。

testdb=# SELECT count(*), all_visible, all_frozen, pd_all_visible
FROM pg_visibility('pgbench_accounts'::regclass)
GROUP BY all_visible, all_frozen, pd_all_visible;
 count | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
     1 | t           | t          | t
(1 row)

めでたし、めでたし。

なお、パターン①の事前に外部テーブル作成→トランザクション開始→外部テーブル作成→外部テーブルへのCOPY FREEZEを実行するケースでも、上と同様のエラーメッセージに修正されたようだ。

testdb=# CREATE FOREIGN TABLE pgbench_accounts_fdw (aid int, bid int, abalance int, filler char(88))SERVER local_fdw OPTIONS (table_name 'pgbench_accounts');
CREATE FOREIGN TABLE
testdb=# BEGIN;
BEGIN
testdb=*# TRUNCATE pgbench_accounts_fdw ;
TRUNCATE TABLE
testdb=*# COPY pgbench_accounts_fdw FROM '/tmp/b.txt' WITH (FREEZE on);
ERROR:  cannot perform COPY FREEZE on a foreign table
testdb=!# ROLLBACK;
ROLLBACK
testdb=#

おわりに

小ネタだと思って軽い気持ちで調べたけど、

• COPY FREEZE自体きちんと理解してなかった
• 外部テーブルへのCOPY FREEZEが実行できる抜け道を知らなかった
  （よくこんなパターンのバグを見つけたよな・・・）
• pg_visibility拡張機能をきちんと使ったことなかった

ので、思いのほか調べるのに時間がかかったけど。いい勉強になったのでヨシ！

1. PostgreSQL 16（日本語版）文書へのリンクだが、たぶんPostgreSQL 17, PostgreSQL 18でも機能的には変わっていないはず。 ↩