工程学习台

主题

主要章节

SQL 方言语义对照推演：PostgreSQL 18 与 Oracle Database 23 ​

教材先例：MySQL 索引、B+ 树与 EXPLAIN、MVCC、隔离级别与锁讲的是 MySQL 的实现。本页是 U56（#93）的 E02 对照页：同样的 SQL 意图，在 PostgreSQL 与 Oracle 上的语义并不相同，而且两者的差异点不是同一组。本页逐条对照 SQL、事务/锁、索引/计划、序列与迁移差异，禁止把 MySQL 行为套用过来。

证据等级（先说清） ​

本机没有 PostgreSQL（5432 未监听）与 Oracle（1521 未监听）服务。本页的所有语义均以官方文档固定版本为准（PostgreSQL 18、Oracle Database 23），并配一个可核对推演模型：

实验代码sh
python3 labs/sql-dialect-model/run.py

模型不连接任何数据库，把六组规则写成确定性断言（6/6 通过，见 labs/sql-dialect-model/evidence/u56-2026-10-31.json）。模型通过 ≠ 真实服务通过：真实 PG/Oracle 上的隔离级别行为、锁等待、执行计划、序列间隙均未运行，按票面「受阻仍算待验证」登记。真正的实机验证要等有隔离环境时补。

序列与 identity：间隙是常态，两端原因不同 ​

两端文档口径一致的核心：序列值不参与事务回滚。PG 18 文档原话是"the value obtained by nextval is not reclaimed for re-use if the calling transaction later aborts"；Oracle 23 CREATE SEQUENCE 文档是"When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back"，并明确"individual sequence numbers will appear to be skipped"。

模型验证（U56 sequenceGap=PASS）：一次 nextval 后回滚，ID 已消耗；CACHE 3 时在实例失败后丢掉未用完的缓存段，已提交的只有 [1]，2、3 永久空缺。

两端的差异在identity 的写法：

PostgreSQL 18Oracle Database 23
语法GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY（CREATE TABLE）GENERATED { ALWAYS | BY DEFAULT [ON NULL] } AS IDENTITY
底层隐式序列，sequence_options 可覆盖内部序列生成器，参数同 CREATE SEQUENCE
显式插值ALWAYS 需 OVERRIDING SYSTEM VALUEALWAYS 直接报错；BY DEFAULT 允许
约束隐含 NOT NULL隐含 NOT NULL 与 NOT DEFERRABLE，且不能再写 DEFAULT

工程结论：不要用序列/identity 值当业务不变量（如"订单号连续""ID 无缺口"）。两端都会在回滚、缓存失效、并发取号处产生空洞；要连续编号只能用业务侧独立机制并承担串行化成本。

读已提交下的并发更新：同一个"等锁"，两套结果 ​

这是本页最重要的一组对照。两端在读已提交下都会让第二个更新者等待第一个事务结束，但等待之后的动作不同。

PostgreSQL 18（官方 transaction-iso 文档）：第二个更新者在第一个提交后，"will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated"——即 EvalPlanQual 重检。真实源码（固定 REL_18_0，nodeModifyTable.c 1695–1718 行）里这条路径写得很直白：

原码c
			case TM_Updated:
				{
					TupleTableSlot *inputslot;
					TupleTableSlot *epqslot;

					if (IsolationUsesXactSnapshot())
						ereport(ERROR,
								(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
								 errmsg("could not serialize access due to concurrent update")));

					/*
					 * Already know that we're going to need to do EPQ, so
					 * fetch tuple directly into the right slot.
					 */
					EvalPlanQualBegin(context->epqstate);
					inputslot = EvalPlanQualSlot(context->epqstate, resultRelationDesc,
												 resultRelInfo->ri_RangeTableIndex);

					result = table_tuple_lock(resultRelationDesc, tupleid,
											  estate->es_snapshot,
											  inputslot, estate->es_output_cid,
											  LockTupleExclusive, LockWaitBlock,
											  TUPLE_LOCK_FLAG_FIND_LAST_VERSION,
											  &context->tmfd);

两个分支一次看清楚：可重复读/串行化（IsolationUsesXactSnapshot()）直接报 40001；读已提交走 EvalPlanQual——锁住最新版本并把 WHERE 在新版本上重算。

Oracle Database 23（官方 data-concurrency-and-consistency 文档）：等待后在阻塞事务提交时，"the waiting transaction proceeds with its intended update to the newly changed row"——不重检 WHERE，直接作用在已变更的行上。

模型把这两条规则跑成可核对的数字（U56 concurrentUpdate=PASS）：

  • 余额 100，两个事务各扣 10，WHERE 不带值条件：两端都是 100→80，第二次 matched=1。
  • 但一旦 WHERE 带旧读到的值（WHERE balance = 100，典型的乐观更新）：PG 会在新版本上重算，第二次 matched=0、余额停在 90；Oracle 没有这一步重检，matched=1、余额 80。

这就是"同一句 UPDATE ... WHERE balance = ? 在两端行为不同"的根因。把带值条件的乐观更新当跨库可移植写法是错的：PG 上它退化成"静默不更新"（要看 affected rows），Oracle 上它挡不住写丢失。要么两端都检查 affected rows，要么用版本列/行锁把语义显式化。

隔离级别与冲突码：重试的是整个事务 ​

  • PostgreSQL 可重复读遇到并发更新：ERROR: could not serialize access due to concurrent update，SQLSTATE 40001（源码里的 ERRCODE_T_R_SERIALIZATION_FAILURE 就是它）。
  • Oracle 串行化遇到事务开始后被别人改过的行：ORA-08177「Cannot serialize access for this transaction」。

（U56 isolationCodes=PASS。）两端文档都要求应用从头重试整个事务，不是只重试那条语句；PG 文档还补了一句 read-only 事务不会有序列化冲突。另外注意 Oracle 默认就是读已提交，串行化是要显式开启的；PG 默认同样是读已提交，"可重复读"在 PG 里等价于快照隔离。

空串、NULL 与补空格比较：最容易被 MySQL 经验带偏的地方 ​

Oracle 把零长度字符串当 NULL。官方 Nulls 文档原话："The database treats a character value with a length of zero as null"，并附警告建议不要把空串当 NULL 用，因为这"may not continue to be true in future releases"。Hibernate ORM 6.6.0 的 OracleDialect.isEmptyStringTreatedAsNull() 直接返回 true（固定源码可查），这也是 Java 侧 @Column(nullable=false) 在 Oracle 上被空串击穿的根源。

PostgreSQL 不这样：空串是一个正常的值，'' IS NULL 为 false。

比较语义也不同：

  • Oracle：两端都是 CHAR/字面量时用补空格比较（'a ' = 'a' 为真）；只要任一端是 VARCHAR2 就不补空格（'a ' > 'a'）。
  • PostgreSQL：bpchar（即 CHAR）"trailing spaces are treated as semantically insignificant"；而 varchar/text 的尾随空格"are semantically significant"。

（U56 nullAndPadding=PASS 六项全过。）迁移时的后果：把 Oracle 的 VARCHAR2(50) 原样挪到 PG 的 varchar(50)，原来"尾部空格不影响相等"的查询会开始失效；反过来把 PG 的 text 挪到 Oracle，空串会悄悄变成 NULL，再撞上 NOT NULL 约束。

DDL 与事务：Oracle 的隐式提交最坑迁移脚本 ​

  • Oracle：官方 transactions 文档——"The database issues an implicit COMMIT statement before and after every DDL statement." 也就是说你的迁移脚本里每一条 ALTER TABLE 都会把此前的 DML 提交掉；DDL 失败也没法把前面的 DML 一起回滚。
  • PostgreSQL：DDL 是事务性的（官方 wiki 的原话："If you put all such changes into a transaction block, you can make sure they all apply atomically or not at all"）。一个事务里 CREATE TABLE + INSERT + ROLLBACK 会连建表一起撤销。

（U56 ddlTransaction=PASS。）工程含义：同一份迁移脚本，在 PG 上可以整体原子回滚，在 Oracle 上必须拆成幂等的小步并自己设计补偿——不能假设"事务包住迁移就安全"。

索引、执行计划与 SQL 写法 ​

两端都没有 MySQL 那种"回表"概念（Oracle 有索引组织表，PG 有覆盖索引与仅索引扫描），执行计划的读法也不同：Oracle 看 EXPLAIN PLAN / DBMS_XPLAN，PG 看 EXPLAIN (ANALYZE, BUFFERS)。共同的判读顺序：先看访问路径（全表扫描 vs 索引扫描），再看连接方式与行数估算偏差。不要拿 MySQL 的 EXPLAIN 列名去套：MySQL 的 type=ref 在 PG/Oracle 的执行计划里没有对应列。

方言差异的具体写法（模型 U56 paginationAndUpsert=PASS 核对的是它们指向同一个窗口）：

意图MySQLPostgreSQL 18Oracle 23
第 3 页、每页 10 条LIMIT 20, 10LIMIT 10 OFFSET 20OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
老版本 Oracle 写法——ROWNUM BETWEEN 21 AND 30，上层 <= 30、内层 >= 21 不能写反
upsertON DUPLICATE KEY UPDATEON CONFLICT (k) DO UPDATEMERGE INTO（无语义相同的 ON CONFLICT）

这三种 upsert 不是同义词：MySQL 的 ON DUPLICATE KEY UPDATE 会被任意唯一键触发，PG 的 ON CONFLICT 要指明冲突目标，Oracle 的 MERGE 需要先 USING 一个来源集合——并发冲突下的行为要逐条核对，不能互相替换。

迁移检查清单（本页可执行产物） ​

  1. 空串：源为 Oracle 的列，在目标 PG 上要明确空串语义；Java 实体不要同时依赖 nullable=false 和空串。
  2. 序列/identity：目标端没有"连续 ID"保证；如有，改业务侧生成。
  3. 乐观更新：UPDATE ... WHERE old_value = ? 在 PG 会重检、在 Oracle 不会；两边都必须检查 affected rows。
  4. 隔离级别：确认目标端默认级别与冲突码（PG 40001 / Oracle ORA-08177），准备好整事务重试。
  5. 迁移脚本：Oracle 上逐条 DDL 都隐式提交，必须按幂等步写；PG 上可以整体事务回滚，但别把大 DDL 和大批量 DML 塞进同一事务。
  6. 分页/upsert：逐条改写为上表的方言形态，并验证边界（越界页、冲突更新）。

未运行与受阻（如实登记） ​

项目状态
真实 PostgreSQL 18 上的隔离级别/锁等待/序列间隙未运行（本机无 PG）
真实 Oracle 23 上的串行化行为/隐式提交/空串未运行（本机无 Oracle）
两端执行计划对比（EXPLAIN/DBMS_XPLAN）未运行（需服务）
迁移脚本端到端演练未运行（需两端服务）
PG/Oracle 历史版本差异（如 PG 17 与 18、Oracle 19c 与 23）未核对

实验读法 ​

实验代码sh
python3 labs/sql-dialect-model/run.py
  • sequenceGap：回滚不回填 + 缓存失败丢号。
  • concurrentUpdate：PG 重检 vs Oracle 不重检的 matched/余额差异。
  • isolationCodes：40001 vs ORA-08177。
  • nullAndPadding：空串即 NULL、CHAR 补空格、VARCHAR 尾随空格。
  • ddlTransaction：隐式提交 vs 事务性 DDL。
  • paginationAndUpsert：四种分页窗与 upsert 的等价性。

来源与版本 ​

来源版本用途
PostgreSQL 18 事务隔离、比较函数与 Nulls18重检语义、空串与补空格（官方口径）
Oracle 23 数据并发与一致性、事务23等锁后行为、隐式提交、空串警告
postgres REL_18_0 nodeModifyTable.cREL_18_0EvalPlanQual 分支（固定摘录）
Hibernate ORM 6.6.0 OracleDialect6.6.0isEmptyStringTreatedAsNull() 行为声明

访问与运行日期：2026-10-31。源码片段为 REL_18_0 tag 的连续行段，整文件 SHA-256 随摘录元数据登记；运行版本以模型证据为准。

闭卷验收 ​

本页 13 道闭卷题收进了下方自测面板：先回忆，再展开参考答案与评分点，最后给三档自评。

工程学习台　阅读讲解，学后自主自测。记录保存在本机浏览器。