PosetgreSQLのパーティションに対してINSERT ON CONFLICTを実行する方法

PostgreSQL9.5からサポートされるUPSERT(INSERT ON CONFLICT)は、9.4以前のパーティション定義ではうまく動作しません。これは、パーティションの親テーブルに対するINSERT実行時に、子テーブルに通常の(ON CONFLICTではない)INSERTを実行するようにTRIGGERが定義されているためです。このため、親テーブルに対してINSERT ON CONFLICTを実行しても、子テーブルには(ON CONFLICTではない)通常のINSERTが実行されるため、UPSERTの動作にはなりません。

この問題を解決するには、親テーブルのINSERTに対するTRIGGERで、通常のINSERTではなくINSERT ON CONFLICTを実行するようにします。この方法では、親テーブルにINSERT ON CONFLICTが実行されると、子テーブルにもINSERT ON CONFLICTが実行されるため、UPSERTの動作になります。ただし、親テーブルに通常のINSERTを実行した場合も、子テーブルにはINSERT ON CONFLICTが実行されるとの問題があります。

親テーブルに対して通常のINSERTとINSERT ON CONFLICTの両方が実行される可能性がある場合は、親テーブルのINSERTに対するTRIGGERにおいて通常のINSERTとINSERT ON CONFLICTを使い分ける必要があります。つまり、TRIGGERに対して、今現在、どちらのINSERTが要求されているのか伝える必要があります。

どちらのINSERTが要求されているのかをTRIGGERに伝える公式の方法は、PostgreSQL9.5でもまだなさそうです。このため、何らかの伝える方法を編み出す必要があります。例えば、INSERTの要求情報を格納するテーブルを用意し、そのテーブル経由で情報を伝える方法が考えられます。

また、以下は、ダミーのPostgreSQLパラメータを使って伝える方法の例です。この例では、idとname列のテーブルhogeを作成し、idが偶数のデータを子テーブルhoge0、奇数のデータを子テーブルhoge1に格納するようにパーティション化します。親テーブルhogeのINSERTに対するTRIGGERでは、ダミーのパラメータupsert.hogeがonの場合はINSERT ON CONFLICTを、さもなければ通常のINSERTを実行するようにします。これにより、親テーブルにINSERT ON CONFLICTを実行するときは常にダミーパラメータupsert.hogeをonに設定することで、期待どおりにUPSERTを実現できます。一方、通常のINSERTを実行するときは常に設定値をon以外にします。

これは十分に試験した方法ではありません。期待どおりに動作しないケースがあるかもしれません。十分に試験するなど、使用時にはご注意ください。

-- パーティションの親テーブルを作成
CREATE TABLE hoge (
  id INTEGER PRIMARY KEY,
  name TEXT
);

-- 親テーブルに対するTRIGGERを作成
CREATE OR REPLACE FUNCTION hoge_upsert_trigger () RETURNS trigger AS $$
DECLARE
  tbl TEXT;
BEGIN
  -- id列が偶数であれば子テーブル名はhoge0、奇数であればhoge1
  tbl := 'hoge' || mod(new.id, 2);

  -- ダミーパラメータupsert.hogeの値により通常のINSERTとINSERT ON CONFLICTを使い分け
  IF current_setting('upsert.hoge') = 'on' THEN
    EXECUTE 'INSERT INTO ' || quote_ident(tbl) ||
      ' VALUES (($1).*) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name'
    USING new;
  ELSE
    EXECUTE 'INSERT INTO ' || quote_ident(tbl) || ' VALUES (($1).*)' USING new;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- TRIGGERを親テーブルに登録
CREATE TRIGGER upsert_hoge_trigger
  BEFORE INSERT ON hoge
  FOR EACH ROW EXECUTE PROCEDURE hoge_upsert_trigger();

-- id列が偶数のデータを格納する子テーブルを作成
CREATE TABLE hoge0 (
  LIKE hoge INCLUDING ALL,
  CHECK (mod(id, 2) = 0)
)
INHERITS (hoge);

-- id列が奇数のデータを格納する子テーブルを作成
CREATE TABLE hoge1 (
  LIKE hoge INCLUDING ALL,
  CHECK (mod(id, 2) = 1)
)
INHERITS (hoge);

-- ダミーパラメータをRESETでon以外(NULL)に設定して、通常のINSERTを実行
RESET upsert.hoge;
INSERT INTO hoge VALUES (1, 'one');  -- 初回INSERTなので成功
INSERT INTO hoge VALUES (1, 'first');  -- id列のユニーク制約違反により失敗

-- ダミーパラメータをonに設定してINSERT ON CONFLICTを実行
SET upsert.hoge TO 'on';
INSERT INTO hoge VALUES (1, 'first')  -- 子テーブルにはUPSERTが実行されるため成功
  ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
-- ダミーパラメータがonであれば、親テーブルに対するINSERTはON CONFLICTなしで構わない
INSERT INTO hoge VALUES (1, 'one');  -- 子テーブルにはUPSERTが実行されるため成功