PostgreSQL locking and MVCC audit, issue #34
This existing-content audit applies the evidence policy in #31 to #34. The baseline is 2ae8c41940e75bc2a36dd1bdeaf76f432eeedf22. The full editorial and QA reports and raw execution evidence remain temporary. This durable record preserves coverage, dispositions and exact outside-slice corrections for subsequent work.
Coverage and occurrence identity
The assigned surface contains twelve lessons, twelve locking YAML Scenarios and five MVCC Scenarios (two YAML, three TypeScript), all seventeen generated Transcripts/timelines, and their ledger records and llms sections. Wraparound has no executable Scenario and is explicitly a PostgreSQL 18 Documented contract. Chapter navigation and README chapter-summary cells are editorial topic labels, not additional executed guarantees.
34-claims.tsv retains 1,026 baseline and 1,027 current occurrence units, 2,053 total across fifty assigned files/ranges. These comprise 849/850 source and chapter-summary units, seventeen complete Transcript/timeline ranges per revision, seventeen exact llms ranges per revision, and 143 ledger fields per revision. 34-evidence-registry.json has 31 source/summary entries and retains versioned sources, ordered operations, before-operation scope, state after each operation, assertions and mirror identities. Exact identity and text, not counts alone, define coverage. TSV text is a JSON-encoded string, preserving embedded newlines. Every table column is inventoried, including the last column; the four-mode table has twenty-five cells including its headers.
YAML identities include exact nested fields, such as scenarios/postgres/03-locking/alter-table-outage.yaml#steps[11].expect[0].balance. An unchanged SQL statement in another step is not a substitute. Baseline and current step indices correspond because existing ordered steps were preserved; the DDL column observation is appended after the existing schedule. TypeScript retains every nonempty physical source line and its exact ordered operation span. Assertions and notes between operations retain the preceding operation's state, rather than borrowing a later state. Repeated operations remain separate. Generated ranges preserve the complete corresponding Transcript/timeline and llms text; ledger fields are separate occurrences. They mirror source claims and assertions, not independent evidence over all schedules.
scope means connection state immediately before the named operation. state_after means state after the scheduled operation. BEGIN consumes the named transaction level; COMMIT/full ROLLBACK returns the session to standalone Read Committed. Failed standalone statements end their implicit transactions. An error inside an explicit PostgreSQL transaction leaves a failed transaction block until recovery/end. A blocked dispatch remains pending; its success/failure observation is separately identified and attributed to the original session and SQL. These are schedule annotations, not an independent server-state trace. Fresh harness sessions use the pinned server's default Read Committed and disabled lock_timeout; the deadlock schedule overrides detection timeouts explicitly.
Semantic dispositions
| Decision | Subject | Resolved support and limits |
|---|---|---|
| A1 | Row modes and foreign keys | Ordinary SELECT and locking SELECT are distinct operations. All sixteen compatibility combinations are manual Table 13.3 contracts; the Scenario executes selected pairs only. Key-changing UPDATE means the manual's FK-usable unique-index criterion. Immediate FK insert asserts permitted non-key update and waiting/failing DELETE; KEY SHARE lookup has additional versioned implementation support. Savepoint lock-release exceptions are documented, not executed here. |
| A2 | Queue and DDL | The observed B-before-C order does not promise FIFO fairness. pg_blocking_pids reports immediate hard/soft edges, not recursive ancestry. ADD COLUMN asserts B-to-A and C-to-B; new C differs from a transaction already holding its required lock. Timeout DDL queues before failing; C is observed only after removal. Added final column assertion verifies the successful retry. No DDL timing or zero-disruption guarantee remains. |
| A3 | NOWAIT, SKIP LOCKED and timeouts | Row-lock controls do not eliminate table locks or other errors. Only NOWAIT/timeout produce the demonstrated 55P03; SKIP LOCKED returns available rows. lock_timeout is per acquisition, can lose to statement_timeout, and differs between standalone/explicit-transaction recovery. Explicit rollback, not an injected crash, restores row availability. No external execution guarantee. |
| A4 | Deadlocks and recovery | This opposite-order schedule observes B's 40P01 under unequal detection settings and A's committed balances. Victim choice and minimum cycle latency are not universal promises. Ordered acquisition executes one successful schedule; its narrowly stated two-row/no-other-resources exclusion is marked † with derivation. Whole-transaction retry does not guarantee eventual success or undo external work. |
| A5 | Monitoring | Four asserted entries apply to this table/index UPDATE, not every UPDATE. On-disk row locks normally are absent, but tuple-manager entries can appear. pg_locks/live activity are not a fully atomic history; privilege limits matter. active/idle states do not diagnose slow/stuck clients by themselves. |
| A6 | Tuple versions | UPDATE creates a new version while changing old tuple headers. Nonzero xmax alone does not establish deletion; locking/MultiXacts also use it. Strengthened initial/fresh B-value and ctid assertions accompany page-chain and DELETE assertions. Physical retention and consecutive slots are scoped to this setup; ctid is not a stable logical key. |
| A7 | Snapshot representation | Exposed top-level xmin/xmax/xip are not the whole visibility algorithm. Commit status, own writes, command order, subtransactions and flags matter. The existing xmax=B+1 observation is retained and Table 9.85 supports its documented definition. pg_current_xact_id can assign an xid without a table write. Level/operation scope replaces claims that commit order never matters or every read is pure arithmetic. |
| A8 | Bloat and reclamation | Added live-value assertion; inspected chain and 5-to-9-page/zero-live-row observations remain setup-specific. Removed perpetual growth, exact doubling, exact reclaimed-tuple count from size, and unmeasured performance claims. Standard VACUUM can truncate empty trailing pages; HOT/pruning can reuse space. n_dead_tup is estimated. |
| A9 | Old snapshots and maintenance | A reads only the original version, not every intermediate version. Equality covers selected tuple-chain fields, not all page bytes or all VACUUM work. Cross-table horizon effect is marked † with derivation and a single-table execution limit. Ordinary RC idle transactions need not retain an RR snapshot. Timeouts, privileges and other horizon retainers are contracts, not new executions. |
| A10 | Freeze and wraparound | Entire page is versioned manual support, not a claimed two-billion-xid experiment. Freeze flags retain xmin; deletion remains relevant. Eligibility/launch thresholds do not guarantee each vacuum freezes all rows or resets age exactly. xid and epoch-extended xid8 differ. Warning/stop thresholds and monitoring query are documented; healthy-age, outage and recovery distributions are not measured. |
† denotes an Entailed guarantee with an explicit derivation and no transcript proving all executions. The other two evidence categories remain distinct: asserted scheduled observations are Demonstrated behavior; linked PostgreSQL 18 contracts are manual support under their stated conditions.
The short row-lock, queue and old-snapshot headings state their supported scope as well. Their original exclusivity, fairness and all-work VACUUM claims are recorded as narrowed, not accepted as nonbehavioral editorial labels. Legacy fragment IDs remain available without repeating the old claims in visible headings or outlines. The old-snapshot Scenario title is corrected at its TypeScript source for the CLI. That title is not a field in the ledger or llms Transcript mirror; their already-scoped claims and generated results are unchanged.
Official support
Versioned PostgreSQL 18 pages were checked rather than relying on the moving current manual. The site's root llms.txt was unavailable, so the HTML manual supplied support. The registry names the applicable pages/anchors for each lesson and Scenario. Key sections are 13.3, explicit locking and Tables 13.2/13.3, 9.27.8, snapshot functions and Table 9.85, 5.6, system columns, 24.1.2/24.1.5, space recovery and wraparound, 19.10, vacuum settings, and 53.13, pg_locks. The FK lookup and item flags additionally cite PostgreSQL's REL_18_STABLE implementation. Those implementation details are distinguished from a manual contract and from the asserted conflict signature.
Cross-surface handoff
These exact occurrences are outside #34. Locations refer to the baseline commit. Each named Scenario correction must propagate to its CLI explanation, generated part/timeline, ledger claim and corresponding llms section. They are required reconciliations for the remaining audit slices, not retrospective tooling proposals or unresolved assigned claims.
| Owner | Exact baseline occurrences | Required reconciliation |
|---|---|---|
| #40 | docs/faq.md:32,36,40; docs/errors/55P03.md:3,11,27-35; docs/errors/40P01.md:3,11,27-35 | Scope ordinary reads versus locking/table waits, standalone versus explicit-transaction recovery, NOWAIT row scope, victim selection and retry limits. descriptions and visible text must agree. |
| #38 | docs/postgres/07-pitfalls/compendium.md:53-56,68-72,101-116; docs/postgres/08-production/long-and-idle-transactions.md:3-5,13-15,41-44; docs/postgres/08-production/bloat-and-vacuum-health.md:15-41; docs/postgres/08-production/logs-and-counters.md:20-24,51-54 | Queued DDL does not force all later operations to wait. Old horizons constrain reclamation, not every VACUUM task. Distinguish RC/RR snapshots, estimated counters, launch thresholds from completion, and guaranteed prevention from observed ordered locking. |
| #38 | scenarios/postgres/07-pitfalls/queue-bloat.yaml#claim, #steps[14].note, #steps[17].note, #steps[20].note, #steps[23].note; scenarios/postgres/08-production/find-long-transactions.yaml#steps[8].note; scenarios/postgres/08-production/vacuum-health.yaml#claim, #steps[3].note, #steps[5].note, #steps[8].note | Scope queue-bloat results to its executed copy of the pattern and observed page fields; do not claim all later versions are visible to the old snapshot or all maintenance becomes a no-op. Scope estimated tuple counts and freeze-margin claims to observed/configured contracts. Each abbreviated field after a full Scenario path belongs to that same path. |
| #36 | docs/postgres/05-patterns/job-queue.md:3-6,12-23,27-42; scenarios/postgres/05-patterns/job-queue.yaml#claim; docs/postgres/05-patterns/orm-pitfalls.md:10-16,53-55 | SKIP LOCKED avoids the specified conflicting row requests, not all waits or duplicate external execution. Explicit rollback is not process-crash/delivery testing. Audit operation and writer boundaries and maintain source/mirror consistency. |
| #40 | README.md:6,15-22,55-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/index.md:3,8,20 | Execution proves asserted schedules, not every prose claim or all schedules. Wraparound/manual-only contracts, marked derivations and TypeScript-only coverage need accurate shared promises. Change generated index text at its source. |
Verification and remaining limits
The issue handoff names the checked commits and attributable attempts: assigned PostgreSQL executions, full Bun gate, applicable independent Python YAML coverage, two-generation stability, type/format/anchor/build checks and fresh-context built-reader QA. A complete inventory or deterministic generation is not itself runtime, cross-driver or rendered-reader proof. Failed attempts and successful retests remain separately identified in the temporary reports.
There are zero intentionally unresolved in-scope claims. Three client-code Scenarios have Bun execution but no independent Python-driver coverage. Wraparound, alternative configurations/versions, lock fairness, performance, process/database crashes, indeterminate commits, real networks, external delivery and learner outcomes are not claimed as executions. Only existing content and evidence were audited; no practice, guide or Mechanism diagram feature was added. The parent audit gate remains open for the remaining slices. Code review and shipping are separate stages.