PostgreSQLのデータ変更CTEは、共通のスナップショットを使って複数の操作を1つのステートメントにまとめます。トランザクション内の個別のステートメントは順番に実行され、先行する書き込みを読み取れます。各操作がどのように入力を受け取る必要があるかに基づいて選択してください。

MacのTerminalで作業する開発者にとって、具体的な課題は、架空の記事ワークフローを1つのSQLステートメントにするか、トランザクション内の複数のクエリにするかを決めることです。以下の例では、可視性の違い、適切なCTEワークフロー、node-postgresによる完全なトランザクションを示します。

操作をCTEにまとめると何が変わるのか？

データ変更CTEには、INSERT、UPDATE、DELETE、または現在のPostgreSQLではMERGEがWITH内に含まれます。その構成要素は同じスナップショットを共有します。物理的な変更順序は予測できません。別の構成要素が変更後の行を必要とする場合は、RETURNINGを通じて渡してください。CTEを上から順に書いても、手続き的な実行順序は確立されません。PostgreSQL: WITH Queries

BEGINとCOMMIT内の個別のSQLステートメントには、ステートメント境界があります。PostgreSQLのデフォルトのRead Committed分離レベルでは、各ステートメントは新しいスナップショットを取得し、同じトランザクションがそれ以前に行った変更を参照できます。他のトランザクションによるコミットも、ステートメント間で可視になることがあります。PostgreSQL: Transaction Isolation

どちらの方法でも、操作全体を成功または失敗のどちらかにできます。明示的なトランザクションブロックがない場合、PostgreSQLは個々のステートメントを暗黙のトランザクション内で実行します。明示的なトランザクションでは複数のステートメントをまとめられます。データ変更CTEも、より大きなトランザクション内の1つのステートメントとして使用できます。PostgreSQL: Transactions

要件 適切な出発点
変更した行を別のテーブルに直接渡す RETURNINGを使うCTE
先行するステートメントで変更した後のテーブルを読み取る トランザクション内の順次実行されるステートメント
同じ行を意図的に2段階で変更する 順次実行されるステートメント、または最終計算を1つの更新にまとめる
次のクエリを選ぶ前に中間結果を確認する アプリケーションロジックを伴う順次実行されるステートメント
独立したリクエストの待ち時間を短縮する SQLの意味論とは別に、通信方法を検討する

この判断ガイドは、文書化された可視性のルールに基づいています。まず依存関係を説明してください。次の操作が必要とするのは、返されたリレーション、新しく読み取ったテーブル、JavaScriptによる判断のどれでしょうか？この問いは、WITH内に何個の操作を収められるか数えるより有用です。

可視性の違いをローカルで再現するには？

psql内の既存のPostgreSQL 18接続、またはMac上のSQLエディターを使います。次の完全なスクリプトを1つのセッションで実行してください。作成するのは一時テーブルのみで、架空の記事を1件使用し、最後にROLLBACKで終了します。期待される値は文書に基づいて導き出したもので、準備中にデータベースの例を実行したわけではありません。

BEGIN ISOLATION LEVEL READ COMMITTED;

CREATE TEMP TABLE cte_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO cte_demo_articles (id, title, stage)
VALUES (1, 'Sample article', 'draft');

WITH edited AS (
    UPDATE cte_demo_articles
    SET stage = 'edited'
    WHERE id = 1
    RETURNING id, stage
)
SELECT a.stage AS table_stage,
       e.stage AS returned_stage
FROM cte_demo_articles AS a
JOIN edited AS e ON e.id = a.id;
-- Expected: table_stage = draft, returned_stage = edited

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: edited, because this is a subsequent statement.

UPDATE cte_demo_articles SET stage = 'draft' WHERE id = 1;

UPDATE cte_demo_articles SET stage = 'edited' WHERE id = 1;
UPDATE cte_demo_articles
SET stage = 'ready'
WHERE id = 1 AND stage = 'edited';

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: ready

ROLLBACK;

最初のクエリでは、テーブルスキャンはdraftを読み取る一方、CTEの出力にはeditedが含まれます。次のステートメントはテーブルからeditedを読み取ります。これは、変更を行うステートメント内で対象テーブルを読む場合と、CTEから返された行を読む場合の違いを示しています。PostgreSQL: WITH Queries

最後の2つの更新では、意図的に別々のステートメントで同じ行を変更します。これらを兄弟のデータ変更CTEにまとめることはサポートされていません。PostgreSQLは1つのステートメント内で同じ行に対する2回の変更を確実には実行しません。先行するCTEを参照しても、この制約はなくなりません。PostgreSQL: SELECT

データ変更CTEが適しているのはどんな場合か？

選択した記事の行をアーカイブに移すのは、リレーショナルな処理として適切な例です。削除によって対象の行を特定し、その戻り値を挿入に使います。DELETEを使うと、RETURNINGは削除された行の内容を公開します。UPDATEを使うと、デフォルトの出力には更新後の値が含まれます。PostgreSQL: Returning Data from Modified Rows

この単独の例では、テーブルと入力を自分で用意します。前の例が終わった後、1つのセッションで実行してください。

BEGIN;

CREATE TEMP TABLE move_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

CREATE TEMP TABLE move_demo_archive (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO move_demo_articles (id, title, stage)
VALUES
    (1, 'Sample article A', 'ready'),
    (2, 'Sample article B', 'draft');

WITH moved AS (
    DELETE FROM move_demo_articles
    WHERE stage = 'ready'
    RETURNING id, title, stage
)
INSERT INTO move_demo_archive (id, title, stage)
SELECT id, title, stage
FROM moved
RETURNING id, title, stage;
-- Expected: article 1, with stage ready.

SELECT id, stage FROM move_demo_articles ORDER BY id;
-- Expected: article 2, with stage draft.

ROLLBACK;

挿入はmovedを直接受け取ります。データ変更を行うWITHは、最上位のステートメントに付ける必要があります。出力が使われなくても、データ変更CTEは最後まで一度実行されます。RETURNINGを省略すると、利用可能な結果リレーションはなくなりますが、変更処理は実行されます。PostgreSQL: WITH Queries

失敗時にはトランザクション境界も重要です。挿入に失敗すると、移動処理全体は正常に完了しません。明示的なトランザクション内でエラーが発生した場合は、ROLLBACKを実行してください。中間行を正常に受け取れたことを、囲んでいるトランザクションがコミットされた証拠とみなさないでください。PostgreSQL: Transactions

node-postgresで依存するステートメントを実行するには？

次のクエリを送る前に1つの結果を調べるワークフローでは、同じクライアント上でawaitを順次呼び出します。node-postgresでは、トランザクション内のすべてのステートメントに同じクライアントを使う必要があります。別々のpool.query()呼び出しでトランザクションの各処理を送っても、同一の接続が維持されるとは限りません。node-postgres: Transactions

前提条件は、Node.js、npm、および既存のローカルPostgreSQL 18データベース接続です。TerminalでNodeプロジェクトのディレクトリに移動し、npm install pgを実行してください。次の完全なプログラムをarticle-transaction.mjsとして保存し、node article-transaction.mjsを実行します。new Client()は一般的なPostgreSQL接続用の環境変数を使います。デフォルト値が既存のデータベースに合わない場合は、それに合わせて設定してください。node-postgres: Connecting

import pg from 'pg';

const { Client } = pg;
const client = new Client();
await client.connect();

try {
  await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

  await client.query(`
    CREATE TEMP TABLE articles (
      id integer PRIMARY KEY,
      title text NOT NULL,
      stage text NOT NULL
    ) ON COMMIT DROP
  `);

  await client.query(
    'INSERT INTO articles VALUES ($1, $2, $3)',
    [1, 'Sample article', 'draft']
  );

  const first = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 RETURNING id, stage`,
    ['edited', 1]
  );
  if (first.rowCount !== 1) {
    throw new Error('Expected one article');
  }

  const second = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 AND stage = $3
     RETURNING id, stage`,
    ['ready', 1, 'edited']
  );
  if (second.rowCount !== 1) {
    throw new Error('Expected one edited article');
  }

  await client.query('COMMIT');
  console.log(second.rows);
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  await client.end();
}

期待される出力行は、id: 1およびstage: 'ready'です。コミット時に一時テーブルは削除されます。JavaScriptの構文はNode.jsで確認しました。データベースでは実行していません。

クエリ呼び出しでは、node-postgresのパラメーター化クエリAPIに従い、$1、$2、$3を使って値を個別に渡します。各呼び出しにはSQLステートメントが1つ含まれます。node-postgres: Queries

rowCountを確認すると、更新対象の行が予想外に0件だった場合、コミット前に明示的なエラーとして扱えます。rowCountはコマンドで処理された行数を示し、rowsには返されたレコードが含まれます。RETURNINGを指定しない更新は、行に影響を与えても空のrows配列を返すことがあります。node-postgres: pg.Result

ステートメントをバッチ処理するとSQLの意味論は変わるのか？

バッチとは、処理をどのように送信するかを表します。複数のSQLステートメントを1つのデータ変更CTEに変えるものではありません。

PostgreSQLのsimple-queryプロトコルでは、セミコロンで区切った複数のステートメントを受け付けます。明示的なトランザクション制御がない場合、1つのsimple Queryメッセージ内のステートメントは暗黙のトランザクションで実行されます。エラーが発生すると、残りのステートメントは実行されません。extendedプロトコルのParseメッセージは1つのステートメントしか受け付けないため、セミコロン区切りのバッチは、個別のパラメーター化クエリの汎用的な代替にはなりません。PostgreSQL: Message Flow

明示的なバッチでは、バッチの外側でのエラー処理も必要です。末尾のROLLBACKに到達する前にエラーが起きると、そのロールバックは実行されません。失敗したトランザクションを終了するには、別のロールバックリクエストを送信してください。PostgreSQL: Multiple Statements in a Simple Query

現行のnode-postgresドキュメントでは、オプトイン方式のパイプライン処理についても説明されています。これは各応答を待たずにリクエストを送信し、PostgreSQLはそれらを順番に処理します。トランザクションの次のクエリが先行する結果に依存する場合、ドキュメントではawaitを順次呼び出す方法が推奨されています。パイプライン処理が変えるのはリクエストのタイミングであり、CTEの共通スナップショットを作るものではありません。node-postgres: Pipelining

バージョンと分離レベルのどの制約を判断の基準にすべきか？

この記事のために取得した現行のPostgreSQLドキュメントでは、バージョン18が対象です。PostgreSQL 16では、データ変更CTEの本体としてINSERT、UPDATE、DELETEを使えますが、MERGEは使えません。バージョン17のドキュメントにはMERGEが含まれ、リリースノートにはMERGE RETURNINGの追加が記載されています。PostgreSQL 16のワークフローでは、新しいCTE構文をコピーするのではなく、サポート対象のデータ変更ステートメントか個別のステートメントを使ってください。PostgreSQL 16: WITH Queries、PostgreSQL 17: WITH Queries、PostgreSQL 17 Release Notes

Repeatable Readでは、順次実行されるステートメントが兄弟CTEのように動作するわけではありません。最初のトランザクション制御以外のステートメントで確立されたトランザクションスナップショットを保持しますが、後続のステートメントからは、同じトランザクション内で先に行った書き込みを引き続き参照できます。この分離レベルを使うアプリケーションは、シリアライゼーション失敗後にトランザクションを再試行できるよう準備しておく必要があります。PostgreSQL: Repeatable Read Isolation Level

MATERIALIZEDとNOT MATERIALIZEDは、データ変更CTEの可視性を回避する方法にはなりません。PostgreSQLは、副作用のないCTEではないものに対するNOT MATERIALIZEDを無視します。こうしたプランナー制御によって、同じ行への書き込み順序を確立することはできません。PostgreSQL: SELECT

返された行が次の操作への自然な入力となる場合は、データ変更CTEを選んでください。後続の処理で先行するテーブル変更やアプリケーションの判断が必要な場合は、順次実行されるステートメントを選びます。リクエストのタイミングを最適化するときも、その依存関係を保ち、複数のステートメントを一体として完了させる必要がある場合は明示的なトランザクションを使ってください。

出典