PostgreSQLでのUPSERT(INSERT or UPDATE)処理を検証する

PostgreSQLでUPSERT（データがあればUPDATEしてなければINSERT）する

方法について調べたところ、いくつか書き方があるようだ。

UPSERT処理で気になるのは同時実行された際に

重複登録されたり重複エラーが発生しないかどうかなので、

その観点で下記の4パターンの方法を検証してみる。

1. UPDATEしてNOT FOUNDならINSERT
2. CTEでUPDATEしてNOT EXISTSならINSERT
3. INSERT ON CONFLICT DO UPDATE
4. INSERTしてUNIQUE_VIOLATION EXCEPTIONならUPDATE

準備

下記の様な商品テーブルを用意する：

drop table if exists upsert_products;
create table upsert_products(
    id serial
    ,item_cd varchar(100) -- 商品コード
    ,name varchar(100) -- 商品名
    ,primary key(id)
    --,unique (item_cd)
);

商品コードは重複させずに登録したものとする。

UNIQUE制約の有無でどう変わるかも確認したいので、

UNIQUE無しと制約付きで試す。

このテーブルに対して、

下記のようなClojureスクリプトで2つスレッドを作成し、

同じ商品コードの商品を10件ずつ同時登録してみる。

(ns safe-upsert.core
  (:gen-class)
  (:require [korma.db])
  (:require [korma.core :as kc]))

(korma.db/defdb db
                {:user "＜DBユーザー名＞"
                 :password "＜DBパスワード＞"
                 :subname "//localhost:5432/＜DB名＞"
                 :subprotocol "postgresql"})

(defn upsert-product
  [t cd] 
  (korma.db/with-db
    db  
    (dotimes [i t]
      (kc/exec-raw ["select ＜UPSERT用SP名＞(?, ?)" [(str i) (str cd "-" i)]] :results))))

(defn -main
  [& args]
  (.start (Thread. (fn [] (upsert-product 10 "thread-a"))))
  (.start (Thread. (fn [] (upsert-product 10 "thread-b")))))

UPDATEしてNOT FOUNDならINSERT

下記のようなSPを準備する：

drop function if exists sp_upsert_product_if_not_found(varchar(100), varchar(100));
create function sp_upsert_product_if_not_found(
    p_item_cd varchar(100)
    ,p_name varchar(100)
) returns void as $$
begin
    update upsert_products
    set 
        name = p_name
    where
        item_cd = p_item_cd
    ;   

    if found then
        return;
    end if; 

    insert into upsert_products(item_cd, name)
    values(p_item_cd, p_name)
    ;   
end;
$$ language plpgsql;

UNIQUE制約無しのテーブルでスクリプトを実行した場合：

 id | item_cd |    name    
----+---------+------------
  1 | 0       | thread-b-0
  2 | 0       | thread-a-0
  3 | 1       | thread-b-1
  4 | 1       | thread-a-1
  5 | 2       | thread-b-2
  6 | 2       | thread-a-2
  7 | 3       | thread-a-3
  8 | 4       | thread-b-4
  9 | 4       | thread-a-4
 10 | 5       | thread-a-5
 11 | 6       | thread-b-6
 12 | 6       | thread-a-6
 13 | 7       | thread-b-7
 14 | 7       | thread-a-7
 15 | 8       | thread-a-8
 16 | 9       | thread-a-9
(16 行)

いくつかうまくUPSERTされたと思われるデータもあるが、かなり重複登録されてる。

別スレッドでUPDATE->INSERTする間にFOUND判定が行われたため

両方のスレッドでINSERTされてしまっていると思われる。

item_cdにUNIQUE制約をつけて同じことをしてみると、

ERROR: 重複キーが一意性制約"upsert_products_item_cd_key"に違反しています

データは：

 id | item_cd |    name    
----+---------+------------
  1 | 0       | thread-a-0
  3 | 1       | thread-a-1
  4 | 2       | thread-a-2
  5 | 3       | thread-a-3
  6 | 4       | thread-a-4
  7 | 5       | thread-a-5
  8 | 6       | thread-a-6
  9 | 7       | thread-a-7
 10 | 8       | thread-a-8
 11 | 9       | thread-a-9
(10 行)

重複登録は防げるが、遅れた方のスレッドがエラー終了する。

CTEでUPDATEしてNOT EXISTSならINSERT

下記のようなSPを準備する：

drop function if exists sp_upsert_product_cte(varchar(100), varchar(100));
create function sp_upsert_product_cte(
    p_item_cd varchar(100)
    ,p_name varchar(100)
) returns void as $$
begin
    with up as (
        update upsert_products
        set
            name = p_name
        where
            item_cd = p_item_cd
        returning item_cd
    )
    insert into upsert_products(item_cd, name)
    select p_item_cd, p_name
    where not exists (select item_cd from up)
    ;
end;
$$ language plpgsql;

UNIQUE制約無しのテーブルでスクリプトを実行した場合：

 id | item_cd |    name    
----+---------+------------
  1 | 0       | thread-b-0
  2 | 0       | thread-a-0
  3 | 1       | thread-b-1
  4 | 1       | thread-a-1
  5 | 2       | thread-b-2
  6 | 3       | thread-a-3
  7 | 3       | thread-b-3
  8 | 4       | thread-a-4
  9 | 5       | thread-a-5
 10 | 6       | thread-a-6
 11 | 7       | thread-a-7
 12 | 8       | thread-a-8
 13 | 9       | thread-a-9
(13 行)

いくつかうまくUPSERTされたと思われるデータもあるが、やはり重複登録される。

この方法はうまくいくと思ったのだけど、上のNOT FOUND版と同じく、

UPDATE->INSERTの間に別スレッドもINSERTに到達してしまうのだろう。

item_cdにUNIQUE制約をつけて同じことをしてみた場合も

NOT FOUND版と同じで一意制約エラーとなる。

INSERT ON CONFLICT DO UPDATE

PostgreSQL 9.5で追加されたUPSERT用構文。

下記のようなSPを準備する：

drop function if exists sp_upsert_product_on_conflict(varchar(100), varchar(100));
create function sp_upsert_product_on_conflict(
    p_item_cd varchar(100)
    ,p_name varchar(100)
) returns void as $$
begin
    insert into upsert_products(item_cd, name)
    values(p_item_cd, p_name)
    on conflict (item_cd) do update
    set name = p_name
    ;
end;
$$ language plpgsql;

UNIQUE制約無しのテーブルでスクリプトを実行した場合：

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

UNIQUE制約か排他制約が必要らしい。

item_cdにUNIQUE制約をつけて実行してみると：

 id | item_cd |    name    
----+---------+------------
  2 | 0       | thread-a-0
  3 | 1       | thread-b-1
  5 | 2       | thread-b-2
  7 | 3       | thread-b-3
  9 | 4       | thread-a-4
 11 | 5       | thread-a-5
 13 | 6       | thread-a-6
 15 | 7       | thread-a-7
 17 | 8       | thread-b-8
 19 | 9       | thread-b-9
(10 行)

重複しない。使える。

INSERTしてUNIQUE_VIOLATION EXCEPTIONならUPDATE

上のON CONFLICT版は9.5以降でしか使えないので、

それ以前のバージョンでも使えるEXCEPTION版も試してみる。

下記のようなSPを準備する：

drop function if exists sp_upsert_product_on_exception(varchar(100), varchar(100));
create function sp_upsert_product_on_exception(
    p_item_cd varchar(100)
    ,p_name varchar(100)
) returns void as $$
begin
    insert into upsert_products(item_cd, name)
    values(p_item_cd, p_name)
    ;
exception when unique_violation then
    update upsert_products
    set
        name = p_name
    where
        item_cd = p_item_cd
    ;
end;
$$ language plpgsql;

UNIQUE制約無しのテーブルでスクリプトを実行した場合：

 id | item_cd |    name    
----+---------+------------
  1 | 0       | thread-a-0
  2 | 0       | thread-b-0
  3 | 1       | thread-a-1
  4 | 1       | thread-b-1
  5 | 2       | thread-a-2
  6 | 2       | thread-b-2
  7 | 3       | thread-a-3
  8 | 3       | thread-b-3
  9 | 4       | thread-a-4
 10 | 4       | thread-b-4
 11 | 5       | thread-a-5
 12 | 5       | thread-b-5
 13 | 6       | thread-a-6
 14 | 6       | thread-b-6
 15 | 7       | thread-a-7
 16 | 7       | thread-b-7
 17 | 8       | thread-a-8
 18 | 8       | thread-b-8
 19 | 9       | thread-a-9
 20 | 9       | thread-b-9
(20 行)

めっちゃ重複する。

ただINSERTしてるだけなんだから、そりゃそうか。

item_cdにUNIQUE制約をつけて実行してみると：

 id | item_cd |    name    
----+---------+------------
  1 | 0       | thread-b-0
  3 | 1       | thread-a-1
  5 | 2       | thread-a-2
  6 | 3       | thread-a-3
  8 | 4       | thread-a-4
 10 | 5       | thread-a-5
 12 | 6       | thread-a-6
 14 | 7       | thread-a-7
 16 | 8       | thread-a-8
 18 | 9       | thread-a-9
(10 行)

重複しない。使える。

おわり

ON CONFLICT版かEXCEPTION版を使うのが良さそう。

こちらのQiitaによると、ON CONFLICT版の方が速いようだ。

qiita.com

PostgreSQL 9.5以降が使える環境ならON CONFLICT版、

それ以前ならEXCEPTION版を使うのが良い、ということかな。

プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに