PostgreSQL patterns and distributed work audit, issue #36
This existing-content audit applies #31 to #36. Its baseline is f0f0a006f7dd8ed19444939d94a2d2efa79f775e. Complete occurrence inventories, source snapshots, official-manual HTML, raw execution logs, artifact hashes, and QA reports remain in temporary storage. The issue completion comment identifies the checked commit and records the verification results.
Coverage and identity
The assigned surface has eleven lessons, fifteen Scenarios, fifteen generated Transcript/timeline families, and fifteen matching ledger and llms sections. Thirteen Scenarios are shared YAML; retry and notification listening are TypeScript client-code Scenarios with no independent Python coverage. README chapter 5/6 cells and the PostgreSQL sidebar/curriculum labels describe topics, not additional execution guarantees. Built lesson metadata derives from each lead Scenario claim and must agree with its narrowed source.
The temporary inventory retains every nonempty physical source, lesson, Transcript/timeline and llms line, plus every YAML and matching ledger scalar field, in both baseline and current snapshots. An occurrence unit may contain several statements or only syntax; the unit count is not a count of independent guarantees. IDs combine revision, path, exact line or nested field, and the unique Scenario section where applicable. Source-bearing values are retained verbatim or as lossless parsed scalar values alongside the original source snapshot.
Ordered YAML correspondence uses step identity and session/SQL/pending-resolution fields, not first matching text. Existing steps remain ordered. The outbox adds one order-state assertion at current step 11; baseline steps 11 onward correspond to current index plus one. Lesson rewrites supersede complete baseline lessons through the decisions below rather than asserting an artificial paragraph-to-paragraph match. Repeated BEGIN, COMMIT, SELECT, notes and outputs retain separate occurrence IDs.
Schedule scope in the registry is a static annotation, not an independent engine-state trace. Fresh sessions start at READ COMMITTED; B's retry callback explicitly starts REPEATABLE READ for each attempt. Notes retain preceding schedule state. A blocked dispatch and its later resolution retain the originating session and SQL. The prepared/idle-timeout cases terminate a backend and cannot use it again. Manual contracts target PostgreSQL 18; executed artifacts target the pinned PostgreSQL 18.6 server and the shown configuration.
Semantic dispositions
| Decision | Subject | Supported disposition and limits |
|---|---|---|
| A1 | Lost-update repairs | Three READ COMMITTED schedules assert balance 120. Relative UPDATE returns 110/120; B's locking read waits and returns 110; a stale version write affects zero rows before a reread and successful additive retry. Removed any-isolation, no-wait, lock-free UPDATE and universal ORM promises. All writers must follow the chosen protocol. Stale edits need reconsideration; stronger isolation can reject; cross-row and external effects are outside these repairs. |
| A2 | Check, uniqueness and conflict handling | No-UNIQUE schedule asserts two emails. The constrained schedule asserts waiting, 23505, DO NOTHING zero affected rows and DO UPDATE returning the existing id/email. Removed all-isolation and no-retry generalizations. INSERT and UNIQUE contracts scope arbiter selection, independent errors, RETURNING and key semantics. |
| A3 | Idempotency | Same-key duplicates after commit and in flight insert no row, with balances 70 then 45. Removed universal exactly-once/network promises and narrated response loss. The charge is a database balance change. The retained-key, cooperative-writer guarantee is marked † and derived from unique arbitration plus shared transaction atomicity. Payload validation, retention and complete response recovery are not implemented here. |
| A4 | Advisory locking | The exclusive-key schedule asserts try-lock false, one blocked acquisition, key 9 surviving COMMIT and key 7 becoming available at COMMIT. Removed measured-instant and one-holder-for-all-modes implications. Manual-only rollback, session end, reentrancy, shared modes, key spaces and participation requirements are distinguished from execution. |
| A5 | Queue | A/B select jobs 1/2, explicit rollback leaves job 2 available, both final states are done. Removed no-job-runs-twice, no-job-lost, real crash, global FIFO and all-wait promises. Task names execute nothing. Session-termination rollback is a manual contract; row-lock selection protects queue state, not external effects. Retained horizon guidance is scoped to old snapshots, not every open RC transaction. |
| A6 | ORM guidance | The 500ms idle timeout, 1500ms wait, missing backend, failed COMMIT and pending order are asserted database behavior. Removed every-ORM default/API/retry and every-await horizon claims. No ORM or external API runs, and no server log is captured. Production timeout choices are advice, not measured thresholds. |
| A7 | Retry | Forced RR 40001, explicit rollback, two attempts and balance 115 are asserted. Removed guaranteed success and nonfailure wording. Complete-transaction retry is a manual contract. The helper handles only 40001, defaults to five attempts, has no backoff, and does not clean up transaction state itself. Deadlock/exhaustion handling is not executed. External effects can repeat. |
| A8 | Dual write and outbox | The dual-write Receiver model uses separately committed tables in one database: counts assert an order without an event and an event without an order after CHECK failure. No process or consumer runs. Outbox asserts committed order/event 1 and absence of rolled-back order/event 2; the added exact order query closes the missing order-state observation. Relay rollback/reselection and committed deletion are asserted. Publication is narration only. Duplicate publication is marked † with a two-boundary derivation; continued delivery needs retention, retries and receiver availability, not merely SQL. |
| A9 | Saga | Reader observes four seats between committed steps; hotel update affects zero rows; compensation returns five seats. Removed visible-to-everyone/every-anomaly and universal transport claims. Older snapshots need not see the intermediate commit. The increment compensation is not idempotent. Retry identity, irreversible effects and coordination are application responsibilities, not implemented mechanisms. |
| A10 | Prepared work | Enabled preparation, backend termination, retained prepared entry/row lock, four occupied ledger slots around VACUUM, COMMIT PREPARED and later one slot are asserted. Removed server-crash execution, unconditional commit success, any-session permission and all-table/all-maintenance statements. Prepared durability/recovery restrictions and warnings are manual contracts. Only one participant and a superuser in one database are exercised. |
| A11 | Notification | Registered psql listener observes precommit/rollback silence windows, an asserted channel/payload/sender after commit, and one notification for identical same-transaction requests. Manual contracts generalize commit/folding behavior and describe LISTEN startup races, listener transaction delay and 2PC restrictions. Removed payload-truth, every-client polling and measured-latency implications. The displayed source describes psql as the chosen visible listener, without claiming that Bun lacks a notification API. No durable replay or actual outbox relay delivery is tested. |
† identifies an Entailed guarantee and its explicit derivation, not a transcript of all executions. Demonstrated behavior requires an executed assertion. Documented contracts have versioned manual support. Recommendations, topic labels and modeled application steps do not become engine guarantees through generation.
Official support
The PostgreSQL root llms.txt returned 404, so the versioned HTML manual supplies support. The inventory registry associates each lesson/Scenario with its sources. Important contracts are READ COMMITTED and concurrent updates, row and advisory locks, INSERT conflict actions, UNIQUE constraints, SKIP LOCKED, whole-transaction retry, client transaction settings, session termination, PREPARE, COMMIT PREPARED, LISTEN and NOTIFY. The original saga paper describes an application design, not a PostgreSQL engine contract.
Outside-slice reconciliation
These locations refer to the baseline. They remain with their assigned owners, not unresolved #36 work. Historical #32-#35 audit records remain historical evidence; their handoffs are not rewritten. The #34 queue/ORM handoff is resolved within this slice. The #32 retry/stale-value handoff is reflected in A1/A7; its shared FAQ/error/concept occurrences still belong to #40. The corrected #35 ordered-inventory handoff governs correspondence practice here, without adding its retrospective tooling proposals.
| Owner | Exact baseline occurrences | Required correction |
|---|---|---|
| #40 | docs/concepts/transactional-outbox.md:2,7-10,14-25,44-55,59-65 | Distinguish separately committed same-database models, narrated publication and explicit rollback from real crashes/transport. Mark duplicate-window inference; condition eventual delivery; remove polling-latency and universal no-shared-transaction promises. |
| #40 | README.md:6,15-22,55-56; docs/faq.md:8,28,56; docs/about/methodology.md:11,14,20,54,62,67-75,84-90; scripts/gen-transcripts.ts:152-153; docs/public/llms.txt:4-5; docs/.vitepress/theme/components/HomeCurriculum.vue:17 | Shared promises must distinguish asserted schedules, manual contracts and marked derivations, and YAML parity from TypeScript client-code coverage. Repairs/retries need operation, participation and failure scope. Correct generated shared promises at the generator source. |
| #38 | docs/postgres/07-pitfalls/compendium.md:26-29,40-42,61-63,76-79,83-86,90-92,96-99,110-121; scenarios/postgres/07-pitfalls/queue-bloat.yaml#claim | Single-row repairs and idempotency do not protect external effects. Queue SQL does not prove no duplicate external work. Outbox publication is not an observed receiver effect. Retry can fail, preparation needs permission/recovery, and old horizons constrain relevant reclamation rather than all maintenance. |
| #37 | docs/mysql/05-patterns/idempotency.md:1-53; docs/mysql/05-patterns/job-queue.md:1-50; docs/mysql/06-distributed/transactional-outbox.md:1-66; docs/mysql/06-distributed/sagas.md:1-52; scenarios/mysql/05-patterns/idempotency-key.yaml#claim; scenarios/mysql/05-patterns/job-queue.yaml#claim; scenarios/mysql/06-distributed/dual-write-problem.yaml#claim; scenarios/mysql/06-distributed/transactional-outbox.yaml#claim; scenarios/mysql/06-distributed/saga-compensation.yaml#claim | Reconcile the same exact-once, queue, modeled boundary, narrative publication and intermediate-visibility promises under MySQL's operation/isolation contracts. Correct each Scenario source and regenerate its own mirrors, rather than copying PostgreSQL guarantees. |
The corresponding generated part, unique llms section and ledger declaration belong to each named Scenario. The completion handoff preserves the exact outside-slice source snippets for later reconciliation. No retrospective tooling proposal or new practice, guide, diagram, failure-lab, delivery integration, or alternate harness is part of this audit.
Verification boundaries
The issue completion comment records separately the assigned real-database runs, full required gates, thirteen assigned independent Python YAML cases, two-generation stability, assertion mutation, and fresh-context built-reader QA. TypeScript retry and psql-listener execution has no Python parity. Explicit rollback, client idle time, separately committed tables, and backend termination have their stated model/execution limits. Server crashes, real networks, remote delivery, indeterminate COMMIT, other versions/configurations, performance distributions and learner outcomes are not claimed as tested.
No intentionally unsupported in-scope claim remains. The parent audit gate stays open for the remaining slices. Code-review and shipping are separate stages.