はじめに
にゃーん。
今回はPostgreSQL 17のCOPYコマンドの改善点について調べた内容を書いてみる。
COPYコマンドとは
COPYコマンドは、PostgreSQLに昔から存在するデータロード/データエクスポート用のコマンドだ。
- COPY FROM：ファイルからテーブルへのロード
- COPY TO：テーブルからファイルへのエクスポート
大量のデータをファイル（ファイルだけでなくプログラムの出力結果も使うことができる）から一気にロードするときに、1行ごとにINSERTを行う方法と比べると非常に高速に処理できる
バッチ的なデータロード処理にはなくてはならない機能だ。
PostgreSQL 17での改善点
PostgreSQL 17 Beta1のリリースノートを見ると、PostgreSQL 17ではCOPY FROMに関して以下の4つの改善が入っている。
- "ON_ERROR ignore"オプションの追加
Add new COPY option "ON_ERROR ignore" to discard error rows (Damir Belyalov, Atsushi Torikoshi, Alex Shulgin, Jian He, Yugo Nagata)
The default behavior is "ON_ERROR stop". - LOG_VERBOSITYオプションの追加
Add new COPY option LOG_VERBOSITY which reports COPY FROM ignored error rows (Bharath Rupireddy) - pg_stat_progress_copyの改善
Allow COPY FROM to report the number of skipped rows during processing (Atsushi Torikoshi)
This appears in system view column pg_stat_progress_copy.tuples_skipped. - 全ての列に対するFORCE_NULL/FORCE_NOT_NULL指定の追加
In COPY FROM, allow easy specification that all columns should be forced null or not null (Zhang Mingli)
改善項目の1.～3.はCOPY FROMの処理で入力行がフォーマットエラーになった場合の挙動に関する改善だ。これまでは、COPY処理途中で、フォーマットエラーになった行があるとCOPY処理全体がエラーになっていた。
（COPYのこの仕様のため、エラー行をスキップしたロード処理を行う場合にはpg_bulkloadを使う必要があった）
動作検証
今回はPostgreSQL 17 Beta1を使って検証した。
PostgreSQL 17のCOPYの構文
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
[ WHERE condition ]
（略）
where option can be one of:
FORMAT format_name
FREEZE [ boolean ]
DELIMITER 'delimiter_character'
NULL 'null_string'
DEFAULT 'default_string'
HEADER [ boolean | MATCH ]
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
FORCE_NOT_NULL { ( column_name [, ...] ) | * }
FORCE_NULL { ( column_name [, ...] ) | * }
ON_ERROR error_action
ENCODING 'encoding_name'
LOG_VERBOSITY verbosity
optionのON_ERROR error_action、LOG_VERBOSITY verbosityが追加されている。
"ON_ERROR ignore"オプションの追加
実際には、ON_ERRORオプション自体が追加になっている。
ON_ERRORオプションに指定可能なerror_actionは以下。
| error_action | 意味 |
|---|---|
| stop | コマンドを失敗させる。ON_ERRORオプションのデフォルト値はstopと同義。PostgreSQL 16までのCOPYの動作は ON_ERROR stopを同じになる。 |
| ignore | 入力行を破棄して次に行の処理を続行する。 これがPostgreSQL 17の新機能になる。 |
たとえば以下のようなテーブルがあるとする。
$ psql test -c "\d test"
Null display is "(null)".
Table "public.test"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
review_date | date | | |
price | integer | | |
$ psql test -c "TABLE test"
Null display is "(null)".
id | name | review_date | price
----+------+-------------+-------
(0 rows)
$
このテーブルに、以下のようなタブ区切りのファイルの内容をCOPYコマンドでロードしようとする。
$ cat /tmp/ramen-01.txt
1 チャーシューワンタンメン 2023-12-30 1200
2 サンマーメン 2023-12-31 700.0
3 小ぶた 2024-01-15 880
4 タンタンメン中辛 2024-02-29
5 ラーメン 2024-03-01 650
$
このファイルには2箇所、不正な箇所がある。
- id = 2の行のprice(700.0)が整数型の定数になっていない。
- id=4の行のprice列に対応する値がない（空値になっている）。
このためデフォルト（ON_ERROR stop）のオプションでCOPYを実行すると失敗する。
$ psql test -c "COPY test FROM '/tmp/ramen-01.txt'"
Null display is "(null)".
ERROR: invalid input syntax for type integer: "700.0"
CONTEXT: COPY test, line 2, column price: "700.0"
$
PostgreSQL 16までのCOPYも上と同様の挙動となる。
PostgreSQL 17からは、optionでON_ERROR ignoreが指定可能になった。
上記と同じファイルをON_ERROR ignore指定をつけて実行する。
$ psql test -c "COPY test FROM '/tmp/ramen-01.txt' (ON_ERROR ignore)"
Null display is "(null)"
NOTICE: 2 rows were skipped due to data type incompatibility
COPY 3
$
データタイプ不一致の行が2つあった、というNOTICEメッセージを出力し、COPY自体は成功する。
この状態でtestテーブルの内容を確認すると、データタイプ不一致のid=2, 4以外の行がロードされている。
$ psql test -c "TABLE test"
Null display is "(null)".
id | name | review_date | price
----+--------------------------+-------------+-------
1 | チャーシューワンタンメン | 2023-12-30 | 1200
3 | 小ぶた | 2024-01-15 | 880
5 | ラーメン | 2024-03-01 | 650
(3 rows)
$
ただ、どういうエラーでも無視するわけではないらしい。
例えば、以下のようなファイル
$ cat /tmp/ramen-02.txt
6 焦がし味噌ラーメン 2024-04-02 980
7 にんにくらーめん 2024-04-13
8 地獄ラーメン・極上級１ 2024-04-29 880
$
の場合、id=7の行は、2024-04-13の直後に改行がある。
この場合、COPYはprice列に対応するデータがないというエラーとなり、ON_ERROR ignoreオプションがあるにも関わらず、その行を無視して次の行の処理を行わず、COPYコマンド自体がエラーになっていまう。
$ psql test -c "TRUNCATE test"
Null display is "(null)".
TRUNCATE TABLE
$ psql test -c "TABLE test"
Null display is "(null)".
id | name | review_date | price
----+------+-------------+-------
(0 rows)
$ psql test -c "COPY test FROM '/tmp/ramen-02.txt' (ON_ERROR ignore)"
Null display is "(null)".
ERROR: missing data for column "price"
CONTEXT: COPY test, line 2: "7 にんにくらーめん 2024-04-13"
$ psql test -c "TABLE test"
Null display is "(null)".
id | name | review_date | price
----+------+-------------+-------
(0 rows)
$
これが、バグなのか仕様（仕様だとしたら、ドキュメントに注記欲しい・・・）は良くわからない・・・。
PostgreSQL DocumentのCOPYのON_ERRORの説明に記載はありました。
Specifies how to behave when encountering an error converting a column's input value into its data type.
(カラムの入力値をデータ型に変換する際にエラーが発生した場合の動作を指定します。)
Commitfest項目：Add new error_action COPY ON_ERROR "log"から辿れるMLの議論でも、このケースについて議論があったけど、PostgreSQL 17ではいったんこのケースのエラーを無視する対処は棚上げになるかもしれません。（MLの議論をきちんと追いきれてないですが・・・）
COPYのON_ERROR指定については、他にもON_ERROR NULLの指定なども提案されている（今回の版ではコミットはされてないが）。今後、色々なオプションが増えて、COPYの入力エラーの対応が更に柔軟に対応できそうではある。
LOG_VERBOSITYオプションの追加
これは、さきほど説明したON_ERROR ignoreの挙動に関連するもの。
LOG_VERBOSITYというオプションでエラー発生時の情報を詳細に出力するようになる。
LOG_VERBOSITYに設定できる値は以下となる。
| 設定値 | 意味 |
|---|---|
| default | デフォルト設定。 何行スキップされたかを報告する。 |
| verbose | 冗長出力設定。 エラーが発生した行ごとに、どの列に問題があったかを報告し、最後に何行スキップされたかを報告する。 |
最初に挙げたデータ（id=2, id=4の行で型不一致になる）のCOPY時に、LOG_VERBOSITY verboseオプションも追加して実行した場合、エラーが発生した行のどの列が問題なのか、詳細に報告する。
（ダメダメなデータファイルからロードすると、めっさメッセージが出力される可能性もある）
$ psql test -c "COPY test FROM '/tmp/ramen-01.txt' (ON_ERROR ignore ,LOG_VERBOSITY verbose)"
Null display is "(null)".
NOTICE: skipping row due to data type incompatibility at line 2 for column price: "700.0"
NOTICE: skipping row due to data type incompatibility at line 4 for column price: ""
NOTICE: 2 rows were skipped due to data type incompatibility
COPY 3
$
pg_stat_progress_copyの改善
COPY処理は大量のデータロードで使われることが多い＝処理時間がかかるので進捗見たいよね、ということで、PostgreSQL 14からCOPY処理の進捗を確認するpg_stat_progress_copyビューが追加されている。
PostgreSQL 17では、このpg_stat_progress_copyビューにtuples_skippedという列が追加されている。
PostgreSQL Documentの説明では、
Number of tuples skipped because they contain malformed data. This counter only advances when a value other than stop is specified to the ON_ERROR option.
とあるので、今後、ON_ERROR actionで追加されるignore以外のactionについても、ここでカウントアップされるようだ。
例として、pgbench_accountsテーブル（scale factor=100, 1000万件）のデータのbid列を2箇所、整数でない文字に書き換え、その状態でCOPYをON_ERROR ignore, LOG_VERBOSITY verboseオプションつきで実行する。
$ psql bench -c "COPY pgbench_accounts FROM '/tmp/a.txt' (ON_ERROR ignore, LOG_VERBOSITY verbose)"
Null display is "(null)".
NOTICE: skipping row due to data type incompatibility at line 15 for column abalance: "0.0"
NOTICE: skipping row due to data type incompatibility at line 23744 for column abalance: "a"
NOTICE: 2 rows were skipped due to data type incompatibility
COPY 9999998
$
この処理は自分の環境だと数分くらいかかるので、COPYコマンドの実行中に別の端末から、pg_stat_progress_copyビューを参照する。
postgres@bench=# \x
Expanded display is on.
postgres@bench=# TABLE pg_stat_progress_copy ;
-[ RECORD 1 ]----+----------
pid | 16874
datid | 16410
datname | bench
relid | 16417
command | COPY FROM
type | FILE
bytes_processed | 104464384
bytes_total | 978088899
tuples_processed | 1064700
tuples_excluded | 0
tuples_skipped | 2
postgres@bench=#
tuples_skipped列に型不一致エラーによりスキップされた2行が報告されている。・
全ての列に対するFORCE_NULL/FORCE_NOT_NULL指定の追加
FORCE_NULL指定の変更
$ psql test -c "\d test"
Null display is "(null)".
Table "public.test"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
review_date | date | | |
price | integer | | |
$ psql test -c "TABLE test"
Null display is "(null)".
id | name | review_date | price
----+------+-------------+-------
(0 rows)
$
このテーブルに以下のようなCSVファイルをCOPYでロードしようとする。
$ cat /tmp/ramen-null.csv
1,チャーシューワンタンメン,2023-12-30,1200
2,サンマーメン,,700
3,小ぶた,"",880
4,タンタンメン中辛,2024-02-14,
5,ラーメン,2024-03-01,650
$
id=2の行はreview_dateが引用符なしの空値、id=3の行はreview_dateが引用符ありの空値、id=4はpriceが空値になっている。
このファイルをロードするときにFORCE_NULLオプションを指定すると、
「指定された列の値を、それが引用符付きであったとしても、NULL文字列と比較し、一致した場合は値をNULLにセットする」
という挙動になる（なのでid=3の行も引用符内が空値と判断されNULLを設定する）。
$ psql test -c "COPY test FROM '/tmp/ramen-null.csv' (FORMAT CSV, FORCE_NULL (review_date, price))"
Null display is "(null)".
COPY 5
$ psql test -c "TABLE test"
Null display is "(null)".
id | name | review_date | price
----+--------------------------+-------------+--------
1 | チャーシューワンタンメン | 2023-12-30 | 1200
2 | サンマーメン | (null) | 700
3 | 小ぶた | (null) | 880
4 | タンタンメン中辛 | 2024-02-14 | (null)
5 | ラーメン | 2024-03-01 | 650
(5 rows)
$
PostgreSQL 16までは、FORCE_NULLの指定のために、
FORCE_NULL ( column_name [, ...] )
のように指定対象の列名を明示する必要があった。
PostgreSQL 17では構文が以下のように変更され
FORCE_NULL { ( column_name [, ...] ) | * }
*を指定することで、明示的に列名を指定しなくてもよくなった。
FORCE_NULLに*を設定したCOPYの例を以下に示す。
$ psql test -c "TRUNCATE test"
Null display is "(null)".
TRUNCATE TABLE
$ psql test -c "TABLE test"
Null display is "(null)".
id | name | review_date | price
----+------+-------------+-------
(0 rows)
$ psql test -c "COPY test FROM '/tmp/ramen-null.csv' (FORMAT CSV, FORCE_NULL *)"
Null display is "(null)".
COPY 5
$ psql test -c "TABLE test"
Null display is "(null)".
id | name | review_date | price
----+--------------------------+-------------+--------
1 | チャーシューワンタンメン | 2023-12-30 | 1200
2 | サンマーメン | (null) | 700
3 | 小ぶた | (null) | 880
4 | タンタンメン中辛 | 2024-02-14 | (null)
5 | ラーメン | 2024-03-01 | 650
(5 rows)
$
FORCE_NOT_NULL指定の変更
FORCE_NOT_NULLについても同様に、PostgreSQL 16までは
FORCE_NOT_NULL ( column_name [, ...] )
のように明示的に対象の列を指定する必要があったが、PostgreSQL 17では、
FORCE_NULL { ( column_name [, ...] ) | * }
*を指定することで全ての列をFORCE_NOT_NULLの対象にできるようになった。
おわりに
今回はCOPYコマンドのPostgreSQL 17での改善点について調べてみた。
エラー時のスキップ処理が入ったのはやはり大きい。今までpg_bulkloadを使う必要があったケースでも、PostgreSQL 17からはCOPYで代替可能になるかもしれない。