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

# はじめに

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

# 概要
|タイトル|[Prevent COPY FREEZE on Foreign tables](https://commitfest.postgresql.org/patch/5544/)|
|:--|:--|
|Topic|Bug Fixes|
|Last modified|2025-02-06 21:24:21|
|Emails|[Prevent COPY FREEZE on Foreign tables](https://www.postgresql.org/message-id/flat/CAA5RZ0ujeNgKpE3OrLtR=eJGa5LkGMekFzQTwjgw=rzaLufQLQ@mail.gmail.com)|

改修内容は今まで外部テーブルに対する``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がかかった状態になる。

 :::note warn
 公式ドキュメントに記載があるように、このオプションを付けて実行した場合には、トランザクション完了前であっても、データロード終了後に、他のすべてのセッションから即座にデータが参照可能になる。
これは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文書（日本語版）](https://www.postgresql.jp/document/16/html/sql-copy.html)からの引用）
 
> ロード元のテーブルが現在の副トランザクションで作成または切り詰めされ、開いているカーソルは存在せず、またこのトランザクションで保持される古めのスナップショットが存在しない場合のみ、行は凍結されます。 今のところ、パーティションテーブルではCOPY FREEZEを実行できません。 

この中の最初の一文
> 現在の副トランザクションで作成または切り詰めされ

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

パターン①
```sql
BEGIN;
TRUNCATE テーブル名;
COPY テーブル名 FROM ... WITH (FREEZE ON);
...
COMIT;
```

パターン②
```sql
BEGIN;
CREATE TABLE テーブル名 ... ;
COPY テーブル名 FROM ... WITH (FREEZE ON);
```
パターン②
```sql
BEGIN;
CREATE FOREIGN TABLE テーブル名 ... ;
COPY テーブル名 FROM ... WITH (FREEZE ON);
```

今回の問題は上記のパターン③のときに発生する。
なお、パターン①かつ、テーブルが外部テーブルの場合には、以下のようなエラーになるため本記事で述べる問題は発生しない。
（トランザクション1で外部テーブルを作成し、トランザクション2の中でTRUNCATE→COPY FREEZEを行うケース）
```sql
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](https://www.postgresql.jp/document/16/html/pgvisibility.html)という拡張機能を登録する。[^1]

:::note info
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``関数で表示する。
```sql
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``を実行し、実行後に可視性マップの様子を見てみる。

```sql
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``テーブルを参照する外部テーブルを作成する。
```sql
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を実行する。

```sql
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は**実行できてしまう。**

:::note warn
なお、本バグとは直接関係しないが、このトランザクション内で、外部テーブル（``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``）になってしまう。

```sql
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したテーブルを用意する。
```sql
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を実行する。
```sql
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コマンドは失敗する。
トランザクションもロールバックされるので、可視性マップの状態も変更はない。
```sql
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を実行するケースでも、上と同様のエラーメッセージに修正されたようだ。

```sql
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でも機能的には変わっていないはず。