PostgreSQL pitfalls and production audit, issue #38
This existing-content audit applies #31 to #38. The baseline is c45f96e1c9dde1e7525b49b4861791c0562b1f77. The completion comment records the checked commit, complete inventory counts, execution results, and reader-QA verdict. Raw inventories, baseline snapshots, official-manual HTML, assertion-mutation evidence, and execution logs remain in temporary storage.
Coverage and identity
The assigned surface contains eight lessons, six YAML Scenarios, six generated Transcript families, six ledger records, and six llms sections. Coverage includes all thirteen compendium entries, all six alerting signals, symptom-triage rows, summaries, source claims, setup, ordered steps, notes, comments, timeline labels, query results, and generated metadata derived from the Scenario claims. The baseline inventory has 1377 nonblank line occurrences and the final inventory has 1390: 229 lesson/summary lines, 343 Scenario-source lines, 400 Transcript/timeline lines, six ledger records, and 412 llms lines. It also records 227 nested Scenario fields and 125 ordered Transcript/timeline correspondences. These are inclusive coverage counts, not counts of independent guarantees.
The inventory retains exact file/line locations, nested YAML field paths, step and Session identities, assertions, and dispositions. Generated events correspond in source order to their own statements and pending resolutions, including repeated balance reads and repeated VACUUM/page queries. Timeline occurrences retain those identities. Each llms section contains its corresponding complete Transcript body; each ledger record retains its own Scenario claim and engine version. Unasserted returned columns are observations, not separately asserted guarantees. Rewritten paragraphs supersede the baseline; there is no invented one-to-one paragraph equivalence.
The execution scope is PostgreSQL 18.6, default READ COMMITTED unless the Scenario explicitly selects READ ONLY REPEATABLE READ, superuser access, 8192-byte heap pages, track_counts and track_activities enabled, and prepared transactions enabled with a limit of ten. The queue uses pageinspect. These settings are observed test configuration, not a claim that every production role can run the same queries. Sidebar labels and README chapter-coverage cells name topics rather than additional guarantees. Shared provenance promises remain owned by #40.
Semantic dispositions
| Decision | Assigned claims | Evidence and final disposition |
|---|---|---|
| A1 | Compendium 1, lost-update repairs | The linked READ COMMITTED schedules assert lost and repaired single-row increments. Retain the stated participating-writer protocols; remove blanket cross-row and external-effect protection. |
| A2 | Compendium 2, check then insert | The linked plain-check schedule inserts duplicates; uniqueness and conflict handling support the specified identity. Other errors are outside that conflict branch. |
| A3 | Compendium 3, idempotency | The linked Scenario asserts database-local balance changes under retained unique keys, including an in-flight duplicate. It executes no payment provider or lost response. |
| A4 | Compendium 4, cross-row invariant | Retain the demonstrated REPEATABLE READ write skew and linked Serializable protection. All relevant writers must coordinate; bounded retries can fail. Per-row locks on different rows do not enforce the rule. |
| A5 | Compendium 5, queued DDL | Scope the wait to the shown incompatible later SELECT. A DDL lock timeout bounds that wait, not every migration or outage. |
| A6 | Compendium 6, idle sessions/pool | Idle transactions can occupy connections or retain resources. The linked idle-timeout Scenario runs no ORM or pool and demonstrates no pool exhaustion. Settings and application fixes are advice. |
| A7 | Compendium 7, retained history | Old horizons constrain relevant version removal, not every vacuum task. Reusable space can also explain a large file; transaction age alone is insufficient. |
| A8 | Compendium 8, serialization retry | The linked client Scenario executes one RR retry. Complete-transaction retry is a manual contract; exhaustion, deadlock handling, and external-effect deduplication are not executed there. |
| A9 | Compendium 9, queue selection | The linked database queue selects different rows and reselects after explicit rollback. It tests no task execution, process crash, plain-SELECT failure, reaper, fairness, or duplicate-free external effect. |
| A10 | Compendium 10, outbox | Distinguish separately committed local Receiver-model tables and database outbox state from broker delivery. Duplicate publication is the linked marked inference; continued delivery needs retention, retries, and receiver availability. |
| A11 | Compendium 11, prepared work | Backend termination leaves the prepared entry, row lock, and relevant cleanup constraint. Recovery needs permission and the coordinated decision; the prepared-work view lists more than orphans. No coordinator or server crash runs. |
| A12 | Compendium 12, deadlock repair | Retain the two-row cycle and bounded retry advice. Consistent ordering over that lock set is not proof against every deadlock. Counter rates need reset handling. |
| A13 | Compendium 13, queue composition | Use Q1's measured schedule; remove assertions of observed autovacuum, disk-full incidents, rates, or verified claimed-state recovery. The symptom heading remains a navigation label, with the evidence limits stated in its entry. |
| Q1 | Queue/bloat lesson and Scenario | Assert selected jobs 1/2, A's assigned xid, page sizes 9/11/17, 1200 rows with 1001 done and 199 queued, occupied slots 2201 before/after first VACUUM then 1200 after commit and another VACUUM, and final 17 pages. The removal-horizon explanation is marked † with a manual derivation. No null backend_xmin is asserted: protocol-level snapshot lifetime does not establish a transaction-wide RR snapshot. Ordinary VACUUM can truncate empty trailing pages; FULL is not the only possible shrinking operation. Occupied slots are not free-byte or index-bloat measurements. |
| P1 | Blocker diagnosis and termination | Assert B waiting on idle A, both queries, successful signal delivery, B's resumption, M's balance 100 before B commits, and M's balance 300 afterward. The new intermediate observation checks A's rollback independently of B overwriting the value. Signal delivery is not completed termination. Manual-only limits include permissions, PID-zero prepared blockers, soft blockers, parallel duplicates, and activity visibility. PostgreSQL 18 permits roles with pg_signal_autovacuum_worker privileges to cancel or terminate autovacuum workers, otherwise considered superuser backends; the superuser-only rule applies to ordinary superuser sessions. This exception is documented, not exercised by the Scenario. Remove arbitrary-termination safety promises; database rollback does not reverse external effects. |
| P2 | Long/idle transactions and timeouts | Assert the two-order READ ONLY RR report, idle age over one second, no assigned xid, and retained xmin. This detector does not measure reclamation. Add explicit-transaction cancellation: 57014, subsequent 25P02, ROLLBACK, original balance 100. Retain standalone session survival and idle transaction-timeout termination with rollback. Busy transactions, timeout interactions, defaults, and prepared exemptions are manual scope; no pool or ORM runs. |
| P3 | Deadlock counter, logs, resets | Assert 40P01 and delta one after flush. Remove permanent-history and narrated-retry claims. Resets, unclean versus clean shutdown, cache/flush behavior, and logging controls are documented. Remove the unretained historical log excerpt; no server-log content, restart, or collection overhead is claimed as executed evidence. Logging diagnoses waits rather than preventing them. |
| P4 | Vacuum/statistics/freeze health | Assert inserts/updates and estimated 5/3 then 5/0 counts, manual-vacuum timestamp presence, and age below the configured launch threshold. Separate estimates, occupied slots, free space, and performance. Timestamp age is not a diagnosis; fresh timestamps do not prove complete removal. Freeze launch is per table, database age aggregates its oldest boundary, and threshold comparison is not a safety guarantee. Half-threshold warnings and pgstattuple use are unexecuted advice. |
| P5 | Alerting checklist | All six rows are investigation signals and tunable example policies. Field demonstrations do not execute aggregate alert rules or establish incident coverage. Use numeric division for dead/live ratios, handle null timestamps and counter resets, and distinguish timeout controls from diagnostic logging. Remove guaranteed ordering, early warning, and prevention promises. |
| P6 | Symptom triage | All rows are candidate causes, not measured incident frequencies. Separate single-row and cross-row repairs, database activity and application pools, prepared work and orphans, and bounded retries from guaranteed success. Naming Sessions aids diagnostics but does not confer monitoring permission. |
† identifies an Entailed horizon explanation or a linked derivation with its explicit execution limit. Demonstrated behavior requires an executed assertion. Documented contracts use PostgreSQL 18 support. Recommendations, hypothetical symptoms, and model boundaries do not become guarantees through generation. The inherited #34 and #36 occurrences assigned to #38 are reconciled in these decisions and their source/mirror families.
Official support
The PostgreSQL root llms.txt and llms-full.txt endpoints returned 404; the versioned HTML manual supplies support. The registry retains claim-specific references to space recovery, freeze thresholds and old horizons, transaction identifiers, activity fields and access, table estimates, database counters, statistics persistence, statistics cache and publication, timeouts, signaling, blocking PIDs, session termination, logging, pgstattuple limits, complete-transaction retry, and prepared recovery.
Outside-slice reconciliation
These exact locations refer to the baseline commit. They are handoffs to their assigned owners, not unresolved #38 claims. Older audit reports remain historical records.
| Owner | Baseline locations | Required reconciliation |
|---|---|---|
| #40 | docs/faq.md:28,32,48,56; docs/errors/40001.md:3,11,29,37; docs/errors/1213.md:33 | Scope repairs and retry to complete repeatable transactions; retry can fail and external effects can repeat. Statement failure has savepoint/session exceptions; SQLSTATE 40001 has more than the shown concurrent-update trigger. Do not infer a general cross-engine lock behavior from one schedule. |
| #40 | README.md:6,15-22,55-56; CONTRIBUTING.md:3-10,59-79; docs/about/methodology.md:11,20,62,70,75,84-90; scripts/gen-transcripts.ts:152-153; docs/public/llms.txt:4-5; docs/.vitepress/theme/components/HomeCurriculum.vue:17 | Reconcile asserted schedules, manual contracts, marked derivations, and model limits. The contributor queue/bloat narrative attributes the composition to a snapshot and claims a rate; align it with Q1's assigned-xid observation and fixed schedule. Correct generated shared promises at their generator source. |
| #40 | docs/.vitepress/config.ts:237,291-293 | The cross-driver footer applies its stamp to every Transcript, while Python checks shared YAML only. Keep YAML parity separate from TypeScript/client-code evidence. |
| #39, reconciled by #40 | docs/mysql/07-pitfalls/compendium.md:29-32,43-45,63-66,73-77,104-109,121-132; docs/mysql/08-production/alerting-checklist.md:23-33; docs/mysql/08-production/logs-and-counters.md:6,19-22,36-49; docs/mysql/08-production/history-list-health.md:3-4,28-35 | Carry the inherited #37 operation/model limits forward. Check counter reset/retention, slow-log diagnosis, silent-anomaly metrics, estimated history and oldest-reader scope against MySQL contracts; thresholds and operational recommendations are not demonstrated guarantees. Do not copy PostgreSQL vacuum behavior into InnoDB. |
Each assigned generated part, llms section, ledger record, and derived lesson description is reconciled here. No new exercise, protection-choice guide, Mechanism diagram, delivery integration, failure lab, or alternate execution harness is introduced.
Verification boundaries
The completion comment separates real-database execution, six assigned Python YAML checks and full-suite parity, assertion mutations, generated stability, static/link/build checks, and fresh-context built-reader QA. The inventory correspondence checks support the semantic audit; they are not a substitute for it. Model-only and manual-only branches remain labelled in the lessons.
No unsupported in-scope claim remains intentionally unresolved. The parent full-audit gate remains open for the other slices and shared reconciliation. Code review and shipping are separate stages.