koturnの日記

普通の人です．ブログ上のコードはコピペ自由です．

PostgreSQLで外部テーブルに無理矢理UPSERTする

SQL

背景

PostgreSQLには外部のPostgreSQLサーバに格納されたデータをアクセスするためのpostgres_fdwモジュールがある． これにより，外部のテーブルを自身のサーバにあるかのように扱うことが可能となるが様々な落とし穴がある．

そのひとつは，UPSERT（INSERTとON CONFLICT DO UPDATE）が使用できないことである． ドキュメントにも下記の記載がある．

Note that postgres_fdw currently lacks support for INSERT statements with an ON CONFLICT DO UPDATE clause. However, the ON CONFLICT DO NOTHING clause is supported, provided a unique index inference specification is omitted.

しかし，何とかして外部テーブルにUPSERTをしなければならない場面があったので，この記事では代替手法について述べる．

準備

本記事では，下記のテーブルを例にとりUPSERT対象として扱う．

CREATE TABLE t_upsert_test (
  pkey1 VARCHAR(32) NOT NULL
  , pkey2 INTEGER NOT NULL
  , val1 VARCHAR(32) NOT NULL
  , val2 VARCHAR(32) NOT NULL
  , val3 INTEGER
  , val4 INTEGER
  , PRIMARY KEY (pkey1, pkey2)
);

他テーブルからのSELECT結果をUPSERT

通常のUPSERT

PostgreSQLの通常のUPSERT（ON CONFLICT DO NOTHING）は下記の形となる． t_upsert_test_tmp から t_upsert_test_tmp にUPSERTを行う例を示している．

INSERT INTO t_upsert_test (
  pkey1
  , pkey2
  , val1
  , val2
  , val3
  , val4
)
SELECT
  pkey1
  , pkey2
  , val1
  , val2
  , val3
  , val4
FROM
  t_upsert_test_tmp
WHERE
  val3 = 0
  AND val4 = 1
ON CONFLICT (pkey1, pkey2)
DO UPDATE
SET
  val1 = EXCLUDED.val1
  , val2 = EXCLUDED.val2
  , val3 = EXCLUDED.val3
  , val4 = EXCLUDED.val4
;

代替UPSERT

ドキュメントに記載の通り，ON CONFLICT DO NOTHING 句（INSERTできなかったレコードは無視）は使用可能である． PostgreSQLではRETURNING句を用いることで，INSERTしたレコードを取得することができる． これらとWITHと組み合わせることで，何とかUPSERTの代替を実現することができると考えた．

WITH ct1 AS (
  SELECT
    pkey1
    , pkey2
    , val1
    , val2
    , val3
    , val4
  FROM
    t_upsert_test_tmp
  WHERE
    val3 = 0
    AND val4 = 1
)
, ct2 AS (
  INSERT INTO t_upsert_test (
    pkey1
    , pkey2
    , val1
    , val2
    , val3
    , val4
  )
  SELECT
    pkey1
    , pkey2
    , val1
    , val2
    , val3
    , val4
  FROM
    ct1
  ON CONFLICT DO NOTHING
  RETURNING
    *
)
UPDATE t_upsert_test
SET
  val1 = t1.val1
  , val2 = t1.val2
  , val3 = t1.val3
  , val4 = t1.val4
FROM (
  SELECT
    pkey1
    , pkey2
    , val1
    , val2
    , val3
    , val4
  FROM
    ct1
  EXCEPT
  SELECT
    pkey1
    , pkey2
    , val1
    , val2
    , val3
    , val4
  FROM
    ct2
) t1
WHERE
  -- ※1
  t_upsert_test.pkey1 = t1.pkey1
  AND t_upsert_test.pkey2 = t1.pkey2
;

※1は主キーの一致条件を並べるだけのWHERE句である，

やっていることとしては下記の通り．

  1. t_upsert_test_tmp から t_upsert_test にUPSERTしたいレコードを抽出
  2. 抽出したレコードを t_upsert_test にINSERTし，主キー重複レコードがあった場合は無視する（ON CONFLICT DO NOTHING）
  3. 抽出したレコードとINSERTに成功したレコードの差集合を求め（これがUPDATEすべきレコード），UPDATEを実行する．

CTE（Common Table Expression）はコストが高くなりがちであるが，対象テーブルが外部テーブルであるならばCTEで一度共通化しておいた方がコストは低くなるようだ（上記の例では ct1）．

単一のUPSERT

通常のUPSERT

INSERT INTO t_upsert_test (
  pkey1
  , pkey2
  , val1
  , val2
  , val3
  , val4
)
VALUES (
  'ABCD'
  , 42
  , 'aa'
  , 'bb'
  , 1
  , 2
)
ON CONFLICT (pkey1, pkey2)
DO UPDATE
SET
  val1 = EXCLUDED.val1
  , val2 = EXCLUDED.val2
  , val3 = EXCLUDED.val3
  , val4 = EXCLUDED.val4
;

代替UPSERT

冗長な書き方となるが，下記のように代替UPSERTの形にすることができる．

WITH ct AS (
  INSERT INTO t_upsert_test (
    pkey1
    , pkey2
    , val1
    , val2
    , val3
    , val4
  )
  VALUES (
    'ABCD'
    , 42
    , 'aa'
    , 'bb'
    , 1
    , 2
  )
  ON CONFLICT DO NOTHING
  RETURNING
    *
)
UPDATE t_upsert_test
SET
  -- ※1
  val1 = 'aa'
  , val2 = 'bb'
  , val3 = 1
  , val4 = 2
WHERE
  -- ※2
  pkey1 = 'ABCD'
  AND pkey2 = 42
  -- ※3
  AND EXISTS(SELECT * FROM ct)
;

INSERTで指定した値を再度UPDATE部で指定し直さなければならないのが何とも冗長である．

※1はINSERTで指定した値のうち，主キーを除くものを指定している． ※2はINSERTで指定した主キーに対する値を条件としている． ※3はINSERTできたレコードが無かった場合，という条件指定である．

無理矢理1つのクエリにまとめる形としたが，多くの場合SQLはプログラムから発行すると思うので，

  1. INSERT（ON CONFLICT DO NOTHING）を行う
  2. INSERT件数が0件ならUPDATEを行う

といった形で，INSERTとUPDATEを別々に実行した方がよいと思われる．

まとめ

  • PostgreSQLでは外部テーブルに対し，通常のUPSERT（ON CONFLICT DO UPDATE）はできない
  • RETURNINGやEXCEPTを組み合わせることで，高コストではあるが外部テーブルへの代替UPSERTを実現することができる

代替UPSERTは通常のUPSERTに比べ，はるかに高コストであるため，件数が少ないテーブルに対してのみ用いるべきである． そもそも，外部テーブルへのUPSERTを行う必要がないように，設計を見直すべきであると思う．

参考