Postgresで大量データの一括インサートをする方法

Question

大量データをselectし、別テーブルに一括でインサートする方法をご教示ください。

＜実装したい背景＞30万件ほどのデータのselectをカーソルにて行い、loop内で1件ずつinsertしたところ、タイムアウトしてしまったためです。insert文を大量に発行していることが原因だと考えています。
また、複雑なチェック処理やデータの加工を行っている関係上、insert前に1件ずつの処理が必要です。
そのため、「insert ~ select」で一括インサートが難しい状況です。

＜試したこと＞
Oracleのバルクインサートのようなものが使えないかと考えましたが、
下記処理でもエラーが起きています。

//データを取得するテーブル
CREATE TABLE manager (
    id text,
    first_name text,
    age numeric,
    tel text
);

INSERT INTO manager (id, first_name, age, tel) VALUES
('1', '名字', 30, '090-9999-9999');


//データを登録するテーブル
CREATE TABLE test (
    test1 text,
    test2 text,
    test_num numeric,
    test4 text
);

CREATE OR REPLACE FUNCTION fnc_main(user_id text)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
declare

-- データが30万件ほどある
cur_manager cursor is
	 select
	 	first_name
	 from
	 	manager;

 rec_manager record;
 tmp_test test%rowtype;
 rec_test record;
 fnc_return text;
 err_msg text;

begin

open cur_manager;
fetch cur_manager into rec_manager;
loop
      --値の入力チェックや編集を行う（15項目） 	
        --項目の入力チェックは省略しています
        --値をセット
	tmp_test.test1 = rec_manager.first_name;
        if rec_manager.id = 1 then
          tmp_test.test_num = rec_manager.age;
          -- 別functionで年齢から西暦等を取得する処理
--          select fnc_get_seireki(rec_manager.age) into fnc_return;
            
            --エラーが発生している場合
            if fnc_return = 'true'  then
              err_msg = '西暦が取得できません';
            else
                  tmp_test.test2 = fnc_return;
        end if;
	--...

//        rec_test = tmp_test; --恐らくエラーが発生している
end loop;

//insert into test values(rec_test);  --恐らくエラーが発生している

return '';
end;

$function$

Comments

[m.ts10806]

＞下記処理でもエラーが起きています。
どういうエラーでしょうか。文法上のものかそうでないかでも違います。
https://teratail.com/help/question-tips#questionTips3-4-2

[dameo]

コミットはどの程度の頻度で行っているのでしょうか？
procedureでなくfunctionにしている理由は何かありますか？

[退会済みユーザー]

>m.ts10806様
エラーコード「55000 必要条件を満たさないオブジェクト」の「ERROR: record “ rec_test” is not assigned yet」と出力されました。

[退会済みユーザー]

＞dameo様
単独でfunctionを呼び出しているため、関数終了時に自動コミットをしていると思われます。トランザクションの処理に疎く、間違っていたら申し訳ありません。
procedureはPostgres11からの機能であり、10以前から運用していた流れか、procedureを使用せず、functionでの開発が要望として上がっているためです。（現実的でないことをご理解くださらない、、、）

[sazi]

procedureかfunctionかは呼び出す形式が異なるだけで、やれない事があるという訳では無いですから関係無いですよ。

[dameo]

@sazi様
functionの場合、SQL内に式として書けることもあり、あまり書き換えなどはしないと思いますし、commitが出来ないという問題があります。

@Unknown2030様
大量登録の場合、設定にもよるし、どこに時間がかかっているのか実際には測ってみないと分かりませんが、体感的には、何行ごとにcommitするかが結構大きく効く気がするのです。なので、可能であればprocedureで一定行ごとにcommitしてみてほしいです。他には
・トランザクションログなどを残すか
・indexや制約などがあるか
・bulk処理されているか
などでしょうか。実際には固定データならcopyコマンドが一番速いようですが。

あと、fetch loopの処理が不思議な感じになってるので、まずはそこから分かるように書きましょう。
(もしかしたらこのループが終わってないだけかもしれません。)
また、バージョンや使える機能に制限がある場合は事前に書いておいた方がいいかもです。
申し訳ありませんがbulk collectやis table ofなどがpostgresqlでも使えるのか知りません。ただtableタイプはあるようなので、似たような書き方はできるのではないかと思っています。時間があったら私も試してみますが、期待しないでください。

[dameo]

起きてから軽く書いてみました。bulk fetchも変数からのbulk insertもなさげですね。
手元の骨だけ環境ではinsert投げまくるよりは半分くらいの時間になります。procedureにしてcommitすると1割くらい減ります。indexなどは皆無だし、DB自体もdockerのデフォのまま使ってるので、意味のない値ですが。

CREATE OR REPLACE FUNCTION fnc_main(id text)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
declare
cur_manager cursor is select * from manager;
rec_manager manager;
rec_test test;
tmp_test test[];
i integer;
bulk_count integer;
begin
i := 1;
bulk_count := 1000; -- 1000は適当
open cur_manager;
loop
fetch cur_manager into rec_manager; -- postgresqlにbulk fetchはない
if not found then
insert into test select * from unnest(tmp_test); -- insert selectしかなさそう
-- commit;
exit;
end if;
rec_test.test1 = rec_manager.first_name;
--
tmp_test := array_append(tmp_test, rec_test);
if i = bulk_count then
insert into test select * from unnest(tmp_test); -- 面倒なので共通化はしてません
-- commit;
i := 1;
tmp_test := null;
else
i := i + 1;
end if;
end loop;
return '';
end;
$function$;

[退会済みユーザー]

>dameo様
具体的にご教示くださりありがとうございます。
tmp_test test[];のテーブルの配列が使えることはとても勉強になりました。
ご教示いただいた内容で色々と試してみたいと思います。

Answer 1

--項目の入力チェックは省略しています

たかが30万件のデータですから、
INSERT ... SELECT を条件毎にいくつかのSQLに分けて何度か実行すれば済むので？

Comments

[退会済みユーザー]

複雑なチェック処理やデータの加工を行っている関係上、insert前に各カラムの処理が必要です。
そのため、「insert ~ select」で一括インサートが難しい状況です。
また、取得元のテーブルのデータ全てを対象にすることや、functionの引数に条件を追加することが運用上できないため、複数回functionを呼ぶこともできません。

[sazi]

--コメントする場所を間違えたので削除--

Answer 2

insert文を大量に発行していることが原因だと考えています。

FUNCTION fnc_main(id text) を30万回呼び出しているということはないですか？
原因はinsert文を大量に発行していることではなく、テーブルの１行毎にfnc_main関数を呼び出しその中でselect文を実行して、結果をloopさせていることではないですか？

cur_manager cursor is
     select
         first_name
     from
         manager;

省略

loop
    --項目の入力チェックは省略しています
        --値をセット
    tmp_test.test1 = rec_manager.first_name;
    --...
        rec_test = tmp_test;
end loop;

それであるならば、

複雑なチェック処理やデータの加工を行っている

部分をSQL内のサブクエリでSQLの一部として記述して
「insert ~ select」で一括インサートさせるのが最適解ではないかと
考えます。

具体的なチェック処理やデータの加工内容を提示してみてはどうでしょうか？

Comments

[退会済みユーザー]

fnc_main関数の呼び出しは1度のみです。そのため、カーソルのopenも一度のみです。
モジュールの持ち出しが叶わず、上記のような処理としかお伝えできないため申し訳ありません。
別ファンクションに引数を渡し、return値にてエラーチェックや、マスタのselectを行なっています。
サブクエリの入れ子が3つになりそうだったため、保守性や速度を考慮して「insert ~ select」を断念した次第です。

Answer 3

selectをカーソルにて行い、loop内で1件ずつinsertしたところ、タイムアウトしてしまったためです。
Oracleのバルクインサートのようなものが使えないかと考えましたが、下記処理でもエラーが起きています。

タイムアウトが理由ならSQLのチューニングを行うしかないですね。

複雑なチェック処理やデータの加工を行っている関係上、insert前に1件ずつの処理が必要です。

どんなに複雑でも、Postgresはチューリング完全ですからカーソルに含める事はできます。

IDをパラメタにしているところを見ると、ストアドは1件毎に処理しているのでしょうし、それでタイムアウトなら、致命的に遅いのでしょう。

先ずは、カーソルまたは、loop内でのSQLのチューニングを行ってみる事ですね。

Comments

[退会済みユーザー]

SQLのチューニングにあたって、どこのSQLが重いか判断する方法として、SQLのコメントアウトで切り分けを行ってみたいと思います。より良い方法がございましたら、ご教示ください。

また、モジュールではIDをパラメタにしていますが、一意に絞り込めるものではありません。
実際にはテーブルに存在する30万件のデータをカーソルにて取得するところから始まります。
開発中のモジュールを持ってくることが難しく、記載したモジュールが不完全でお手数おかけします。

[sazi]

> fnc_main関数の呼び出しは1度のみです。
insertする件数も大きいのなら、トランザクション量に問題があるかもしれません。
ストアドでは分割コミットはできませんので、一度、処理件数を限定して処理が完了するか確認されてみた方が良いでしょう。

[sazi]

> サブクエリの入れ子が3つになりそうだったため、保守性や速度を考慮して「insert ~ select」を断念した次第です。

それは作りが悪いとしか言いようが無いですね。

[退会済みユーザー]

＞insertする件数も大きいのなら、トランザクション量に問題があるかもしれません。
ストアドでは分割コミットはできませんので、一度、処理件数を限定して処理が完了するか確認されてみた方が良いでしょう。
ありがとうございます。処理件数を10万件まで絞ったところ、23秒と30秒のタイムアウト前に正常値で登録が終了しました。

[退会済みユーザー]

> サブクエリの入れ子が3つになりそうだったため、保守性や速度を考慮して「insert ~ select」を断念した次第です。
> それは作りが悪いとしか言いようが無いですね。
別の共通モジュールにてマスタ取得処理やエラーチェック等を行う縛りがあり、fun_mainに記載できないのがつらいところです。別の共通モジュールのエラーを拾う関係上「insert ~ select」を使用し難い背景があります。

[sazi]

「別の共通モジュール」とはストアドファンクションなのですよね？
更新を行うファンクションでないなら、カーソル用のSQLに含める方がSQLの発行回数は格段に抑えられると思いますけど。

[sazi]

SQLのチューニングに関しては、実行計画を活用して下さい。

[退会済みユーザー]

＞「別の共通モジュール」とはストアドファンクションなのですよね？
更新を行うファンクションでないなら、カーソル用のSQLに含める方がSQLの発行回数は格段に抑えられると思いますけど

知識が浅く、認識しておりませんでした。ストアドファンクションであり、更新を行うファンクションではないです。狙いはモジュールの共通化で、fnc_mainのようなメインモジュールを複数作成しているため、メインモジュールから呼び出す共通のコードについては、別のストアドファンクションで共通管理しています。

[退会済みユーザー]

＞SQLのチューニングに関しては、実行計画を活用して下さい。
ありがとうございます。実行計画を調べて活用してみます。