PostgreSQL 数据修改 CTE 会将多个操作合并为一条语句，并共享同一快照。事务中的独立语句按顺序运行，可以读取较早语句的写入。应根据每个操作需要如何接收输入来选择。

对于在 Mac 的 Terminal 中工作的开发者，具体任务是决定合成文章工作流应放在一条 SQL 语句中，还是拆成多个事务查询。下面的示例展示了可见性差异、适用的 CTE 工作流，以及完整的 node-postgres 事务。

将操作合并到 CTE 中会有什么变化？

数据修改 CTE 会在 WITH 中包含 INSERT、UPDATE、DELETE，或者在当前版本的 PostgreSQL 中包含 MERGE。各组成部分共享同一快照；它们实际修改数据的顺序无法预测。如果其他组成部分需要变更后的行，请通过 RETURNING传递这些行。按从上到下的顺序编写 CTE，并不能确定过程执行顺序。PostgreSQL：WITH 查询

BEGIN 和 COMMIT 中的独立 SQL 语句具有语句边界。在 PostgreSQL 默认的 Read Committed 隔离级别下，每条语句都会获取新快照，并且可以看到该事务先前所做的更改。其他事务已提交的更改也可能在语句之间变得可见。PostgreSQL：事务隔离

这两种方式都可以实现全有或全无的操作。如果没有显式事务块，PostgreSQL 会在隐式事务中执行单条语句；显式事务则将多条语句组合在一起。数据修改 CTE 也可以作为更大事务中的一条语句。PostgreSQL：事务

需求 合适的起点
将修改后的行直接传入另一张表 使用 RETURNING 的 CTE
在前一条语句更改表后读取该表 事务中的顺序语句
通过两个有意安排的阶段更改同一行 顺序语句，或将最终计算合并到一条更新语句中
在选择下一条查询前检查中间结果 配合应用逻辑使用顺序语句
减少独立请求之间的等待时间 将传输方式与 SQL 语义分开评估

这份决策指南遵循文档所述的可见性规则。先描述依赖关系：下一步操作需要的是返回关系、重新读取表，还是由 JavaScript 做出的决定？与其统计多少个操作能放进 WITH，这个问题更有帮助。

如何在本地复现可见性差异？

使用 Mac 上 psql 中现有的 PostgreSQL 18 连接，或使用 SQL 编辑器。在同一会话中运行以下完整脚本。脚本只创建临时表，使用一篇合成文章，并以 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 查询

最后两次更新有意在不同语句中更改同一行。将它们合并为同级的数据修改 CTE 是不受支持的：PostgreSQL 不会可靠地在一条语句中对同一行进行两次修改。引用前一个 CTE 也无法消除这一限制。PostgreSQL：SELECT

什么时候适合使用数据修改 CTE？

将选中的文章行移入归档表是一个实用的关系型任务：删除操作确定确切的行，其返回关系则提供插入所需的数据。使用 DELETE 时，RETURNING 会公开已删除行的内容；使用 UPDATE 时，其默认输出包含更新后的值。PostgreSQL：从已修改的行返回数据

此独立示例提供了自己的表和输入。在前一个示例结束后，于同一会话中运行它。

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 查询

事务边界也会影响失败处理：如果插入失败，移动操作就无法成功完成。在显式事务中，发生错误后执行 ROLLBACK。不要把成功接收到中间行当作外层事务已提交的证明。PostgreSQL：事务

node-postgres 应如何运行有依赖关系的语句？

如果工作流需要检查一个结果后再发送下一条查询，请在同一个客户端上按顺序调用 await。node-postgres 要求事务中的每条语句都使用同一个客户端；通过不同的 pool.query() 调用分发事务步骤，并不能保证连接连续性。node-postgres：事务

前提条件是已安装 Node.js 和 npm，并且已有本地 PostgreSQL 18 数据库连接。在 Terminal 的 Node 项目目录中运行 npm install pg。将下面的完整程序保存为 article-transaction.mjs，然后运行 node article-transaction.mjs。new Client() 使用常见的 PostgreSQL 连接环境变量；如果默认值与现有数据库不匹配，请进行相应配置。node-postgres：连接

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'。提交时会删除临时表。已使用 Node.js 检查 JavaScript 语法；未执行数据库操作。

这些查询调用使用 $1、$2 和 $3 分别传递参数，遵循 node-postgres 的参数化查询 API。每次调用都包含一条 SQL 语句。node-postgres：查询

检查 rowCount，可在更新行数意外为零时，于提交前明确报错。rowCount 报告命令处理的行数，而 rows 包含返回的记录；不带 RETURNING 的更新可能会影响行，但返回的 rows 数组为空。node-postgres：pg.Result

批处理语句会改变它们的 SQL 语义吗？

批处理描述的是如何提交工作。它不会把多条 SQL 语句变成一个数据修改 CTE。

PostgreSQL 的 simple-query 协议接受以分号分隔的语句。如果没有显式事务控制，同一条 simple Query 消息中的语句会在隐式事务中执行。发生错误时，后续语句会停止执行。extended 协议的 Parse 消息只接受一条语句，因此以分号分隔的批处理不能普遍替代独立的参数化查询。PostgreSQL：消息流程

显式批处理还需要在批处理之外处理错误：如果在末尾的 ROLLBACK 执行前发生错误，就不会执行该回滚。应单独发送回滚请求，以结束失败的事务。PostgreSQL：Simple Query 中的多条语句

当前 node-postgres 文档还介绍了可选择启用的流水线模式。它会在等待每个响应之前发送请求，而 PostgreSQL 会按顺序处理这些请求。文档建议：当事务中的下一条查询依赖先前结果时，按顺序调用 await。流水线模式会改变请求时机，但不会创建共享的 CTE 快照。node-postgres：流水线模式

选择时应考虑哪些版本和隔离级别限制？

为本文检索的当前 PostgreSQL 文档标明版本为 18。PostgreSQL 16 允许在修改型 CTE 中使用 INSERT、UPDATE 和 DELETE，但不允许在其中使用 MERGE。版本 17 的文档包含 MERGE，其发行说明记录了添加 MERGE RETURNING 的情况。对于 PostgreSQL 16 工作流，应使用受支持的修改语句或单独语句，而不要照搬较新版本的 CTE 语法。PostgreSQL 16：WITH 查询，PostgreSQL 17：WITH 查询，PostgreSQL 17 发行说明

Repeatable Read 并不会让顺序语句的行为与同级 CTE 相同。它会保留由第一条非事务控制语句建立的事务快照，但后续语句仍可看到该事务自身较早的写入。使用此隔离级别的应用还必须准备好在发生序列化失败后重试事务。PostgreSQL：Repeatable Read 隔离级别

MATERIALIZED 和 NOT MATERIALIZED 不能解决数据修改 CTE 的可见性问题。对于不满足无副作用条件的 CTE，PostgreSQL 会忽略 NOT MATERIALIZED。这些规划器控制选项无法建立对同一行进行顺序写入的机制。PostgreSQL：SELECT

当返回的行自然构成下一项操作的输入时，选择数据修改 CTE。当后续工作需要读取先前的表更改或依赖应用程序的判断时，选择顺序语句。优化请求时序时，应保留这种依赖关系；当多条语句必须一起完成时，应使用显式事务。

来源