↑↓ 选择 ↵ 打开 ⌫ 改范围 完整检索页

pgsql.cc 提供对 postgresql.org 官网内容的中文翻译，由 Pigsty 团队维护。

百科 / 错误代码 / Class 44 WITH CHECK OPTION 冲突

44000 with_check_option_violation

WITH CHECK OPTION 违规

ERROR 已实测 详解 实测通过

类别
Class 44 WITH CHECK OPTION 冲突
严重等级
ERROR
条件名
with_check_option_violation
宏名称
ERRCODE_WITH_CHECK_OPTION_VIOLATION
启用版本
7.4
状态
活跃

版本覆盖

  1. 18
  2. 17
  3. 16
  4. 15
  5. 14
  1. 13
  2. 12
  3. 11
  4. 10
  1. 19 beta
  2. 20 dev

速览

44000 表示通过视图写入的行不满足该视图的 WITH CHECK OPTION 谓词。它保护的是视图写入不变量，不是表级 CHECK 对应的 23514。

含义

可自动更新的视图声明 WITH CHECK OPTION 后，PostgreSQL 会检查插入或更新后的行是否仍能通过该视图看到。执行器把视图谓词的 FALSE 和 NULL 都视为失败，因此可空谓词列的未知结果也不能通过。ExecWithCheckOptions 报告 new row violates check option for view "%s"；只有权限允许描述该行时才附带动态失败行 DETAIL。LOCAL 只检查当前视图直接定义的条件；底层视图条件不会检查，除非那些底层视图也指定了 CHECK OPTION。CASCADED 检查当前视图以及所有底层视图条件。CHECK OPTION 只支持没有 INSTEAD OF 触发器或规则的自动可更新视图；底层可由触发器更新的视图和 INSTEAD 重写都是独立边界，级联检查可能在这些边界停止或全部被忽略。选定自然案例使用的是直接可更新视图。

诊断

保存 SQLSTATE、视图名称、存在时的 DETAIL，以及通过视图发送的实际行值。用 pg_get_viewdef() 查看定义，并按 SQL 三值逻辑计算谓词：只有 TRUE 可见，FALSE 和 NULL 都会失败。确认视图是否自动可更新、使用的是 LOCAL 还是 CASCADED，以及底层视图是否有 INSTEAD OF 触发器或 INSTEAD 重写。不要只搜索表 CHECK 约束：LOCAL 不检查普通底层视图谓词，而 CASCADED 会检查这些谓词，除非遇到可由触发器更新或被重写的边界。缺少 DETAIL 可能是权限边界，不能据此判断没有进行行检查。

处理

让行的谓词结果为 TRUE；只有确认模式契约和 LOCAL/CASCADED 范围后才修改视图定义。如果视图是受保护的过滤写入接口，应保留 check option。如果涉及底层触发器可更新的视图或 INSTEAD 重写，应检查该边界及其实际生效的检查，不要假定级联检查已经到达那里。选定自动提交案例的错误后连接仍为 IDLE；若在显式事务中发生，则先回滚失败块，再用合法行重试。

可复现案例

在一次性实例上执行过的场景。其中 1 个附有可执行 SQL，正文相应小节里给出。

view_check_option_recovery PG 10 / 18 有 SQL

前置条件

  • A runner-owned disposable target is provisioned.

触发

Insert a row through a view with a visible-row predicate and WITH CHECK OPTION, then insert a row satisfying the predicate.

断言

  • SQLSTATE is 44000
  • The diagnostic identifies the view
  • The rejected row leaves autocommit usable
  • A row satisfying the view predicate is accepted

处置

Fix the row or the view predicate deliberately; do not bypass the view invariant by treating this as a table constraint failure.

清理

Drop the case schema with an owner connection.

报文

固定源码模板是 new row violates check option for view "%s" 和 Failing row contains %s.。视图名称和行渲染都是动态值。DETAIL 是诊断数据，解析器必须考虑用户值和格式变化。

报文模板

源码里的格式串，不是某一次运行的输出。%s 之类是占位符，实际报文会填入对象名与取值。适用范围一栏是核验时留下的原始英文记录，未经翻译。

主消息 new row violates check option for view "%s"
DETAIL Failing row contains %s.

来源：src/backend/executor/execMain.c @ REL_18_6

适用范围：Both the view identifier and row rendering are dynamic.

代表案例

共享 registry verify/cases/44000/snippets.json（SHA-256 7b227bca904c860388c6cf1f5b7f551412b4d67832fb91aaa682f4126072e41c）创建过滤视图，先尝试谓词外的行，再插入合法行。见公开案例导出和结构化证据。

CREATE TABLE items(id integer PRIMARY KEY, visible boolean NOT NULL, note text NOT NULL);
CREATE VIEW visible_items AS SELECT id, visible, note FROM items WHERE visible WITH CHECK OPTION;
INSERT INTO visible_items VALUES (1, false, 'hidden');
INSERT INTO visible_items VALUES (1, true, 'visible');
SELECT id, visible, note FROM visible_items ORDER BY id;

运行器会在私有 schema 中限定 registry 的表名。它断言服务器自然产生的 SQLSTATE 和视图诊断，并核对只有可见行被接受。

选定案例在 PostgreSQL 18.6 和 10.21 通过带 WITH CHECK OPTION 的视图插入 visible = false 行时观察到 44000。DETAIL 给出失败行；自动提交连接保持 IDLE，满足谓词的行随后插入成功。

版本

锁定目录从早期历史边界到正式快照都记录了该条件。固定源码在 18.6 和 10.23 都包含同一机制；选定运行只覆盖 18.6 与 10.21 的简单视图插入，不涵盖所有视图规则组合。

来源

证据

断言

每条断言都写明了是怎么核实的，以及它不覆盖什么。这一层是核验时留下的原始英文记录，照原样呈现，未经翻译。

  • 44000 is the with_check_option_violation condition in Class 44.

    核实方式Read the fixed errcodes.txt row and macro mapping.

    不覆盖This is a view write invariant, not a table CHECK constraint code.

    来源src/backend/utils/errcodes.txt

  • ExecWithCheckOptions emits new row violates check option for view "%s" with a dynamic failing-row DETAIL.

    核实方式Trace ERRCODE_WITH_CHECK_OPTION_VIOLATION in execMain.c.

    不覆盖The row representation and view name are dynamic.

    来源src/backend/executor/execMain.c · raw/calls/REL_18_6.jsonl

  • ExecWithCheckOptions treats a view check qual that evaluates to NULL or FALSE as a violation because the new tuple would not be visible through the view.

    核实方式Read the fixed ExecWithCheckOptions comment and branch before the ERRCODE_WITH_CHECK_OPTION_VIOLATION report.

    不覆盖This describes the executor check; the selected case uses a directly updatable view.

    来源src/backend/executor/execMain.c

  • The failing-row DETAIL for a view check option is conditional on permissions that allow the row description.

    核实方式Trace the WCO_VIEW_CHECK permission guard before ExecBuildSlotValueDescription.

    不覆盖DETAIL may be absent even when the check ran.

    来源src/backend/executor/execMain.c

  • LOCAL checks only conditions defined directly in the current view; ordinary underlying-view conditions are not checked unless those base views also specify CHECK OPTION. CASCADED checks the current view and all underlying base-view conditions. CHECK OPTION is supported only on automatically updatable views without an INSTEAD OF trigger or rule; a trigger-updatable base view stops a cascade, and an INSTEAD rule rewrite can cause all checks to be ignored.

    核实方式Read the fixed CREATE VIEW documentation and compare the selected case definition.

    不覆盖The selected runtime case is a direct automatically updatable view; it does not exercise nested views, a trigger-updatable base, or an INSTEAD rewrite.

    来源doc/src/sgml/ref/create_view.sgml

  • A write through a view with WITH CHECK OPTION must satisfy the view predicate; correcting the row preserves the invariant.

    核实方式Use the shared visible=true/false view case.

    不覆盖LOCAL/CASCADED view composition and UPDATE paths can add further predicates.

    来源doc/src/sgml/ref/create_view.sgml

  • The selected 44000 case passed on PostgreSQL 18.6 and 10.21 with the structured SQLSTATE, transaction-state, and repair assertions recorded in the runtime entries.

    核实方式Read the selected runner summaries and raw outputs tied to the shared snippet registry.

    不覆盖This covers the selected directly updatable view path; it does not test nested LOCAL/CASCADED views or INSTEAD OF triggers.

运行记录

目标服务器版本结果覆盖案例
latest 18.6 (Homebrew) passed view_check_option_recovery
pg10 10.21 (Debian 10.21-1.pgdg90+1) passed view_check_option_recovery