PostgreSQL 17がやってくる(9) COPYコマンドの改善

# はじめに
にゃーん。
今回はPostgreSQL 17のCOPYコマンドの改善点について調べた内容を書いてみる。

# COPYコマンドとは
[COPYコマンド](https://www.postgresql.jp/document/16/html/sql-copy.html)は、PostgreSQLに昔から存在するデータロード/データエクスポート用のコマンドだ。
* COPY FROM：ファイルからテーブルへのロード
* COPY TO：テーブルからファイルへのエクスポート

大量のデータをファイル（ファイルだけでなくプログラムの出力結果も使うことができる）から一気にロードするときに、1行ごとにINSERTを行う方法と比べると非常に高速に処理できる
バッチ的なデータロード処理にはなくてはならない機能だ。

# PostgreSQL 17での改善点
PostgreSQL 17 Beta1のリリースノートを見ると、PostgreSQL 17ではCOPY FROMに関して以下の4つの改善が入っている。

1. "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".
1. LOG_VERBOSITYオプションの追加
Add new COPY option LOG_VERBOSITY which reports COPY FROM ignored error rows (Bharath Rupireddy)
1. 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.
1. 全ての列に対する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](http://ossc-db.github.io/pg_bulkload/pg_bulkload-ja.html)を使う必要があった）

# 動作検証
今回はPostgreSQL 17 Beta1を使って検証した。

## PostgreSQL 17のCOPYの構文

```sql
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|コマンドを失敗させる。<br>``ON_ERROR``オプションのデフォルト値はstopと同義。<br>PostgreSQL 16までのCOPYの動作は``ON_ERROR stop``を同じになる。|
|ignore|入力行を破棄して次に行の処理を続行する。<br>これがPostgreSQL 17の新機能になる。|

たとえば以下のようなテーブルがあるとする。
```sql
$ 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コマンドでロードしようとする。

```text
$ 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を実行すると失敗する。

```sql
$ 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``指定をつけて実行する。

```sql
$ 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以外の行がロードされている。

```sql
$ 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)

$
```
ただ、どういうエラーでも無視するわけではないらしい。
例えば、以下のようなファイル
```text
$ 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コマンド自体がエラーになっていまう。
```sql
$ 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)

$
```
**これが、バグなのか仕様（仕様だとしたら、ドキュメントに注記欲しい・・・）は良くわからない・・・。**

:::note info
PostgreSQL DocumentのCOPYの``ON_ERROR``の説明に記載はありました。
> Specifies how to behave when encountering an error converting a column's input value into its data type. 

(カラムの入力値をデータ型に変換する際にエラーが発生した場合の動作を指定します。)
:::

:::note info
Commitfest項目：[Add new error_action COPY ON_ERROR "log"](https://commitfest.postgresql.org/47/4798/)から辿れるMLの議論でも、このケースについて議論があったけど、PostgreSQL 17ではいったんこのケースのエラーを無視する対処は棚上げになるかもしれません。（MLの議論をきちんと追いきれてないですが・・・）
:::

COPYの``ON_ERROR``指定については、他にも``ON_ERROR NULL``の指定なども提案されている（今回の版ではコミットはされてないが）。今後、色々なオプションが増えて、COPYの入力エラーの対応が更に柔軟に対応できそうではある。

## LOG_VERBOSITYオプションの追加
これは、さきほど説明した``ON_ERROR ignore``の挙動に関連するもの。
LOG_VERBOSITYというオプションでエラー発生時の情報を詳細に出力するようになる。

LOG_VERBOSITYに設定できる値は以下となる。

|設定値|意味|
|:--|:--|
|default|デフォルト設定。<br>何行スキップされたかを報告する。|
|verbose|冗長出力設定。<br>エラーが発生した行ごとに、どの列に問題があったかを報告し、最後に何行スキップされたかを報告する。|

最初に挙げたデータ（id=2, id=4の行で型不一致になる）のCOPY時に、``LOG_VERBOSITY verbose``オプションも追加して実行した場合、エラーが発生した行のどの列が問題なのか、詳細に報告する。
（ダメダメなデータファイルからロードすると、めっさメッセージが出力される可能性もある）
```sql
$ 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](https://www.postgresql.jp/document/16/html/progress-reporting.html#COPY-PROGRESS-REPORTING)ビューが追加されている。

PostgreSQL 17では、この[pg_stat_progress_copy](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING)ビューに``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``オプションつきで実行する。

```sql
$ 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``ビューを参照する。
```sql
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指定の変更

```sql
$  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でロードしようとする。
```csvs
$ 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を設定する）。

```sql
$  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の指定のために、
```sql
    FORCE_NULL ( column_name [, ...] )
```
のように指定対象の列名を明示する必要があった。

PostgreSQL 17では構文が以下のように変更され
```sql
    FORCE_NULL { ( column_name [, ...] ) | * }
```
``*``を指定することで、明示的に列名を指定しなくてもよくなった。
``FORCE_NULL``に``*``を設定したCOPYの例を以下に示す。

```sql
$  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までは
```sql
    FORCE_NOT_NULL ( column_name [, ...] )
```
のように明示的に対象の列を指定する必要があったが、PostgreSQL 17では、
```sql
    FORCE_NULL { ( column_name [, ...] ) | * }
```
``*``を指定することで全ての列を``FORCE_NOT_NULL``の対象にできるようになった。

# おわりに
今回はCOPYコマンドのPostgreSQL 17での改善点について調べてみた。
エラー時のスキップ処理が入ったのはやはり大きい。今まで[pg_bulkload](http://ossc-db.github.io/pg_bulkload/pg_bulkload-ja.html)を使う必要があったケースでも、PostgreSQL 17からはCOPYで代替可能になるかもしれない。