PostgreSQL

GROUPING SETS 让一条 SQL 同时生成多种聚合层级。销售报表可以一起返回地区与商品明细汇总、地区小计和总计。结果中混合了不同种类的行，因此，识别每行属于哪个层级，与算对金额同样重要；如果下游把所有结果行都当成普通明细，即使数据库计算正确，报表仍可能出错。 声明报表需要的层级 GROUPING SETS ((region,product),(region),()) 声明三种分组。第一种为每个实际出现的地区与商品组合输出一行，第二种为每个地区输出一行，空集合则汇总全部选中的输入行。这是在声明结果语义，具体执行方式由优化器选择。它可以在不同层级共享工作，但语法本身不保证固定的物理扫描次数，也不会消除排序和内存成本。 ROLLUP(region,product) 是相同前缀集合的简写，并包含空集合。交换两列顺序，会改变小计维度。CUBE(region,product) 还包括只按商品汇总的层级，覆盖维度的全部子集。六个独立维度对应六十四种分组集合，不是只输出六十四行；每种集合还可能包含很多不同分组，所以结果规模可能远大于层级数。 所有层级都汇总 FROM 和 WHERE 选出的总体。如果过滤条件只保留某个月的销售，总计也只代表该月。如果不同层级需要不同输入总体，直接对一个共享输入声明这些集合，可能并不符合需求。比较另一张报表的总额之前，要先确认日期、业务状态等筛选范围一致，不能只对照列名。 用 GROUPING 标记层级 某个分组集合没有使用的维度列，会在输出中填入 SQL NULL。但原始数据本来就可能含有 NULL，因此仅靠哪些列为空，无法可靠判断这是小计还是普通分组。GROUPING(region,product) 返回位掩码，说明当前集合缺少了哪些分组表达式；最右边的参数对应最低有效位。 在这里，零表示两个维度都参与分组，一表示省略商品，三表示两个维度都省略。真实空地区的小计与全表总计，可能同时显示空地区、空商品，但掩码不同。应把这个标记纳入输出契约，只有层级明确表示跨地区汇总时，才生成“所有地区”等展示标签。 表达式是否参与分组，与表达式的值是否非空，是不同问题。GROUPING 报告前者，其参数必须匹配查询中的分组表达式，并不是测试任意值是否为空的普通函数。向下游提供数值层级或其他无歧义标识，比把每个空值都替换为“小计”更可靠，也能保留真实缺失维度的含义。 坏掉的样子 把结果中所有 total 再相加，会让同一笔销售在每个层级重复计入。需要进一步汇总时，应先选择一个层级，或直接取已经算好的总计。平均值和去重计数更不能随意相加：没有按样本数加权的地区平均值，通常不能直接平均成整体平均值；重叠集合的去重数量也不能简单求和。聚合层级不是彼此独立的新增交易。 空分组集合在没有输入行时仍形成一个分组，但后续 HAVING 条件可以把这行过滤掉；没有这类过滤时，其中 count 为零，sum 为 NULL。因此，金额为空不等于查询没有返回总计行。应明确报表如何展示空总体，而不是把空值自动当作执行失败。还要避免无意重复声明分组集合；重复层级可能产生重复聚合行，并不代表多了一份独立证据。 快速验证 把下面内容保存为 grouping.sql，在可丢弃的 PostgreSQL 18 数据库中执行 psql -X -v ON_ERROR_STOP=1 -f grouping.sql。数据特意包含一个真实空地区，脚本用事务包住临时对象，末尾回滚清理。 ...

给 PostgreSQL 现有表添加外键，既是 schema 变更，也是对历史数据作出声明。新写入可以立即受到检查，而旧行仍可能保留失效引用。规划迁移或排查完整性错误时，应分别考虑约束是否执行、历史数据是否验证，以及检查发生在什么时候，不能把这三个状态混成“外键已经存在”。 执行约束不等于验证历史数据 普通启用的外键要求适用的非空引用能匹配父键。被引用列需要符合条件的唯一性，例如适当的非延迟主键、唯一约束或非部分唯一索引。子列如果没有另外声明 NOT NULL，仍然允许空值；外键本身不会把所有关系变成必填。复合引用默认使用 MATCH SIMPLE，只要一个引用列为空就不要求匹配；MATCH FULL 允许全部为空，却拒绝部分为空、部分非空的组合。 ADD CONSTRAINT … NOT VALID 跳过对现有行的初次扫描，但仍会检查新建或改变的引用，因此可以在调查旧数据时阻止新增孤儿引用。NOT VALID 并不表示规则被禁用。与此同时，数据库还不能假定所有历史行都满足该约束，迁移也不能仅凭添加语句成功就宣告数据已经完整。 VALIDATE CONSTRAINT 扫描现有数据，检查成功后才把约束标为已验证。如果仍有历史孤儿行，验证会失败，约束继续处于未验证状态。应依据权威业务关系修复数据，再次验证。为了让检查变绿而编造父记录或删除子记录，即使得到结构一致的数据库，也可能破坏真实业务事实；修复依据不能只来自外键错误本身。 把部署阶段分开处理 添加外键即使使用 NOT VALID，也会锁定引用表和被引用表，不能把它描述成完全无锁的部署方式。其价值是在添加阶段持有锁时，避免执行可能很长的历史数据扫描。VALIDATE 使用允许普通数据修改的较弱锁，但仍消耗资源，并与其他 schema 操作进行协调，所以也需要观察执行耗时和实际争用。 如果迁移目标是缩短第一个锁窗口，应先提交短暂的添加阶段，再把验证作为后续操作执行。把两步包在同一个长事务中，会让早先取得的锁一直保留到事务结束，可能抵消分阶段的收益。下面示例故意把步骤放在一个事务里，只是为了最后回滚清理整个测试环境，并不是生产锁窗口的部署模板。 PostgreSQL 不会自动给子表引用列建立索引。父键的唯一性已有索引，但更新或删除父行时，仍可能需要查找子表引用。应先检查子表已有索引，再在实际负载需要时补上适合的访问路径。声明了外键，不代表优化器一定消除关联，也不保证大范围级联删除会很快。 什么时候延迟检查 DEFERRABLE 外键可以把检查推迟到事务结束。INITIALLY DEFERRED 表示初始就采用延迟模式；SET CONSTRAINTS 可以把适用的约束切回立即检查，并检查此前尚未确认的工作。这使同一事务中先插子行、再补父行的顺序成为可能，适合需要在最终提交点保证完整性的批量操作。 延迟并不允许提交未解决的失效引用，它改变的是检查边界，而不是最终不变量。NO ACTION 可以配合延迟检查，RESTRICT 的删除限制不能延迟。级联动作也不会仅因为约束可延迟就一并推后。应一起选择动作与检查模式，不能把 DEFERRABLE 理解为临时忽略完整性而无需满足结束条件的许可。 ...