13.6 实战：为订单状态建立数据库端护栏

created -> shipped
canceled -> paid
completed -> created

CONSTRAINT sales_order_total_positive
    CHECK (total_minor > 0),

CONSTRAINT sales_order_status_domain
    CHECK (
        status IN (
            'created', 'paid', 'packing', 'shipped',
            'completed', 'canceled', 'expired'
        )
    ),

CONSTRAINT sales_order_version_nonnegative
    CHECK (version >= 0)

allowed_transition(text,text)
  IMMUTABLE
  STRICT
  PARALLEL SAFE
  SECURITY INVOKER

invalid edge       -> P3613
version not +1     -> P3615
valid edge         -> normalize updated_at, return NEW

status = paid
  <=> captured_minor = total_minor

order_snapshot(order_id)
transition_order(order_id, expected_version, target, actor)
capture_payment(order_id, expected_version, payment_ref, amount, actor)

lock order row
  -> validate expected version/status/amount
  -> set transaction-local actor
  -> insert payment
  -> update order to paid and version +1
  -> row + statement triggers
  -> deferred checks at commit

shop_ch13
├── schema_version
├── sales_order
├── payment
├── order_history
├── statement_audit
├── 7 functions
├── 1 procedure
└── 4 user triggers

pg36 ch13 routine guard lab; safe to rebuild

postgres/admin session
  └─ SET ROLE pg36_owner for reviewed DDL

pg36_owner
  ├─ NOLOGIN, non-superuser
  ├─ owns shop_ch13 objects
  └─ runs maintenance procedure

pg36_app
  ├─ LOGIN, constrained
  ├─ USAGE shop_ch13
  ├─ EXECUTE 3 public API functions
  └─ no table DML / internal function / procedure EXECUTE

SET search_path = pg_catalog, pg_temp

order_id
old_status/new_status
old_version/new_version
actor/session_actor
statement_timestamp
xid8

xid8
actor/session_actor
affected_count
ordered order_ids[]
statement_timestamp

sum(statement_audit.affected_count)
  = count(order_history)
  = sum(final order versions)
  = 10

orders=13
created=3
paid=1
canceled=4
expired=5
payments=1
history=10
statement_audit=6
affected_sum=10

database=pg36_shop
owner=pg36_owner
application role=pg36_app
model=ch04-v1
business checksum=stable

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

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

./static/labs/ch13/task.sh setup
./static/labs/ch13/task.sh catalog
./static/labs/ch13/task.sh behavior

SELECT *
FROM shop_ch13.transition_order(
    101, 0, 'canceled', 'app-cancel'
);

SELECT *
FROM shop_ch13.capture_payment(
    102, 0, 'pay-ch13-102', 2000, 'app-payment'
);

101,canceled,1
102,paid,1,pay-ch13-102

UPDATE shop_ch13.sales_order
SET status = 'canceled', version = version + 1
WHERE order_id = 105;

SQLSTATE 42501

SELECT *
FROM shop_ch13.transition_order(
    103, 0, 'shipped', 'app-invalid'
);

SQLSTATE P3613
order 103 remains created v0
history delta=0
audit delta=0

SELECT *
FROM shop_ch13.transition_order(
    104, 0, 'paid', 'app-no-payment'
);

SQLSTATE P3614
order/history/audit all rolled back

SQLSTATE P3616

SQLSTATE P3618
payment delta=0
order remains created v0

BEGIN;
CALL shop_ch13.expire_stale_orders(..., 2, 0);
COMMIT;

SQLSTATE 2D000

p_total=5
batches=[2,2,1]

p_total=0
audit delta=0

caught_state=P3613
status_after=created
version_after=0

RESET ROLE;
SET track_functions = 'all';
SET ROLE pg36_owner;

BEGIN;
-- rollback-only calls
...
SELECT ... FROM pg_stat_xact_user_functions;
ROLLBACK;

allowed_transition calls>=1
guard_order_transition calls>=1
audit_order_transition calls>=1
order_snapshot calls>=1
transition_order calls>=1

evidence="$PWD/evidence/ch13/$(date -u +%Y%m%dT%H%M%SZ)"

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

evidence/
├── manifest.txt
├── preflight.txt
├── setup.txt
├── routine-catalog.csv
├── trigger-catalog.csv
├── security-catalog.csv
├── transition-matrix.csv
├── api-happy.csv
├── invalid-transition.{exit,stdout,stderr}
├── paid-without-payment.{exit,stdout,stderr}
├── version-conflict.{exit,stdout,stderr}
├── payment-mismatch.{exit,stdout,stderr}
├── delete-payment.{exit,stdout,stderr}
├── direct-write.{exit,stdout,stderr}
├── exception-probe.csv
├── function-stats.csv
├── bulk-update.csv
├── procedure-in-transaction.{exit,stdout,stderr}
├── procedure-run.csv
├── procedure-rerun.csv
├── final-state.csv
├── verify.txt
├── review.txt
├── reset-*.{exit,stdout,stderr}
├── reset.txt
└── rebuild/
    └── 同一套第二遍证据

business_checksum=f045467816a9be6774f30312adc16402

source + fixture + direct server behavior

cluster identity
service route
primary/recovery role
PostgreSQL minor version
PgBouncer path if used
role/database declaration
secret delivery
HA behavior
metrics/logs/alerts
change window and rollback authority

pg_users:
  - name: pg36_owner
    login: false
    superuser: false
    ...

  - name: pg36_app
    login: true
    pgbouncer: true
    pool_mode: transaction
    ...

pg_databases:
  - name: pg36_shop
    owner: pg36_owner
    schemas:
      - { name: shop_ch13, owner: pg36_owner }

application routine calls
  -> Pigsty primary service
  -> PgBouncer transaction pool
  -> pg36_app

reviewed DDL, catalog, maintenance CALL
  -> Pigsty direct/default management service
  -> PostgreSQL
  -> controlled admin SET ROLE pg36_owner

validation_path=direct-postgresql

SELECT *
FROM pg_proc
WHERE oid IN (
  'shop_ch13.transition_order(bigint,bigint,text,text)'::regprocedure,
  'shop_ch13.capture_payment(bigint,bigint,text,bigint,text)'::regprocedure
);

SELECT *
FROM pg_stat_user_functions
WHERE schemaname = 'shop_ch13'
ORDER BY total_time DESC;

SELECT
    pid, backend_start, application_name,
    state, wait_event_type, wait_event,
    xact_start, query_start
FROM pg_stat_activity
WHERE datname = 'pg36_shop'
  AND application_name LIKE 'pg36-%';

SELECT
    count(*) FILTER (WHERE status = 'paid') AS paid_orders,
    count(*) FILTER (WHERE status = 'paid'
                     AND captured_minor <> total_minor) AS invalid
FROM reviewed_payment_projection;

32377d82a7ce958aa50b0077ebe99c47d27672223c3c77fd9f91072d3745de9d

PG36_RESET_TOKEN=RESET_CH13_ROUTINE_GUARD \
PG36_RESET_TARGET=pg36_shop/shop_ch13 \
  ./static/labs/ch13/task.sh reset

stop new callers / disable job schedule
  -> observe and drain active calls
  -> route application to compatible old API
  -> verify old writes still accepted
  -> revoke new EXECUTE
  -> disable/drop new trigger only if data remains valid
  -> preserve audit and migration evidence
  -> observation window
  -> later contract objects