18.6 实战：设计 `pg36_shop` 生产蓝图

no setup
no DDL
no DML
no reset
no service deployment

database=pg36_shop
PostgreSQL major=18
session_user=postgres
pg36_owner=NOLOGIN non-superuser
pg36_app=LOGIN non-superuser
ch04-v1 physical model
ch13 routine guard
ch14 extension lifecycle
ch15 search quality
ch16 spatiotemporal
ch17 analytics/FDW + two shard database shells

[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

risk=L0 read-only
target=confirmed local fixture
allowed=catalog reads + JSON validation + evidence files
forbidden=DDL/DML/roles/extensions/deploy/failover/backup/reset
pigsty_l1=not-run

static/labs/ch04/task.sh verify
static/labs/ch13/task.sh verify
static/labs/ch14/task.sh verify
static/labs/ch15/task.sh verify
static/labs/ch16/task.sh verify
static/labs/ch17/task.sh verify

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ
READ ONLY;

current_database = pg36_shop
server_version_num in 18.x
session_user = postgres superuser
can inspect pg36_owner
owner role = NOLOGIN, non-superuser
app role = LOGIN, non-superuser
ch04 schema_version present

database=pg36_shop
server_major=18
server_version=18.4 (formal run)
session_user=postgres
in_recovery=false
model_version=ch04-v1
relation_checksum=f8a7bfae59c6d16cd323abecfefe1014
pigsty_reference=4.4
pigsty_l1=not-run
mutation=none

extension_name
extension_version
schema_name
owner_name
relocatable
comment

btree_gist 1.8
pg_trgm 1.6
plpgsql 1.0
postgis 3.6.4
postgres_fdw 1.2
vector 0.8.4

shop / shop_private
shop_ch13 / shop_ch14 / shop_ch15
shop_ch16 / shop_ch16_ext
shop_ch17 / shop_ch17_ext

pg36_app   LOGIN, !SUPERUSER, !BYPASSRLS
pg36_owner NOLOGIN, !SUPERUSER
postgres   LOGIN, SUPERUSER (formal local admin)

Pigsty reference mapping = documented
Pigsty L1 = not-run

product-cache-v1
order-events-v1
product-media-v1
analytics-export-v1
external-search-projection-v1

id / kind / status / owner / authority
source / sink / freshness / delivery / ordering
idempotency / rebuild / failure_mode / reconciliation
deletion / security / exit

business authority=PostgreSQL
cache identity=product_id + source version
TTL <= 300 seconds proposal
stale version never replaces newer
outage falls back to bounded PostgreSQL reads
namespace can be discarded and rebuilt

"product_business_state": "cache"

E_CACHE_AUTHORITY

business state + publication intent -> PostgreSQL transaction
delivery/replay log                 -> event bus
delivery                            -> at-least-once
ordering                            -> per order_id, no global order
idempotency                         -> stable event_id

bytes -> object storage
identity/owner/state/checksum -> PostgreSQL
immutable object version
visible only after checksum + metadata agree
orphan upload quarantined
two-phase deletion

source=snapshot or CDC
sink=versioned immutable analytical tables
watermark on every dataset
last complete state
row/aggregate/partition checksum
tombstone propagation
new generation rebuild

deferred-until-trigger

snapshot + idempotent changes
monotonic product version/tombstone
index generation
quality golden
document count/payload hash
alias swap
fallback classification
exit to PostgreSQL

blueprint contract set == declared contract set
capability references exist
external/pilot/conditional placement has trigger
every capability has owner and evidence

argparse
copy
hashlib
json
pathlib

sort object keys
compact separators
UTF-8
preserve array order

static/labs/ch18/
├── lab-contract.md
├── architecture-adr.md
├── platform-map.mmd
├── pigsty-declaration.example.yml
├── service-catalog.json
├── external-data-contracts.json
├── baseline-v1.6-proposal.json
├── lower-volume-gates.json
├── negative-cases.json
├── context.sql
├── platform-state.sql
├── extension-catalog.sql
├── schema-catalog.sql
├── role-catalog.sql
├── capability-snapshot.sql
├── validate.py
├── review.py
└── task.sh

python3 static/labs/ch18/validate.py \
  --blueprint static/labs/ch18/baseline-v1.6-proposal.json \
  --catalog static/labs/ch18/service-catalog.json \
  --contracts static/labs/ch18/external-data-contracts.json \
  --gates static/labs/ch18/lower-volume-gates.json

{
  "status": "ok",
  "counts": {
    "offerings": 4,
    "extension_bundles": 5,
    "contracts": 5,
    "capabilities": 9,
    "lower_volume_gates": 18,
    "exit_paths": 5
  }
}

python3 static/labs/ch18/validate.py \
  --blueprint static/labs/ch18/baseline-v1.6-proposal.json \
  --catalog static/labs/ch18/service-catalog.json \
  --contracts static/labs/ch18/external-data-contracts.json \
  --gates static/labs/ch18/lower-volume-gates.json \
  --negative-cases static/labs/ch18/negative-cases.json

evidence="$(mktemp -d /tmp/pg36-ch18.XXXXXX)"

PG36_EVIDENCE_DIR="$evidence" \
  static/labs/ch18/task.sh capture

status=capture-ok
mutation=none
evidence=/tmp/...

evidence="$(mktemp -d /tmp/pg36-ch18-final.XXXXXX)"

PG36_EVIDENCE_DIR="$evidence" \
  static/labs/ch18/task.sh all

status=ok
preflight=ch04+ch13+ch14+ch15+ch16+ch17
cycles=2-byte-identical
documents=catalog+contracts+blueprint+18-pending-gates
counterexamples=7-rejected
pigsty_l1=not-run
mutation=none
release_candidate_checksum=beec6b6d47075a7b3b4a6aa6ee3ca2902ef8d555547fe6c2b2b009e56c25c9eb

platform-state.csv
extension-catalog.csv
schema-catalog.csv
role-catalog.csv
capability-snapshot.csv
validation-report.json
negative-report.json
review.txt

context
decision per capability
external contracts
service offerings
Pigsty reference mapping
proposed topology
positive consequences
costs/risks
rejected alternatives
revision triggers

everything in PostgreSQL
everything split immediately
loopback FDW as production proof
Pigsty install as production readiness

proposed; accepted for lower-volume validation,
not production approval

target identity
version
procedure
raw evidence
review result
owner approval
limitations
expiry/review trigger

PostgreSQL 如何正确建模、查询、扩展与交付应用能力

这些能力如何在真实环境中持续、可恢复、可观察、可升级地成为服务