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

LLMS 索引： [llms.txt](/llms.txt)

---

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

```text
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.6 的受控开发数据库。Pigsty 4.5 的声明
与交付职责已经映射，但没有执行 Pigsty L1，因此结果明确标为：

```text
pigsty_l1=not-run
```

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

## 16.7.1 生成确定性事件与地理数据 {#item-16-7-1}

### 前置连接

沿用第 4 章的 libpq service：

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

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

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

[`context.sql`](/labs/ch16/context.sql) 要求：

```text
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 比较的证据。

### 资产清单

核心文件：

```text
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`](/labs/ch16/fixture-manifest.json) 固定：

```text
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 识别发布合同，两者不是
同一个东西。

### 为什么使用合成坐标

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

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

这样做可以：

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

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

### 单步建立

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

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

```text
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
```

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

```sql
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 加入事务边界。如今任何
中途错误都会回滚，不留下会挡住下一次运行的半成品。

### 扩展创建

```sql
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：

```text
pg36 ch16 spatiotemporal lab; safe to rebuild
```

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

### 时间与空间 schema

数据层：

```text
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
```

关键约束：

```text
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：

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

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

```sql
ORDER BY event_id, received_at, attempt_id
```

选择。固定：

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

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

### 建成摘要

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

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

### 逐字节回读

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

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

```bash
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 错误、裁剪失效和空间索引 {#item-16-7-2}

### 先看时间事实

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

应为：

```text
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、时区、去重或路由合同漂移。

### 验证分区目录

```bash
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。

### 对照裁剪正反例

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

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

正例：

```text
Seq Scan on delivery_event_20260308
```

反例：

```text
Append
  20260307 rows removed
  20260308 seven rows
  20260309 rows removed
```

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

### 验证围栏边界与换版

```bash
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 必须失败

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

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

```text
XX000
Operation on mixed SRID geometries
```

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

### 重叠有效期必须失败

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

预期：

```text
23P01
violates exclusion constraint geofence_version_no_overlap
```

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

### 应用写入必须失败

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

预期：

```text
42501 permission denied for table delivery_event
```

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

### GiST 与 SP-GiST 路径

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

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

固定计划包含：

```text
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；它们只证明路径可用。

### 时空联合计划

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

必须同时出现：

```text
geofence_version_no_overlap
event_20260308_location_gist_idx
ST_Covers filter
```

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

### 索引目录不能只看名字

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

13 行全部要求：

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

特别是：

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

### 完整 SQL 断言

```bash
./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；
- 应用权限；
- 业务校验和。

成功：

```text
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 证据与生产代价清单 {#item-16-7-3}

### 一键双周期验收

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

`all` 执行：

```text
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 结构

```text
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 保存：

```text
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`](/labs/ch16/final-state.sql) 固定：

```text
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 覆盖：

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

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

### 自动审校

[`review.py`](/labs/ch16/review.py) 不连接数据库，只审查 evidence 与冻结
source：

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

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

### reset 三道动作护栏

精确复位要求：

```bash
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：

```text
P3660
```

错误 target：

```text
P3661
```

活跃 `pg36-ch16-*` worker：

```text
P3663
```

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

### 为什么不用 CASCADE

删除顺序：

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

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

### ADR 的核心决策

[`spatiotemporal-adr.md`](/labs/ch16/spatiotemporal-adr.md) 记录：

```text
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 未证明：

```text
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.6 结果：

```text
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`](/labs/ch16/baseline-v1.4-proposal.json)
的规范化 JSON。修改合同后必须生成新 proposal checksum，不能继续引用旧
结果。

## 16.7.4 超预算时先删扩展专属细节，不删基础判断力 {#item-16-7-4}

### 本章的教学最小闭环

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

```text
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 映射删成一句话

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

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

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

### 不能把反例全删掉

本章四个关键反例：

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

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

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

如果没有预算完成：

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

结论应停在：

```text
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 边界；
- 增加唯一归属消歧规则。

必须：

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

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

---

[上一节：时空扩展的交付与观察](../06/) · [返回本章目录](../) · [下一章：合纵连横：分析加速与分布式选型](/analytics-distributed/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)