HTTP request
  → bounded application context
  → pgxpool acquisition
  → parameterized SQL
  → one atomic PostgreSQL transaction
       inventory invariant
       idempotency ledger
       order/payment state
       outbox event
  → commit
  → stable response

application owns:
  protocol, validation, deadline, retry budget,
  response shape, trace propagation, external coordination

PostgreSQL owns:
  durable state, constraints, atomic transition,
  concurrency arbitration, idempotency record, outbox commit

Pigsty owns:
  service routing, pooler, role/database declaration,
  HA boundary, secrets delivery, monitoring and operational evidence

POST /v1/orders
  atomic inventory reservation
  request-key fingerprint
  order + item + outbox in one transaction

POST /v1/payments
  payment-key fingerprint
  exact amount and state transition
  payment + order state + outbox in one transaction

GET /v1/orders/{id}
  explicit result shape
  ordered item aggregation with LATERAL

GET /v1/orders
  keyset page
  CTE + row_number + LATERAL

/health/live   process/event-loop only; no database
/health/ready  acquire pool + database/user/writable/schema contract
/metrics       request, SQLSTATE, retry and pgxpool state
/debug/hold    lab-only controlled pool/cancellation fault

schema_version       exact application/database contract marker
inventory            non-negative stock and monotonic version
order_request        order idempotency key + fingerprint + response
sales_order          placed/paid state and trace
sales_order_item     quantity, unit price and generated total
payment_request      payment idempotency ledger
payment              one captured payment per order
outbox               event committed with the business transition
retry_fault_seq      lab-only non-transactional retry gate

service/
  go.mod / go.sum
  main.go       lifecycle and configuration
  server.go     HTTP contract, logs and health
  store.go      parameterized SQL and transactions
  model.go      request/response/error types
  metrics.go    bounded metrics and pgxpool stats

PostgreSQL 18.6
pgx v5.10.0
Go 1.26.4 runtime
QueryExecModeExec
direct PostgreSQL service
application pgxpool MaxConns=2 in the failure lab

business:
  orders=2
  payments=1
  outbox=3
  order requests=2
  payment requests=1
  SKU-001=8:v1
  SKU-002=4:v1

idempotency:
  order replay → same 1200001 response / no second decrement
  payment replay → same 1200001 response / no second payment
  same key + different payload → 409 idempotency_conflict

failure:
  statement_timeout → 57014 / HTTP 504 / committed state unchanged
  injected 40001 → whole transaction retried once / order 1200002 once
  client timeout → backend active observed=1 / after cancel=0

pool MaxConns=2:
  two database workers held
  liveness=200
  readiness=503 pool_unavailable
  business request=503 pool_unavailable
  both holders=200
  readiness after release=200

metrics:
  transaction retries=1
  idempotent replays=2
  SQLSTATE 40001=1
  SQLSTATE 57014=1
  canceled pool acquisitions=2

security:
  current_user=pg36_app
  schema CREATE=false
  table DELETE=false
  active API query after suite=0
  ch04 checksum=f8a7bfae59c6d16cd323abecfefe1014

c85a930af366a9e96be7a0e166d3d0c04faace778208743718af51f633d8044d

what may be sent
what PostgreSQL guarantees after commit
what may be returned
how failure is identified
which application/database versions may coexist
how an operator proves the target is the intended target

ALTER TABLE shop_ch12.sales_order
    ADD COLUMN note text;

CREATE TABLE shop_ch12.schema_version (
    singleton boolean PRIMARY KEY DEFAULT true,
    version integer NOT NULL,
    contract text NOT NULL,
    installed_at timestamptz NOT NULL,
    CHECK (singleton),
    CHECK (
        version = 1
        AND contract = 'pg36-ch12-service-contract-v1'
    )
);

SELECT EXISTS (
    SELECT 1
    FROM shop_ch12.schema_version
    WHERE singleton
      AND version = 1
      AND contract =
          'pg36-ch12-service-contract-v1'
);

latest migration command returned zero

all required objects, privileges and data transitions are valid

input:
  order_id positive int64

success:
  exactly one order object
  items is an array ordered by line_no
  payment is object or JSON null
  money is integer minor units + currency

absence:
  no row → domain order_not_found / HTTP 404

failure:
  database error is not rewritten as not-found

ordering key=(order_id ASC)
cursor means order_id > after
limit range=1..100
next_cursor exists only when another row is known to exist

SQLSTATE
severity
schema/table/column
constraint name
routine
message/detail/hint

404 sku_not_found
409 insufficient_inventory

expand:
  DB accepts v1 and v2

deploy:
  v2 app rolls out, v1 remains rollback-capable

observe:
  old query/writer identity reaches zero

contract:
  DB stops accepting v1 only in separate release

谁拥有足够信息？
谁能在并发与故障下强制执行？
谁能给出稳定证据？
规则变化是否需要与 schema 一起发布？

SELECT available → application sees 1
another request also sees 1
both UPDATE available = 0
both report success

UPDATE shop_ch12.inventory
SET available = available - $2,
    version = version + 1
WHERE sku = $1
  AND available >= $2
RETURNING unit_price_minor,
          currency_code,
          available;

one row → reservation succeeded
zero rows + SKU exists → insufficient
zero rows + SKU absent → not found

same key + same fingerprint
  → return the persisted first response

same key + different fingerprint
  → reject 409 idempotency_conflict

INSERT INTO shop_ch12.order_request (
    request_key,
    fingerprint
)
VALUES ($1, $2)
ON CONFLICT (request_key) DO NOTHING;

SELECT fingerprint, response
FROM shop_ch12.order_request
WHERE request_key = $1
FOR UPDATE;

either:
  inventory decremented
  order exists
  item exists
  request response exists
  order.placed outbox exists

or:
  none of them commits

INSERT INTO shop_ch12.outbox (
    event_key,
    aggregate_type,
    aggregate_id,
    event_type,
    payload,
    trace_id
)
VALUES (...);

business fact committed ↔ intent to publish committed

BEGIN
  lock order
  call payment provider over network
  update payment
COMMIT

persist intent/idempotency state
COMMIT
perform remote protocol with provider idempotency key
persist observed outcome in a new transaction
publish through outbox

expand migration compatible with current app
  → verify database post-state
  → deploy new app with old-path fallback if needed
  → shadow/observe
  → switch new read/write path
  → retain rollback shape
  → separate contract release

process broken
database dependency not ready
business route overloaded

SELECT
    current_database(),
    current_user,
    NOT pg_catalog.pg_is_in_recovery(),
    EXISTS (
        SELECT 1
        FROM shop_ch12.schema_version
        WHERE singleton
          AND version = 1
          AND contract =
              'pg36-ch12-service-contract-v1'
    );

application=pg36-api
application_version=1.0.0-rc.1
database_contract_min=1
database_contract_max=1
query_bundle_checksum=...
driver=pgx/v5.10.0
query_mode=exec
pooling_assumption=transaction-compatible

database gate:
  target identity
  migration post-state
  constraints and grants
  old/new query compatibility
  lock/WAL/replica evidence

application gate:
  runtime role
  direct/pooler path identity
  readiness
  business smoke
  cancellation/retry/idempotency
  logs and metrics

traffic gate:
  SLI/error/tail latency
  pool and database saturation
  rollback observation window

PostgreSQL 18.6 direct path
pgx v5.10.0
pg36_app least privilege
application-side pool behavior
business and failure matrix

unchanged suite through Pigsty primary → PgBouncer transaction pool
PostgreSQL 14, 15, 16, 17 compatibility
HA failover and in-flight semantics
L1 load, tail latency, WAL and replica impact
application + database owner sign-off

input type and bound
row cardinality
ordering
null and no-row semantics
locking and transaction requirement
expected SQLSTATE
result shape across schema versions

SELECT state, total_minor, currency_code
FROM shop_ch12.sales_order
WHERE order_id = $1
FOR UPDATE;

-- 不成立：$1 不会被当作列名
ORDER BY $1;

UPDATE shop_ch12.inventory
SET available = available - $2,
    version = version + 1
WHERE sku = $1
  AND available >= $2
RETURNING
    unit_price_minor,
    currency_code,
    available;

input:
  sku text matching API vocabulary
  quantity int32 in 1..1000

success:
  exactly one row
  price/currency are the values used for this order
  stock and version changed atomically

zero rows:
  SKU absent or quantity unavailable

constraint:
  available remains >= 0 for every writer

SELECT
    orders.order_id,
    orders.customer_ref,
    orders.state,
    orders.total_minor,
    orders.currency_code,
    orders.trace_id,
    orders.created_at,
    items.value,
    payment.value
...

SELECT COALESCE(
    jsonb_agg(
        jsonb_build_object(
            'line_no', line.line_no,
            'sku', line.sku,
            'quantity', line.quantity,
            'unit_price_minor', line.unit_price_minor,
            'line_total_minor', line.line_total_minor
        )
        ORDER BY line.line_no
    ),
    '[]'::jsonb
)
FROM shop_ch12.sales_order_item AS line
WHERE line.order_id = orders.order_id;

GET /v1/orders?limit=1
→ item 1200001
→ next_cursor=1200001

GET /v1/orders?limit=1&after=1200001
→ WHERE order_id > 1200001
→ item 1200002
→ next_cursor=null

WHERE orders.order_id > $1
ORDER BY orders.order_id
LIMIT $2;

string(payload)

CTE:       name a query stage and stabilize one statement's shape
window:    compute across related rows without collapsing them
LATERAL:   evaluate a right-side subquery using the current left row

FROM shop_ch12.sales_order AS orders
CROSS JOIN LATERAL (
    SELECT COALESCE(
        jsonb_agg(... ORDER BY line.line_no),
        '[]'::jsonb
    ) AS value
    FROM shop_ch12.sales_order_item AS line
    WHERE line.order_id = orders.order_id
) AS items

LEFT JOIN LATERAL (
    SELECT ...
    WHERE child.parent_id = parent.id
    ORDER BY ...
    LIMIT 1
) AS latest ON true

WITH page AS (
    SELECT
        orders.order_id,
        orders.state,
        orders.total_minor,
        orders.created_at
    FROM shop_ch12.sales_order AS orders
    WHERE orders.order_id > $1
    ORDER BY orders.order_id
    LIMIT $2
),
ranked AS (
    SELECT
        page.*,
        row_number() OVER (
            ORDER BY page.order_id
        ) AS page_position
    FROM page
)
SELECT ...
FROM ranked
CROSS JOIN LATERAL (...)
ORDER BY ranked.order_id;

page:
  use keyset + limit to bound parent rows

ranked:
  number only the bounded page

final:
  build nested items only for selected parents

row_number() OVER (ORDER BY page.order_id)

WHERE row_number() OVER (...) <= 10;

domain code
HTTP status
retryable flag
SQLSTATE when present
constraint name when present
trace_id
cause for internal log/tracing

{
  "error": {
    "code": "database_timeout",
    "message": "database statement exceeded its time budget",
    "retryable": true,
    "trace_id": "trace-timeout-001"
  }
}

{
  "msg": "request_error",
  "error_code": "database_timeout",
  "status": 504,
  "retryable": true,
  "trace_id": "trace-timeout-001",
  "sqlstate": "57014"
}

SET LOCAL statement_timeout='50ms'
SELECT pg_sleep(0.2)
→ PostgreSQL 57014
→ service 504 database_timeout

HTTP client times out while pg_sleep
→ request context canceled
→ driver cancels DB work
→ service log client_cancelled/499
→ active worker reaches zero

ch12_sales_order_state_check

if err != nil {
    return notFound
}

return controlled 500
preserve trace and internal cause
increment error metric
do not expose raw detail
page/operator alert if it represents contract drift

request context
  bounds pool acquisition
  bounds SQL execution
  triggers cancellation

transaction wrapper
  owns begin/commit/rollback
  retries only a complete safe unit

pool configuration
  fits the global connection budget
  exposes wait and cancellation evidence

config, err := pgxpool.ParseConfig(databaseURL)
if err != nil {
    return nil, err
}

config.MaxConns = maxConns
config.MinConns = 0
config.MinIdleConns = minIdleConns
config.ConnConfig.DefaultQueryExecMode =
    pgx.QueryExecModeExec
config.ConnConfig.RuntimeParams["application_name"] =
    "pg36-ch12-api"

pool, err := pgxpool.NewWithConfig(ctx, config)
if err != nil {
    return nil, err
}

pingCtx, cancel := context.WithTimeout(ctx, 3*time.Second)
defer cancel()
if err := pool.Ping(pingCtx); err != nil {
    pool.Close()
    return nil, err
}

application pgxpool MaxConns
  = one process's concurrent client connections to PgBouncer

PgBouncer pool_size/reserve/connlimit
  = server connections PgBouncer may open to PostgreSQL

concurrency in DB
≈ request rate × time actually holding a DB connection

database workers observed=2
pool canceled acquires=2
liveness=200
readiness=503
business request=503
release → readiness=200

client deadline
  total willingness to wait

HTTP/server request deadline
  service execution budget

pool acquire deadline
  queue budget before any SQL starts

statement_timeout
  PostgreSQL statement execution budget

lock_timeout
  one lock acquisition wait budget

transaction timeout / idle-in-transaction timeout
  transaction lifecycle guard

client 1500 ms
service 1200 ms
pool acquire 150 ms
statement 900 ms
lock 100 ms where appropriate
response/cleanup headroom

request context canceled
  → pgx sends cancel / stops waiting
  → PostgreSQL worker leaves active query
  → connection is reusable or discarded safely
  → transaction rolls back
  → no committed business delta

GET /debug/hold?ms=2000
client transport timeout=400 ms
active worker observed=1
active worker after cancel=0
structured log=client_cancelled / 499

503 pool_unavailable

504 database_timeout
SQLSTATE=57014

Acquire
BEGIN with explicit options
run complete callback
COMMIT
on failure ROLLBACK with bounded cleanup context
Release
classify error
retry or return

BEGIN
  read A
  compute decision
  update B → 40001
  retry update B only    ← decision used a dead snapshot

40001 serialization_failure
40P01 deadlock_detected

client did not receive COMMIT response

database did not commit

attempt 1:
  nextval=1
  raise 40001
  transaction rolls back

attempt 2:
  nextval=2
  continue
  order commits once

BEGIN
  optional lab fault before business writes
  claim request key or read existing response
  atomic inventory UPDATE ... RETURNING
  INSERT order
  INSERT item
  INSERT outbox order.placed
  persist idempotent response
COMMIT

insufficient inventory
missing SKU
same key + different fingerprint

BEGIN
  claim payment key or return existing response
  SELECT order FOR UPDATE
  require state=placed
  require amount=order total
  INSERT one payment
  UPDATE order to paid
  INSERT outbox payment.captured
  persist response
COMMIT

UNIQUE (payment.order_id)

tx, err := pool.Begin(ctx)
if err != nil { ... }
defer tx.Rollback(ctx)

wrong database
wrong user
read-only replica
schema migration incomplete
pool route not intended

{
  "status": "ok",
  "database": "pg36_shop",
  "user": "pg36_app",
  "writable": true,
  "schema_ready": true
}

can acquire
right identity
writable
schema marker

pg36_pool_acquire_total=24
pg36_pool_empty_acquire_total=2
pg36_pool_canceled_acquire_total=2
pool acquired=0
pool idle=2
pool total=2
pool max=2

two holders consume max=2
two waiting acquisitions cancel within their own budget
holders finish successfully
pool returns to acquired=0
readiness recovers

request
  → application pgxpool connection
  → HAProxy service
  → PgBouncer client connection
  → one of many PostgreSQL server connections

BEGIN
  all statements use one backend
COMMIT
backend returns to pool
next transaction may use another backend

SET app.tenant_id = 'tenant-a';
COMMIT;

BEGIN;
SELECT ...;  -- 可能已经换 backend

BEGIN;
SET LOCAL app.tenant_id = 'tenant-a';
SELECT ...;  -- same transaction/backend
COMMIT;

state lifetime <= transaction lifetime

Exec("SET LOCAL ...")   // outside explicit transaction; warning/no effect
Query("SELECT ...")     // another transaction/backend

pg_advisory_lock()
client transaction ends
backend returns, session lock may remain on backend
next client may inherit effect
original client cannot reliably unlock same backend

SQL PREPARE name AS ...
protocol-level named prepared statement
unnamed statement/extended protocol
driver statement cache
description cache
simple protocol interpolation
PgBouncer prepared-statement tracking

max_prepared_statements > 0

PgBouncer exact version
max_prepared_statements actual value
pool_mode at database/user level
driver exact version
driver query mode
query parameter/result types
DDL/cache invalidation behavior
reconnect procedure

extended protocol
automatically prepare and cache statements
single round trip after cached

config.ConnConfig.DefaultQueryExecMode =
    pgx.QueryExecModeExec

cached plan must not change result type

SET / SET SESSION
  current session
  if transaction commits, value persists after transaction

SET LOCAL
  only current transaction
  COMMIT or ROLLBACK ends it
  outside transaction block warns and has no effect

tx, err := conn.BeginTx(ctx, options)
...
_, err = tx.Exec(
    ctx,
    `SELECT set_config('app.tenant_id', $1, true)`,
    tenantID,
)
...
rows, err := tx.Query(ctx, tenantScopedSQL, ...)

SET LOCAL app.tenant_id = '<user input>';

current_setting('app.tenant_id', true)

zero rows / reject

pool.Exec(ctx, "SELECT set_config(..., true)")
pool.Query(ctx, tenantSQL)

tx, _ := pool.Begin(ctx)
tx.Exec(ctx, setLocalSQL, tenant)
tx.Query(ctx, tenantSQL)
tx.Commit(ctx)

shop_ch12.sales_order

SET search_path = pg_catalog

max_connections budget
PgBouncer topology and auth
pool_size/reserve/connlimit
queueing and admission control
pause/resume/reconnect
failover and connection storm
per-user/per-database pools
SHOW POOLS/STATS evidence

roles and ownership
default privileges
RLS and FORCE RLS
tenant/session context
SECURITY DEFINER hardening
credential rotation
TLS/HBA
audit and break-glass

request/trace ID       one logical request or business flow
idempotency key        one replayable business command
application_name       one low-cardinality workload class
query identity         one normalized SQL shape
transaction/backend    one execution attempt
outbox event key       one publishable fact
release identity       one application/database artifact

request:
  X-Request-ID=trace-order-001

database session class:
  application_name=pg36-ch12-api

durable facts:
  sales_order.trace_id=trace-order-001
  outbox.trace_id=trace-order-001
  outbox.event_key=order:order-001:placed

business replay:
  request_key=order-001
  fingerprint=sha256(canonical command fields)

application_name=pg36-ch12-api

pg36-api/tenant-123/user-456/trace-abcdef...

pg36-api-rw
pg36-worker-outbox
pg36-migration
pg36-read-report

attempt 1 → 40001 rollback
attempt 2 → commit order 1200002

request total=1
transaction retry total=1
SQLSTATE 40001 total=1
committed order total=1

{
  "msg": "request",
  "route": "orders.create",
  "status": 201,
  "duration_ms": 3.004,
  "trace_id": "trace-order-001"
}

{
  "msg": "request_error",
  "error_code": "database_timeout",
  "status": 504,
  "retryable": true,
  "trace_id": "trace-timeout-001",
  "sqlstate": "57014",
  "constraint": ""
}

PG36_DATABASE_URL
password
raw request body
full SQL arguments
stack trace in client response

parse/plan
execution CPU/IO
lock wait
client read/write wait
commit/WAL

AcquireCount
AcquireDuration
EmptyAcquireCount
EmptyAcquireWaitTime
CanceledAcquireCount
AcquiredConns
IdleConns
TotalConns
MaxConns

pg36_pool_acquire_total
pg36_pool_acquire_seconds_total
pg36_pool_empty_acquire_total
pg36_pool_canceled_acquire_total
pg36_pool_connections{state=...}

SELECT
    pid,
    application_name,
    state,
    wait_event_type,
    wait_event,
    query_id,
    xact_start,
    query_start
FROM pg_catalog.pg_stat_activity
WHERE application_name = 'pg36-ch12-api';

HTTP route/status class
domain error code in logs
SQLSTATE counter
transaction retries
idempotent replays

pg36_db_errors_total{sqlstate="40001"}=1
pg36_db_errors_total{sqlstate="57014"}=1
pg36_transaction_retries_total=1
pg36_idempotent_replays_total=2

traffic:
  requests/s by route

errors:
  status class + domain code + SQLSTATE

duration:
  p50/p95/p99 request
  DB/query duration
  pool acquire duration

saturation:
  app pool acquired/max/wait/cancel
  PgBouncer wait/server slots
  PostgreSQL active/wait/CPU/IO

UTC window
route=orders.create
trace_id
idempotency key if authorized
application version
service endpoint/pool mode

1. application log
   status/domain code/duration/trace

2. pool metrics
   acquired/max/acquire wait/canceled

3. PgBouncer/Pigsty
   client wait/server slots/service target

4. PostgreSQL
   application_name/query_id/wait/blocker/SQLSTATE

5. durable business evidence
   idempotency ledger/order/outbox

6. client outcome
   response received, timed out, or unknown

logical command committed?
what response is persisted?
is replay safe?
is an outbox event pending?

order 1200001:
  sales_order.trace_id=trace-order-001

payment 1200001:
  payment.trace_id=trace-payment-001

outbox:
  order:order-001:placed
    → trace-order-001
  order:order-retry:placed
    → trace-retry-001
  payment:pay-001:captured
    → trace-payment-001

pg_stat_activity:
  distinct application_name=[pg36-ch12-api]

t0:
  holder-1 acquires connection
  holder-2 acquires connection
  database active sleepers=2

t1:
  /health/live → 200

t2:
  /health/ready waits its 150 ms budget
  → 503 pool_unavailable

t3:
  business GET waits 100 ms request budget
  → 503 pool_unavailable

t4:
  holders complete 200
  acquired returns 0
  readiness → 200

app pool max=2
canceled acquisition=2
PostgreSQL had exactly two sleepers
no business SQL for rejected GET
liveness unaffected
recovery without process restart

trace=trace-timeout-001
fault=statement-timeout
SET LOCAL statement_timeout=50ms
pg_sleep(200ms)
SQLSTATE=57014
HTTP=504 database_timeout
state snapshot before == after

ledger has completed response
  → return it; command committed

ledger absent
  → safe to attempt command

ledger exists but incomplete
  → protocol defect/manual repair path

role identity and privilege
  + database/schema contract
  + credential delivery
  + service endpoint and routing semantics
  + application/pool configuration
  + readiness and business evidence
  + rollback/reconnect procedure

pg36_owner
  NOLOGIN
  object owner
  migration effective role

pg36_app
  LOGIN
  runtime DML only
  no CREATEDB/CREATEROLE/SUPERUSER/REPLICATION/BYPASSRLS

pg36_ro
  LOGIN
  reviewed read path

current_user=pg36_app
has_schema_privilege(CREATE)=false
has_table_privilege(DELETE)=false

pg_users:
  - name: pg36_app
    login: true
    superuser: false
    createdb: false
    createrole: false
    replication: false
    bypassrls: false
    connlimit: 40
    pgbouncer: true
    pool_mode: transaction
    pool_connlimit: 32

pg_databases:
  - name: pg36_shop
    owner: pg36_owner
    revokeconn: true
    pgbouncer: true
    pool_mode: transaction
    pool_size: 32
    pool_reserve: 8
    pool_connlimit: 64

app replicas and MaxConns
PgBouncer pool partitioning by user/database
PostgreSQL connection budget
workload hold time
HA/failover headroom

GRANT
ALTER DEFAULT PRIVILEGES
schema marker
named constraints
SECURITY DEFINER
fixture/migration

PG36_DATABASE_URL

/etc/pg36-api/runtime.env

ExecStart=... --database-url 'postgres://user:password@...'

host/service DNS
port
database
user
sslmode
target_session_attrs if used
connect timeout
application_name
query mode
pool bounds

online application → primary :5433
reviewed DDL/admin → default :5436

application_name
lock_timeout
statement_timeout
target guard
change identity
evidence directory

100 pods × MaxConns 100
→ 10,000 PgBouncer clients

service=pg36-admin user=pg36_app
direct PostgreSQL socket

validation_path=direct-postgresql
pooler_validation=not-run

cd static/labs/ch12
export PGSERVICEFILE=/secure/path/pg_service.conf
export PGSERVICE=pg36-admin
export PG36_APP_DATABASE_URL='postgres://pg36_app@pg-demo:5433/pg36_shop?sslmode=verify-full'
./task.sh all

validation_path=operator-supplied-application-endpoint
pooler_validation=behavior-run-config-identity-required

admin direct URL
application primary/PgBouncer URL

process:
  exact binary/config checksum
  liveness

service:
  DNS/VIP/HAProxy target
  TLS/auth
  primary writable identity

pool:
  pgxpool bounds
  PgBouncer mode and slots
  no unexpected waiting/cancel spike

database:
  current_database/current_user
  schema marker
  privileges
  query/constraint contract

business:
  idempotent order/payment smoke
  outbox and trace

operations:
  logs/metrics/dashboard
  rollback and reconnect

p95 < 12.3 ms
acquire count = 24
backend PID = 12345

same idempotency key does not add writes
pool saturation fails within budget
liveness does not consume DB slot
readiness removes unready instance
cancel clears backend
SQLSTATE ratio remains within error budget
no idle-in-transaction leak
primary change does not duplicate business effect
tail latency meets declared SLO under declared load

old application_name/query identity=0
old deployment replicas=0
queue/cron/ETL inventory reviewed
new error/tail latency stable
pool wait stable
rollback artifact still usable

target/model guard
  → exact shop_ch12 fixture
  → build frozen Go module
  → start as pg36_app / MaxConns=2
  → business + idempotency matrix
  → timeout/retry/cancel/pool faults
  → SQL/catalog/model verification
  → wrong-token reset refusal
  → active-service reset refusal
  → wrong-target reset refusal
  → exact reset
  → ch04 checksum verification
  → rebuild from empty
  → rerun the same suite
  → release-candidate review

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

psql -X -w \
  --dbname='service=pg36-admin application_name=pg36-ch12-preflight' \
  --command="
    SELECT
        current_database(),
        session_user,
        current_setting('server_version'),
        pg_is_in_recovery();
  "

database must be pg36_shop
target must be writable
PostgreSQL >= 14
session can SET ROLE pg36_owner
ch04-v1 marker exists
pg36_app is constrained LOGIN

cd static/labs/ch12
export PG36_EVIDENCE_DIR="$PWD/evidence/ch12/all-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh all

setup | build | run | verify | review | reset | all

curl --fail-with-body \
  -H 'Content-Type: application/json' \
  -H 'X-Request-ID: trace-order-001' \
  --data '{
    "request_key": "order-001",
    "customer_ref": "customer-001",
    "sku": "PG36-SKU-001",
    "quantity": 2
  }' \
  http://127.0.0.1:18012/v1/orders

{
  "order_id": 1200001,
  "state": "placed",
  "total_minor": 25800,
  "currency_code": "CNY"
}

SKU-001 10:v0 → 8:v1
order 1200001 placed
one order item quantity=2
order_request order-001 complete
outbox order:order-001:placed

HTTP 201
Idempotency-Replayed: true
body exactly equals first persisted response
inventory remains 8:v1
order count remains 1
outbox count remains 1

{
  "error": {
    "code": "idempotency_conflict",
    "retryable": false,
    "trace_id": "trace-order-conflict"
  }
}

quantity=999 → 409 insufficient_inventory
valid-shape missing SKU → 404 sku_not_found
unknown JSON field → 400 invalid_json
quote/SQL-shaped SKU → 400 invalid_order

request_key=order-retry
SKU-002 quantity=1
lab header X-PG36-Fault=retry-once

attempt 1 → 40001 / rollback
attempt 2 → 201 / order 1200002
SKU-002 5:v0 → 4:v1 exactly once
outbox adds exactly one event

pay-wrong / amount=1
→ 422 amount_mismatch
→ payment_request rolled back

curl --fail-with-body \
  -H 'Content-Type: application/json' \
  -H 'X-Request-ID: trace-payment-001' \
  --data '{
    "idempotency_key": "pay-001",
    "order_id": 1200001,
    "amount_minor": 25800
  }' \
  http://127.0.0.1:18012/v1/payments

{
  "payment_id": 1200001,
  "order_id": 1200001,
  "state": "captured",
  "amount_minor": 25800,
  "currency_code": "CNY"
}

payment 1200001
order 1200001 placed → paid
payment_request pay-001 complete
outbox payment:pay-001:captured

GET /v1/orders/1200001

{
  "order_id": 1200001,
  "state": "paid",
  "total_minor": 25800,
  "trace_id": "trace-order-001",
  "items": [
    {
      "line_no": 1,
      "sku": "PG36-SKU-001",
      "quantity": 2,
      "unit_price_minor": 12900,
      "line_total_minor": 25800
    }
  ],
  "payment": {
    "payment_id": 1200001,
    "state": "captured",
    "amount_minor": 25800
  }
}

limit=1, after absent
  → order 1200001 / next_cursor=1200001

limit=1, after=1200001
  → order 1200002 / next_cursor=null

SET LOCAL statement_timeout = '50ms';
SELECT pg_catalog.pg_sleep(0.2);

HTTP=504
code=database_timeout
SQLSTATE=57014
retryable=true under the idempotent request contract
state before == state after

pg36_db_errors_total{sqlstate="40001"} 1
pg36_transaction_retries_total 1

order-retry ledger=1
order=1
item=1
outbox=1
inventory decrement=1

active pg36-ch12-api sleeper observed=1
client timeout occurs
active sleeper after cancel=0

{
  "error_code": "client_cancelled",
  "status": 499,
  "trace_id": "trace-client-cancel"
}

PG36_MAX_CONNS=2

pg_stat_activity sleepers=2
pool acquired=max

pool empty acquire=2
pool canceled acquire=2
pool acquired=0
pool idle=2

manifest.txt
setup.txt
startup-ready.json
service.log
service-lab.txt
api-results.json
trace-correlation.json
client-cancel.json
pool-saturation.json
metrics.txt
db-final.json
verify.txt
model-verify-after.txt
review.txt

ch07:
  plan/statistics/parameter evidence

ch08:
  hypothesis-led diagnosis and negative controls

ch09:
  workload-bound index decision and write cost

ch10:
  concurrency invariant, retry and idempotency

ch11:
  expand/migrate/validate/switch/contract release state

服务必须把连接池预算、请求截止时间、语句超时、整事务重试、
幂等键、外部副作用边界、健康检查和可观测关联作为同一交付合同；
进程存活、一次成功请求或直连测试均不能单独证明服务可发布。

使用 transaction pooling 时，业务正确性不得依赖跨事务会话状态；
驱动 query mode、协议级 prepared 能力和 PgBouncer 配置必须按实际
版本组合验证；DDL 发布还要验证缓存计划失效后的恢复路径。

candidate_baseline=1.0.0
status=release-candidate
depends_on=v0.6 candidate
canonical checksum=
c85a930af366a9e96be7a0e166d3d0c04faace778208743718af51f633d8044d

PostgreSQL 18.6 direct endpoint
pgx v5.10.0 / QueryExecModeExec
pgxpool MaxConns=2 failure fixture
pg36_app without DDL/DELETE

exact API case inventory and status/code
replay header + same body
fixed final business cardinality
inventory decremented once
client-cancel worker cleared
pool saturation relationship
40001/57014/retry/replay metrics
trace/outbox/application_name relation
structured logs and secret absence
direct/pooler validation boundary
v0.6 dependency canonical checksum
v1.0 RC checksum and blockers

API routes and JSON shape
database contract v1
Go module and pgx version
query mode
transaction/idempotency/outbox implementation
fault matrix
evidence schema
release-candidate checksum

ch13 functions/triggers:
  use isolated SQL fixtures; compare with ch12 boundary

extensions/search/vector chapters:
  use workload scripts, not new API endpoints

ch19+ operations:
  use pgbench/SQL/fault workloads against Pigsty

ch22 pooling:
  reuse the frozen connection/error matrix

ch23 security:
  reuse runtime role and add RLS-specific fixture

export PG36_RESET_TOKEN=RESET_CH12_SERVICE_LAB
export PG36_RESET_TARGET=pg36_shop/shop_ch12
./task.sh reset

wrong action token
wrong target
unmarked schema
unknown relation/function
unmarked relation/function
any pg36-ch12-api database session

schema_remaining=0
ch04 checksum=f8a7bfae59c6d16cd323abecfefe1014

status=ok
business=orders:2/payments:1/outbox:3
contract=idempotency+atomic-reservation+outbox
failure=57014/40001/client-cancel/pool-exhaustion
observability=trace+json-log+pool-metrics
validation=pg18.6-direct/pgx-v5.10.0/pooler:not-run
release=1.0.0-rc
release_candidate_checksum=
c85a930af366a9e96be7a0e166d3d0c04faace778208743718af51f633d8044d