2つの独立した一意制約を持つPostgreSQLのUpsert

PostgreSQLでは、ON CONFLICT DO UPDATEを「どちらかの一意キーに一致する」汎用操作として使うことはできません。基準となるキーを1つ選ぶか、トランザクション内で両方のキーを明示的に確認し、異なる2つの行を特定する入力を拒否してください。

このチュートリアルでは、MacのTerminalで作業する開発者向けに、node-postgresを使って後者の方針を実装します。どちらのキーにも一致しなければ挿入し、1つの記事だけに一致すれば更新し、キーが異なる記事を指していれば明示的な競合を返します。すべての行は架空のものです。

SQLの例は、取得した現行ドキュメントが対象としているバージョン、PostgreSQL 18向けです。実装では、2つの即時一意制約を持つ通常の非パーティションテーブルを使います。別のバージョンのマニュアルにある例を応用する前に、サーバーのメジャーバージョンを確認してください。PostgreSQL 18のINSERTドキュメント

2つの一意制約を1つの競合対象にできない理由

記事に外部参照とスラッグがあり、それぞれを個別に一意にする必要があるとします。空のデモ用データベースで、次の完全なセットアップを実行してください。setup.sqlとして保存します。

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

これらの宣言は、独立した2つのルールを適用します。これらをUNIQUE (external_key, slug)に置き換えるとモデルが変わり、組み合わせたペアだけを一意にすればよくなります。その場合、組み合わせが異なれば外部キーとスラッグの重複が許されます。この例では、NULL可能なキーの挙動を持ち込まないよう、両方の列をNOT NULLにしています。PostgreSQLの制約

DO UPDATEでは、PostgreSQLは競合対象を指定する必要があります。列リストは一致する一意インデックスの列を表すもので、「どちらかの列」を意味するものではありません。そのため、ON CONFLICT (external_key, slug)では上記の2つの制約から競合判定に使うインデックスを推論できません。制約名を指定すると、その制約が選択されます。また、構文で使えるON CONFLICT句は1つであり、代替の処理を連ねることはできません。PostgreSQL INSERT: ON CONFLICT句

より根本的な問題は、入力された行がどの既存記事を表すかを決めることです。入力値がfeed-101とgreen-notesだとします。外部キーは青の記事に属し、スラッグは緑の記事に属しています。どちらの行を更新しても、入力された両方のキーを設定すれば、もう一方の行との競合が起きます。最初に一致したものを選ぶと、未解決の入力判断を覆い隠してしまいます。

一致数ごとの処理を決める

クエリを書く前に、次の判断表を使ってください。これは、このスキーマに対して提案するアプリケーション方針であり、PostgreSQLが自動的に提供する動作ではありません。

入力された外部キー	入力されたスラッグ	異なる一致行の数	このチュートリアルでの処理
未使用	未使用	0	記事を挿入する
既存の記事に一致	未使用	1	スラッグを含め、その記事を更新する
未使用	既存の記事に一致	1	外部キーを含め、その記事を更新する
記事Aに一致	記事Aに一致	1	記事Aを更新する
記事Aに一致	記事Bに一致	2	入力を拒否し、両方の記事を保持する

一致が1件の場合は、どちらのキーで記事を特定してもよく、もう一方のキーを変更できる方針です。インポートで必要な挙動か確認してください。外部キーを変更できない要件があるなら、スラッグだけが一致するケースはアプリケーション側で競合として扱う必要があります。

ルールがもっと単純で、「外部キーが常に記事を決定する」という場合は、通常の対象指定付きUpsertを使ってください。次の完全な文を上記のセットアップ後に実行できます。

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

ここではEXCLUDEDが指定値を提供します。もう一方の一意制約も引き続き適用されます。提案するスラッグをgreen-notesに変更すると、別の記事に切り替わるのではなく、独立した一意性競合が発生します。対象指定付きUpsertの原子的な挿入または更新の保証は、独立したエラーがないことを条件とします。PostgreSQLのINSERTドキュメント

並行書き込み中も「どちらかのキー」の判断を安定させる

検索してから挿入すると、判断と書き込みの間に隙間ができます。その間に別のトランザクションが一致するキーを挿入する可能性があります。検索にFOR UPDATEを加えると取得した行はロックされますが、一致する行がなければロック対象となる行はありません。そのため、このケースの「一致なし」経路では行ロックだけでは不十分です。PostgreSQLの明示的ロック

小規模なインポートや時折行うデスクトップ操作なら、キーを確認する前にテーブルロックSHARE ROW EXCLUSIVEを取得する方法が簡潔です。このモードは、通常の挿入、更新、削除が取得するROW EXCLUSIVEロックと競合し、同じモードの別ロックとも競合します。通常の読み取りとは両立します。PostgreSQLのテーブルレベルのロックモード

処理の流れは、トランザクションを開始し、テーブルロックを取得し、一致する行を重複なく検索し、判断表に沿って処理してからコミットする、という順序です。明示的なロックは競合するロックが解除されるまで待機し、トランザクションが終了するまで保持されます。PostgreSQL LOCK

ここではREAD COMMITTEDを明示的に使います。この分離レベルでの検索では、検索開始前にコミットされたデータが見えます。ロック取得の待機中に完了した書き込みも含まれます。このスナップショット動作とテーブルロックを組み合わせることで、判断して書き込む際の基盤を安定させます。PostgreSQLのトランザクション分離

このロックは広範囲に影響し、関係のない記事の書き込みも待機します。トランザクションは短く保ち、ロック取得からコミットまでの間に対話的なプロンプトや外部作業を挟まないでください。並行インポートが頻繁にあるなら、この方式を採用する前に、正規のキー1つで識別できるか再検討してください。

node-postgresでトランザクションを実装する

PostgreSQL 18のデモ用データベースが実行中であること、psqlコマンドが使えること、インストール済みのnode-postgresパッケージがサポートするバージョンのNode.jsが必要です。Terminalでパッケージをインストールし、セットアップファイルを読み込んでください。最初のコマンドは、デモ用ディレクトリにまだない場合、パッケージのメタデータを作成します。node-postgresのインストールと互換性

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

これらのコマンドは、通常のローカル接続設定でデモ用データベースが選択されていることを前提とします。プログラムはnew Pool()を使用します。これはpsqlと同じPostgreSQL接続環境変数を読み込みます。必要に応じてPGDATABASEにデモ用データベース名を設定してください。node-postgresの接続

次の完全なプログラムをupsert-articles.mjsとして保存してください。セットアップ用テーブルに、検索や更新の動作を変える追加のトリガーやルールがないことを前提としています。値はSQL文字列とは別に渡す位置パラメーターを使います。node-postgresのクエリ

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

すべてのトランザクション文で同じチェックアウト済みクライアントを使う必要があります。node-postgresではこれが必須です。各文を個別のpool.query()呼び出しで送ると、この接続に関する要件を満たせません。node-postgresのトランザクション

finallyブロックは、取得したクライアントを必ず解放します。ロールバック自体が失敗した場合、release(true)でその接続を破棄します。外側のpool.end()は、デスクトップ用スクリプトの終了時にプールを閉じます。node-postgresのPool API

node upsert-articles.mjsでプログラムを実行します。期待される結果は、青の記事のタイトルが更新され、キーは変わらないことです。JavaScriptの構文はNode.jsで確認しましたが、データベースの例はドキュメントと照合しただけで、ここではPostgreSQLサーバーに対して実行していません。

各分岐を確認し、エラーを明示的に処理する

セットアップ時の行を使い、upsertArticle()に渡すオブジェクトを変更して次のケースを試してください。各ケースは元のセットアップ状態を使って実行するか、先行するケースによる変更を考慮してください。

externalKey	slug	期待される結果
feed-303	amber-notes	新しい記事を挿入する
feed-101	blue-revised	青の記事を更新し、スラッグを置き換える
feed-303	blue-notes	青の記事を更新し、外部キーを置き換える
feed-101	blue-notes	青の記事のタイトルを更新する
feed-101	green-notes	ARTICLE_KEY_CONFLICTをスローし、どちらの記事も変更しない

検索結果は行の一覧なので、両方の条件に一致する記事は1件として数えられます。この2つの独立したNULL不可の一意キーでは、一致する異なる行は最大2件です。コードはLIMIT 1で2件の結果を解決したり、記事を削除したり、入力を黙って無視したりしません。

ARTICLE_KEY_CONFLICTはアプリケーションで定義するコードです。PostgreSQLの23505はunique_violationを意味し、別の条件です。例を応用した後に後者が現れた場合は、すべての重複を同じ結果として扱わず、違反した制約と追加したテーブルの動作を調べてください。PostgreSQLは、メッセージ本文の解析ではなく、エラーコードを確認することを推奨しています。PostgreSQLのエラーコード

ロック取得は待機することがあります。このデスクトップ作業で待機せずに失敗させたい場合は、ロック文をLOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAITに置き換えてください。ロックをすぐに取得できないとPostgreSQLはエラーを発生させ、プログラムはロールバック処理に進みます。PostgreSQL LOCK

DO NOTHINGやMERGEで簡潔にできるか

対象を指定しないON CONFLICT DO NOTHINGは、使用可能な一意制約またはインデックスのいずれかに違反する挿入をスキップできます。スキップが意図した結果である場合に選んでください。この方法では、ここで説明した更新方針は実装できません。PostgreSQLのINSERTドキュメント

MERGEはPostgreSQL 15で導入されました。どちらかのキーを含む結合条件を指定できますが、結合した場合でも、ソース行が異なる2つの対象記事に一致したときの方針が必要です。各アクションは通常の制約の対象となり、並行実行時の動作も対象指定付きUpsertと同じではありません。PostgreSQL 15リリースノート、PostgreSQL MERGE

どちらかのキーで照合する場合は、一致件数を明示的な判断材料にしてください。0件なら挿入し、1件なら更新し、2件なら役立つ競合メッセージとともに処理を止めます。判断を完了まで維持できる並行処理方針を選び、一意制約も維持してください。記事の書き込みを直列化してよい場合、テーブルロックを使う実装は具体的な出発点になります。

出典

• INSERT — PostgreSQL Global Development Group。

• 制約 — PostgreSQL Global Development Group。

• 明示的ロック — PostgreSQL Global Development Group。

• LOCK — PostgreSQL Global Development Group。

• トランザクション分離 — PostgreSQL Global Development Group。

• PostgreSQLのエラーコード — PostgreSQL Global Development Group。

• Release 15 — PostgreSQL Global Development Group。

• MERGE — PostgreSQL Global Development Group。

• node-postgres: 概要、互換性、インストール — node-postgres。

• 接続 — node-postgres。

• クエリ — node-postgres。

• トランザクション — node-postgres。

• Pool API — node-postgres。