16.7 实战：配送事件的时空 PoC

本节把前六节压成一个可审计的 1.4-proposal：

frozen attempts/geofences/hubs
  -> exact extension and schema ownership
  -> canonical event deduplication
  -> UTC native partition routing
  -> geometry/geography modeling
  -> temporal, boundary, distance, and plan evidence
  -> expected failures and application privileges
  -> full catalog/data checksum
  -> reset guards
  -> transactional exact reset
  -> rebuild and second review

正式证据来自 Homebrew PostgreSQL 18.4 的受控开发数据库。Pigsty 4.4 的声明与交付职责已经映射，但没有执行 Pigsty L1，因此结果明确标为：

pigsty_l1=not-run

破坏边界
task.sh all 会删除并重建带精确 marker 的 shop_ch16、 shop_ch16_ext，以及后者中的 PostGIS/btree_gist。它只适用于本书本地/开发 fixture。生产环境不得执行这条删后重建路径。

16.7.1 生成确定性事件与地理数据

前置连接

沿用第 4 章的 libpq service：

[pg36-admin]
host=/path/to/socket-or-host
port=5432
dbname=pg36_shop
user=postgres

chmod 600 /path/to/pg_service.conf
export PGSERVICEFILE=/path/to/pg_service.conf
export PGSERVICE=pg36-admin

不要把密码写进命令行、脚本、Git 或 evidence。

context.sql 要求：

database = pg36_shop
writable instance
PostgreSQL major = 14..18
session user = superuser
can SET ROLE pg36_owner
ch04-v1 physical model exists
pg36_app is constrained non-superuser LOGIN
PostGIS 3.6.4 is available or exact managed install exists
btree_gist 1.8 is available or exact managed install exists
existing ch16 schemas/extensions, if any, have exact identities

任何一项不符都停止。脚本不会“接受最接近的 PostGIS”后继续生成一套无法与 golden 比较的证据。

资产清单

核心文件：

static/labs/ch16/
├── frozen-attempts.csv
├── frozen-geofences.csv
├── frozen-hubs.csv
├── fixture.sql
├── fixture-manifest.json
├── context.sql
├── setup.sql
├── verify.sql
├── final-state.sql
├── reset.sql
├── review.py
├── task.sh
├── spatiotemporal-adr.md
├── baseline-v1.4-proposal.json
└── pigsty-declaration.example.yml

另有时间、分区、边界、距离、目录、权限与计划探针。

冻结文件身份

fixture-manifest.json 固定：

attempts
  rows=13
  distinct_events=12
  sha256=7fa1aadbba029061fbc7eb34c9f6285eabb38b438e7d0c0c8c6b820cdc738ccf

geofences
  rows=4
  zones=3
  sha256=b2da791c7adba720cf9f4fc1123546eb08036bb60ed8ed778ae5b8dc60435c66

hubs
  rows=3
  sha256=bd56e0f8f978495bc1e677270f285628163a3edc1656fe99a2a7173ffe8e18af

fixture.sql
  sha256=b254bf5d695cf1ab738fc71b573544ef526146355b18d60b99f342ca1536a860

这些 SHA-256 识别输入；release candidate checksum 识别发布合同，两者不是同一个东西。

为什么使用合成坐标

冻结数据像一个简化城市网格，但不是权威地图：

central hub (-74.00000, 40.71000)
east hub    (-73.98000, 40.71000)
airport hub (-73.87500, 40.65000)

这样做可以：

离线运行；
不引入地图许可证；
人工看懂边界点与围栏扩张；
每次得到相同 WKT 和 checksum；
隔离数据库机制与外部地理数据质量。

因此本章不能证明地址、道路、行政区或真实 GPS 精度。

单步建立

./static/labs/ch16/task.sh setup

setup 的碰撞保护先验证已有对象：

shop_ch16
  owner=pg36_owner
  exact schema marker
  all relations have marker
  no routines/operators/opclasses/opfamilies

shop_ch16_ext
  owner=pg36_owner
  exact schema marker
  exactly btree_gist 1.8 + postgis 3.6.4
  exact extension owner/trusted boundary
  no unmanaged relations/types/functions/operators/opclasses

通过后，整个重建位于一个事务：

BEGIN;
  drop exact old objects without CASCADE
  create extension schema
  create extensions
  create data schema/tables/partitions/indexes
  load fixture
  create views/grants/comments
  analyze
COMMIT;

第一次开发执行曾在视图语法处失败，这促使 setup 加入事务边界。如今任何中途错误都会回滚，不留下会挡住下一次运行的半成品。

扩展创建

SET ROLE pg36_owner;
CREATE EXTENSION btree_gist
  WITH SCHEMA shop_ch16_ext
  VERSION '1.8';
RESET ROLE;

CREATE EXTENSION postgis
  WITH SCHEMA shop_ch16_ext
  VERSION '3.6.4';

PostGIS 由管理员创建，btree_gist extension owner 是业务 owner。两个 extension 和 schema 都带相同精确 marker：

pg36 ch16 spatiotemporal lab; safe to rebuild

marker 不是安全令牌的替代品；reset 还会核对 target、完整对象清单、依赖、数据 checksum 和活跃 worker。

时间与空间 schema

数据层：

fixture_meta
ingest_attempt
event_registry
geofence_version
delivery_hub
delivery_event (partitioned parent)
  ├── delivery_event_20260307
  ├── delivery_event_20260308
  └── delivery_event_20260309
event_lateness view
event_zone_membership view
quarter_hour_volume view

关键约束：

ingest coordinates within lon/lat ranges
received_at >= occurred_at
event_registry payload consistency
geofence tstzrange is [), non-empty
geofence SRID=4326, non-empty, valid
same zone validity ranges cannot overlap
event geometry is Point/4326
partition primary key includes occurred_at
generated geography derives from geometry

确定性去重

loader 先统计同一 event_id 的 payload variant：

count(
  DISTINCT concat_ws(
    '|',
    occurred_at,
    courier_id,
    event_type,
    longitude,
    latitude,
    source_sequence
  )
)

只有 payload_variants = 1 才进入 registry。canonical 按：

ORDER BY event_id, received_at, attempt_id

选择。固定：

e003 attempts=a003,a004
canonical=a003
attempt_count=2

再由 canonical attempt 生成 Point 并写入父分区表。

建成摘要

status=fixture-ready
attempts=13
events=12
geofence_versions=4
postgis=3.6.4

这只是 setup 摘要，不是完整验收。

逐字节回读

PG36_EVIDENCE_DIR="$PWD/evidence/ch16-cycle" \
  ./static/labs/ch16/task.sh evaluate

自动化从数据库执行三条 COPY ... TO STDOUT CSV HEADER，再：

cmp frozen-attempts.csv evidence/attempts.csv
cmp frozen-geofences.csv evidence/geofences.csv
cmp frozen-hubs.csv evidence/hubs.csv

导出显式固定：

行顺序；
UTC RFC3339 风格时间；
经纬度五位小数；
WKT；
CSV header。

没有 ORDER BY 的数据库导出不具备逐字节比较意义。

16.7.2 验证 SRID 错误、裁剪失效和空间索引

先看时间事实

psql "service=pg36-admin" \
  -f static/labs/ch16/temporal-analysis.sql

应为：

dst_e002_local=2026-03-08 01:55:00
dst_e003_local=2026-03-08 03:05:00
dst_elapsed_seconds=600
duplicate_event=e003:2
late_event_ids=e001,e004
out_of_order_pair=e004->e005
partition_boundary=e008=...20260308;e009=...20260309
utc_day8_events=7

任何一个值改变，都意味着 fixture、时区、去重或路由合同漂移。

验证分区目录

psql "service=pg36-admin" \
  -f static/labs/ch16/partition-catalog.sql

固定：

partition	bound	rows
`delivery_event_20260307`	`[03-07,03-08)`	1
`delivery_event_20260308`	`[03-08,03-09)`	7
`delivery_event_20260309`	`[03-09,03-10)`	4

同时验证每张叶表 owner 与 marker。

对照裁剪正反例

psql "service=pg36-admin" \
  -f static/labs/ch16/time-pruned-plan.sql

psql "service=pg36-admin" \
  -f static/labs/ch16/time-wrapped-plan.sql

正例：

Seq Scan on delivery_event_20260308

反例：

Append
  20260307 rows removed
  20260308 seven rows
  20260309 rows removed

两条 SQL 都返回七行。计划对照证明“结果正确”与“裁剪正确”是两项验收。

验证围栏边界与换版

psql "service=pg36-admin" \
  -f static/labs/ch16/boundary-semantics.sql

固定：

scenario	event	zone/version	covers	contains	touches
at expansion	e005	central/2	t	t	f
before expansion	e004	central/1	f	f	f
shared boundary	e003	central/1	t	f	t
shared boundary	e003	east/1	t	f	t

这四行比一个“地图截图”更容易自动回归。

混合 SRID 必须失败

psql "service=pg36-admin" \
  -v VERBOSITY=verbose \
  -f static/labs/ch16/srid-mismatch.sql

预期进程退出码 3，stderr 含：

XX000
Operation on mixed SRID geometries

自动化将“正确拒绝”当成功证据。若 SQL 意外返回 false/true，说明坐标身份保护失效。

重叠有效期必须失败

psql "service=pg36-admin" \
  -v VERBOSITY=verbose \
  -f static/labs/ch16/overlap-geofence.sql

预期：

23P01
violates exclusion constraint geofence_version_no_overlap

失败语句不留下 version 99，随后 verify.sql 仍要求围栏恰好四行。

应用写入必须失败

psql "service=pg36-admin user=pg36_app" \
  -v VERBOSITY=verbose \
  -f static/labs/ch16/app-write.sql

预期：

42501 permission denied for table delivery_event

应用可读取 central day8 五行，却不能更新事件或绕过生成/分区/去重链。

GiST 与 SP-GiST 路径

psql "service=pg36-admin" \
  -f static/labs/ch16/spatial-gist-plan.sql

psql "service=pg36-admin" \
  -f static/labs/ch16/spatial-spgist-plan.sql

固定计划包含：

event_20260308_geog_gist_idx
  Index Cond: geography bbox expansion
  Filter: ST_DWithin(...,1500)

delivery_hub_location_spgist_idx
  Index Cond: geometry bbox expansion
  Filter: ST_DWithin(...,0.02)

两条探针临时关闭 Seq Scan；它们只证明路径可用。

时空联合计划

psql "service=pg36-admin" \
  -f static/labs/ch16/joint-plan.sql

必须同时出现：

geofence_version_no_overlap
event_20260308_location_gist_idx
ST_Covers filter

且不出现 20260307/20260309 事件分区。

索引目录不能只看名字

psql "service=pg36-admin" \
  -f static/labs/ch16/index-catalog.sql

13 行全部要求：

indisvalid=true
indisready=true
indislive=true
index_bytes>0
marker exact
access method/opclass exact

特别是：

geography -> gist_geography_ops
geometry  -> gist_geometry_ops_2d
hub point -> spgist_geometry_ops_2d
exclusion -> gist_text_ops + range_ops

完整 SQL 断言

./static/labs/ch16/task.sh verify

verify.sql 检查：

两个 schema 的 owner/marker；
34 个关系对象精确白名单；
extension schema 中没有未管理成员；
两项扩展版本、owner、trusted/relocatable 边界；
fixture 身份与 13/12/4/3 基数；
去重 canonical；
1/7/4 路由与两个边界事件；
DST 600 秒、迟到和乱序；
[)、非重叠、有效 geometry；
14 条 membership 和四个边界事实；
generated geography；
13 个索引状态/opclass；
应用权限；
业务校验和。

成功：

status=ok
fixture=frozen-byte-identical
time=event+ingest+validity
space=geometry+geography+4326
partition=utc-range-1+7+4
membership=14
business_checksum=53f51cef1f0bed1a5c2fc89bfad109f4

16.7.3 输出 ADR、PoC 证据与生产代价清单

一键双周期验收

PG36_EVIDENCE_DIR="$PWD/evidence/ch16-final" \
  ./static/labs/ch16/task.sh all

all 执行：

cycle-1 setup + collect + review
  -> wrong token reset guard
  -> wrong target reset guard
  -> active worker reset guard
  -> exact reset
  -> cycle-2 setup + collect + review

第二周期不是重复表演。它证明 reset 后：

数据 schema 消失；
扩展 schema 消失；
PostGIS/btree_gist 被精确移除；
第 14 章 pg_trgm/vector 保留；
同一输入能重建同一业务 checksum；
计划、权限和失败边界仍成立。

evidence 结构

evidence/ch16-final/
├── cycle-1/
│   ├── manifest.txt
│   ├── attempts.csv
│   ├── geofences.csv
│   ├── hubs.csv
│   ├── temporal-analysis.csv
│   ├── partition-catalog.csv
│   ├── time-buckets.csv
│   ├── zone-membership.csv
│   ├── boundary-semantics.csv
│   ├── distance-semantics.csv
│   ├── extension-catalog.csv
│   ├── index-catalog.csv
│   ├── security-catalog.csv
│   ├── size-catalog.csv
│   ├── *-plan.txt
│   ├── *-failure stderr/exit
│   ├── final-state.csv
│   ├── verify.txt
│   └── review.txt
├── reset-wrong-token.*
├── reset-wrong-target.*
├── reset-active-worker.*
├── reset-exact.*
└── cycle-2/
    └── same evidence set

manifest 身份

每周期 manifest 保存：

captured_at
action/service
validation_path=direct-postgresql
pigsty_reference=4.4
pigsty_l1=not-run
model_version=ch04-v1
partition_timezone=UTC
coordinate_contract=EPSG:4326-synthetic
server/database/admin/recovery
extension versions
preserved ch14 extensions
baseline canonical checksum
fixture manifest canonical checksum
all source file SHA-256

动态采集时间不进入业务 checksum。它说明“证据何时采集”，不改变数据真值。

最终状态

final-state.sql 固定：

attempts=13
events=12
duplicate_registry=e003:2
late_events=e001,e004
partition_counts=1,7,4
memberships=14
central_day8=e002,e003,e005,e006,e008
extensions=btree_gist:1.8,postgis:3.6.4
business_checksum=53f51cef1f0bed1a5c2fc89bfad109f4

checksum 覆盖：

attempts
registry
geofences and WKT
hubs and WKT
canonical events and WKT
zone memberships

它不包含执行计划、物理 OID、索引页或采集时间，因此 reset/rebuild 后仍应相同。

自动审校

review.py 不连接数据库，只审查 evidence 与冻结 source：

三份导出字节相同且 hash/行数匹配 manifest；
DST、迟到、乱序与路由事实精确；
分区、桶与 membership 守恒；
边界、距离结果精确；
扩展、索引、权限、体积目录满足合同；
直接/包裹时间计划形成反例；
GiST/SP-GiST/联合计划包含目标路径；
三个失败的退出码与 SQLSTATE 正确；
final state 与完整 verify 通过；
baseline JSON 与业务 checksum 一致。

这样可以把“数据库输出的确生成了”与“输出符合我们预先定义的结论”分开。

reset 三道动作护栏

精确复位要求：

export PG36_RESET_TOKEN=RESET_CH16_SPATIOTEMPORAL_LAB
export PG36_RESET_TARGET='pg36_shop/shop_ch16+shop_ch16_ext'

./static/labs/ch16/task.sh reset

错误 token：

P3660

错误 target：

P3661

活跃 pg36-ch16-* worker：

P3663

通过动作护栏后，reset 在事务内先 \ir verify.sql。也就是说，只有完整状态仍与本章合同相同时才开始 DROP。

为什么不用 CASCADE

删除顺序：

views
event parent (and its owned partitions/indexes)
other data tables
data schema
postgis
btree_gist
extension schema

全部使用 RESTRICT 默认语义。若外部对象意外依赖本章扩展，DROP 会失败，事务整体回滚，数据和扩展不会处于半删状态。

ADR 的核心决策

spatiotemporal-adr.md 记录：

occurred_at is event time and partition key
received_at remains ingest evidence
valid_during is non-overlapping tstzrange
UTC daily native RANGE is baseline
TimescaleDB is deferred
EPSG:4326 is canonical
geometry serves topology/index
geography serves meter distance
ST_Covers includes boundary

同时列出否决方案、代价与重开条件。ADR 的价值不是替 SQL 写说明，而是保留 “为什么这样选”和“什么新证据会让我们重选”。

生产代价清单

本 PoC 未证明：

production ingest throughput
P50/P95/P99 time-space query latency
WAL and replica lag
GiST/SP-GiST build/reindex duration
autovacuum and statistics behavior
real polygon complexity/selectivity
GPS and authoritative map quality
backup/restore on Pigsty L1
failover behavior
PostGIS/Pigsty upgrade path
TimescaleDB benefit

发布前应将这些项目变成有负责人、环境、阈值、证据路径和停止线的验收计划。

最终正式输出

两轮 Homebrew PostgreSQL 18.4 结果：

status=ok
fixture=frozen-byte-identical
time=event+ingest+validity+dst
space=geometry+geography+srid+boundary
plans=pruning+gist+spgist+joint
guards=P3660+P3661+P3663
extensions=btree_gist:1.8+postgis:3.6.4
pigsty_l1=not-run
release_candidate_checksum=13902984b3da92a66638d0d6e2f886d6d8ac5cb20ba89ec08b1527ae79d2b923

这个 checksum 对应 baseline-v1.4-proposal.json 的规范化 JSON。修改合同后必须生成新 proposal checksum，不能继续引用旧结果。

16.7.4 超预算时先删扩展专属细节，不删基础判断力

本章的教学最小闭环

如果书稿、课程或项目时间不足，最小闭环仍必须保留：

event / ingest / valid time
timestamptz + explicit timezone
DST counterexample
late / out-of-order / duplicate distinction
[) range policy
partition-key ADR
pruning positive and negative plans
geometry / geography / SRID / units
ST_SetSRID vs ST_Transform
boundary predicate counterexample
ST_DWithin vs ST_Distance vs KNN
bbox candidate + exact predicate
one joint time-space query
one expected SRID failure
one exact reset/rebuild path

删掉其中任一组，读者很可能只记住命令，不会形成判断力。

第一优先可删：扩展参数百科

可压缩：

TimescaleDB 某一版本的全部 GUC；
所有 PostGIS 子扩展列表；
每个索引 opclass 的完整矩阵；
某发行版每个包文件名；
罕见 geometry 类型函数目录。

它们变化快，也可从目标版本官方文档查到。正文应保留如何核对，而不是复制百科。

第二优先可删：未实测的高级方案

本章没有假装实现：

路网最短路；
地图匹配；
轨迹压缩；
3D/4D 几何；
raster；
全球多投影治理；
双时态修订系统；
continuous aggregate 基准。

这些可以成为后续项目，但不应挤掉本章已经可复现的基础闭环。

不能把 Pigsty 映射删成一句话

即使篇幅少，也至少保留：

package supply
preload/config when required
CREATE EXTENSION
all L1 nodes
catalog/functional validation
backup/restore and upgrade

否则读者会把本地 CREATE EXTENSION 当成生产交付。

不能把反例全删掉

本章四个关键反例：

夏令时墙上差 70 分钟，实际 600 秒；
包裹分区键后逻辑结果相同，但三分区全扫；
边界点 covers=true、contains=false；
混合 SRID 必须失败。

成功路径告诉读者“怎么写”；反例让读者知道“为什么这样写”。若只留成功截图，认知无法迁移到新业务。

生产预算不足时的正确停止线

如果没有预算完成：

代表性规模压测
Pigsty L1 节点一致性
备份恢复
故障切换
扩展升级演练
真实地图许可证/质量评审

结论应停在：

semantic and mechanical PoC passed
production release not approved

不能因为 PoC 代码整洁就降低生产验收标准。

迁移练习

读者可复制 fixture 为 ch16-spatiotemporal-v2，任选一项扩展：

改用一个真实但许可明确的公开边界数据集；
增加 GPS accuracy 与 uncertain membership；
实现围栏修订的 system time；
对 native partition 与 TimescaleDB 做同输入 A/B；
加入真实数量级并比较 GiST/SP-GiST；
为当地业务日生成可裁剪 UTC 边界；
增加唯一归属消歧规则。

必须：

新建 manifest/version；
保留旧 fixture；
写明新许可证与生成方法；
更新 expected facts/checksum；
加入至少一个新反例；
重新做 reset/rebuild 两周期；
不沿用本章 release checksum。

做到这一步，读者不只是会调用 PostGIS，而是能把时空需求变成可验证的 PostgreSQL/Pigsty 工程合同。

上一节：时空扩展的交付与观察 · 返回本章目录 · 下一章：合纵连横：分析加速与分布式选型 · 查看全书目录 · 查看索引中心

最后更新于 2026-07-30

16.6 时空扩展的交付与观察