12.1 数据库契约与应用边界

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.4 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