find-postgres-bug

# Find Postgres Bug ## Overview Crash-first bug-hunting pipeline for a local PostgreSQL source tree. The order matters and is the whole point: ``` poison the build → fuzz it → triage the core → minimise to an MRE → bisect the commit → report (do NOT self-patch) ``` The leverage is front-loaded. A poisoned build does ~90% of the work by turning latent bugs into immediate crashes; the fuzzer then finds inputs no human would hand-write. Reading commits by eye is a *secondary*, targeted probe — used to aim the fuzzer, not as the primary search. **Source layout assumed:** the standard PG tree (`src/backend/`, `src/include/`, `contrib/`, `src/test/`, …) at `$PG_SRC_DIR`. **Three hard rules:** 1. The skill **never** auto-stops the instance. Run `scripts/stop_pg.sh` explicitly when done. 2. **Never write a patch.** The deliverable is an MRE + backtrace + bisect result, posted upstream. A drive-by fix from someone who just met the code is worth less than a clean reproducer. 3. **A finding only counts if it crashes at HEAD, with the tree exactly as committed.** Never revert a fix (or commit a staged revert) to make something crash and call it a bug — that just re-creates an already-fixed bug and is worthless. Before investing in any candidate, run the pre-flight in Step 0. See [references/already_fixed.md](references/already_fixed.md). --- ## Quick start ```bash export PG_SRC_DIR=/path/to/postgres # 1. POISON: configure+build+initdb+start with cassert, cache-discard, # -O0 -ggdb3, core dumps enabled. The 90% lever. ./scripts/build_and_start_pg.sh # 2. SEED + FUZZ: load some tables, then let sqlsmith run (overnight is best) psql -h /tmp -p 55432 -d postgres -f your_schema.sql # populate the catalog ./scripts/fuzz_sqlsmith.sh 3600 postgres # 1h; use 28800 overnight # 3. TRIAGE: a crash leaves a core under $PGDATA ./scripts/triage_core.sh /path/to/$PGDATA/core.NNNN # 4. MINIMISE: cut the failing query down, confirm it crashes on demand $EDITOR repro/mybug.sql ./scripts/run_repro.sh repro/mybug.sql mybug # "SERVER IS DOWN" = good # 5. BISECT: find the commit that introduced it ./scripts/bisect_run.sh repro/mybug.sql REL_17_STABLE HEAD # 6. REPORT: render markdown for pgsql-bugs (no patch) $EDITOR /tmp/bug.yaml ./scripts/gen_bug_report.sh /tmp/bug.yaml # when finished: ./scripts/stop_pg.sh ``` If a real crash surfaces, the markdown is ready for [pgsql-bugs]. If the fuzzer runs clean, broaden the seed schema / run longer / switch branch. --- ## Workflow Each step has a **success criterion** to verify before moving on. ### Step 0 — Pre-flight: clean tree & live-at-HEAD gate (do this first) Before building or investing in any candidate, rule out the two biggest time-wasters: a dirty working tree, and a "bug" that HEAD already fixed. ```bash cd "$PG_SRC_DIR" git status --short # any M/MM/A? the tree is NOT as-committed git stash list # stashed reverts hide here too ``` - **Dirty tree → stop and ask.** Uncommitted edits (especially a *staged revert* of a fix) mean you are no longer testing HEAD. A `git commit` could re-introduce a fixed bug. Surface this to the user and do not build on top of it. Never `git commit`, `git checkout`, `git restore`, or `git stash` away the user's changes without explicit permission. - **Always build and test the tree exactly as committed.** Do not revert a fix to "reproduce" a bug. If you ever need to confirm a fix works, that is a *fix-verification* task, not a bug find — label it as such and do not report it as a new bug. When the target is a specific commit (the user said "test this commit" or you picked one in Step 6), check it is not already superseded: ```bash ./scripts/check_already_fixed.sh <hash> # is it in HEAD? any later "Fix"/revert touching the same files? ``` **Success:** `git status` is clean (or the user has explicitly accepted a dirty tree), and the candidate commit is confirmed *not* already fixed or reverted upstream. Only then proceed to Step 1. Details and edge cases: [references/already_fixed.md](references/already_fixed.md). ### Step 1 — Poison the build & start (the 90% lever) ```bash export PG_SRC_DIR=/path/to/postgres ./scripts/build_and_start_pg.sh ``` Runs `./configure --enable-debug --enable-cassert --enable-debug-symbols --enable-tap-tests` with `CFLAGS="-O0 -ggdb3"`, auto-detects the cache-poison mechanism by **grepping the tree** (PG14+: `debug_discard_caches=1` GUC; older: `-DCLOBBER_CACHE_ALWAYS`), enables core dumps, and starts on port `${PG_PORT:-55432}`. Idempotent. Read [references/pg_build_options.md](references/pg_build_options.md) for what each flag buys and how to add valgrind / ASan / `RELCACHE_FORCE_RELEASE` when chasing memory bugs. **Success:** `pg_isready -p $PG_PORT` returns 0; the start banner shows `Cache poison: guc` (or `macro`), **not** `none`. If it says `none`, poisoning was disabled — you'll find far fewer bugs. ### Step 2 — Seed & fuzz (primary discovery) ```bash # populate the catalog first — sqlsmith is far better against real tables psql -h /tmp -p $PG_PORT -d postgres -f some_schema.sql ./scripts/fuzz_sqlsmith.sh 3600 postgres ``` The fuzzer generates valid random SQL until the backend crashes. Read [references/fuzzing.md](references/fuzzing.md) for seeding strategy, targeted fuzzing (aim it at a specific commit's new objects/GUC), and alternatives (SQLancer for logic bugs, amcheck for corruption). **Success:** either a new `core*` appears under `$PGDATA` (→ Step 3), or the run is clean — in which case broaden the seed schema, run longer, or switch to a riskier branch/area (see Step 6). ### Step 3 — Triage the core ```bash ./scripts/triage_core.sh <core-file> ``` Produces a full backtrace (lldb on macOS, gdb on Linux). Read the top non-library frames for the failing function + line. If there's no core, the crash was likely an `Assert` trip — find `TRAP:` and the last `STATEMENT` in the server log. See [references/triage_and_bisect.md](references/triage_and_bisect.md). **Success:** you can name the failing function and the query that triggered it. ### Step 4 — Minimise to an MRE Reduce the fuzzer's giant query to the fewest self-contained lines that crash a fresh backend every time. Save as `repro/<name>.sql`. ```bash ./scripts/run_repro.sh repro/<name>.sql <name> ``` `run_repro.sh` now detects a crash (server gone / PANIC / TRAP), not just SQL ERRORs — it prints `SERVER IS DOWN` and exits non-zero when the MRE works. Patterns by subsystem are in [references/repro_patterns.md](references/repro_patterns.md); log reading in [references/log_indicators.md](references/log_indicators.md). **Success:** a 3–10 line `.sql` crashes the backend deterministically from a clean database. **Until this holds, do not theorise about root cause — it's a guess.** ### Step 5 — Bisect the introducing commit ```bash ./scripts/bisect_run.sh repro/<name>.sql <good-ref> <bad-ref> ``` Drives `git bisect run`: each step rebuilds, re-initdbs, runs the MRE, and reports crash-vs-clean. Confirm `<good-ref>` is genuinely clean first, or bisect will blame an innocent commit. Details + decision tree in [references/triage_and_bisect.md](references/triage_and_bisect.md). **Success:** `git bisect` names a first-bad commit; the MRE crashes at that commit and not at its parent. ### Step 6 — Targeted commit probe (when fuzzing comes up dry) If overnight fuzzing finds nothing, aim it. List recent risky commits: ```bash ./scripts/find_suspicious_commits.sh 30 HEAD ``` Read [references/commit_risk_indicators.md](references/commit_risk_indicators.md): bias toward parser/planner/executor/storage/replication paths and toward repeatedly-fixed areas (`Fix`/`Undo thinko`/`back-patch`). Then **pre-create the objects that commit cares about** and re-fuzz, or hand-write a focused repro for its new code path. ```bash ./scripts/analyze_commit.sh <hash> 600 # message + diff slice ``` **Before spending any time on a candidate, screen it (Step 0 rule 3):** ```bash ./scripts/check_already_fixed.sh <hash> ``` - A commit whose message starts with `Fix`/`Revert`/`Undo` is *itself the fix* — the bug it describes is already gone at HEAD. Don't try to reproduce that bug; the repro will pass and you'll have proven nothing. - The useful probe is the **opposite**: treat a recent fix as a hint that the surrounding code is fragile, and hunt for a *new, still-live* crash near it — one that still reproduces on the unmodified HEAD build. - **Never** revert the fix (or any commit) to make it crash. A crash that only appears after you undo a committed fix is not a finding. **Success:** you reproduced a crash on the **unmodified HEAD** build, or confirmed the candidate path is clean for the inputs you exercised. A crash that needs a reverted fix does **not** count. ### Step 7 — Render the bug report (no patch) Fill [references/bug_report_template.md](references/bug_report_template.md) into YAML, then: ```bash ./scripts/gen_bug_report.sh /tmp/<name>.yaml ``` Writes `./markdown/find_postgres_bug-<slug>-<date>.md` with environment, MRE, actual/expected, backtrace/log tail, why-it's-a-bug, and bisect result. Leave "suggested fix" as an explicitly-unverified direction at most. **Success:** the markdown has all required blocks (Environment / Summary / Repro / Actual / Why / Fix) and a log/backtrace snippet containing the `TRAP:`/`PANIC`/`ERROR:` line. **Only render a bug report for a crash that reproduces on the unmodified HEAD build.** If the investigation ended in "already fixed", "already reverted", or "only crashes after I undid a committed fix", do **not** produce a pgsql-bugs report — write a short findings note instead (`markdown/find_postgres_bug-no-new-bug-<slug>-<date>.md`) stating what was ruled out and why. Reporting an already-fixed bug upstream wastes maintainers' time. ### Step 8 — Tear down (only when done) ```bash ./scripts/stop_pg.sh ``` The **only** script that stops the instance. Nothing else will. --- ## Output format One markdown file per bug: `markdown/find_postgres_bug-<slug>-<YYYY-MM-DD>.md`. Template in [assets/bug_report.md.template](assets/bug_report.md.template); field reference in [references/bug_report_template.md](references/bug_report_template.md). Required sections (pgsql-bugs expectations): 1. **Environment** — OS / kernel / compiler / PG version / branch / commit / build flags (incl. cache-poison mode). 2. **Summary** — 1–3 factual sentences, no judgement. 3. **Reproduction** — the MRE: file path + inline `.sql`. 4. **Actual vs expected** — verbatim crash/output vs expected, plus a backtrace or server-log tail. 5. **Why is this a bug** — the spec / docs / invariant it violates. 6. **Suggested fix** — optional, flagged unverified. "I have a reliable reproducer but no fix" is a perfectly good report. --- ## Script reference | Script | Purpose | | --- | --- | | `scripts/pg_env.sh` | Sourceable env (paths, port, `PG_CFLAGS`, `PG_POISON_CACHE`) | | `scripts/build_and_start_pg.sh` | Poisoned configure+make+install+initdb+start; **idempotent, never auto-stops** | | `scripts/fuzz_sqlsmith.sh` | Drive sqlsmith at the instance; detect new cores + crash markers | | `scripts/triage_core.sh` | Backtrace a core (lldb/gdb) | | `scripts/run_repro.sh` | Run a `.sql`; detect crash (server-down/PANIC/TRAP), capture log slice | | `scripts/bisect_run.sh` | `git bisect run` an MRE across good..bad, rebuilding each step | | `scripts/find_suspicious_commits.sh` | List recent risky commits (targeted probe) | | `scripts/check_already_fixed.sh` | Screen a commit: is it in HEAD / itself a Fix / later reverted-or-refixed? | | `scripts/analyze_commit.sh` | Show stat + diff slice for one commit | | `scripts/capture_log.sh` | Grep `$PG_LOG` (tail / errors / full) | | `scripts/gen_bug_report.sh` | Render markdown from a YAML inputs file | | `scripts/stop_pg.sh` | Explicit teardown | Override any default by exporting before running: ```bash PG_PORT=55433 PG_POISON_CACHE=1 ./scripts/build_and_start_pg.sh ``` --- ## Reference index Load only what you need; each file is self-contained. - [references/pg_build_options.md](references/pg_build_options.md) — poisoned-build flags, cache-discard mechanisms, valgrind/ASan, pitfalls. - [references/fuzzing.md](references/fuzzing.md) — sqlsmith usage, seeding, targeted fuzzing, SQLancer/amcheck alternatives. - [references/triage_and_bisect.md](references/triage_and_bisect.md) — backtrace reading, MRE minimisation discipline, `git bisect run`, the "report don't patch" rule, triage decision tree. - [references/commit_risk_indicators.md](references/commit_risk_indicators.md) — commit-log-as-regression-signal + where bugs hide, for the targeted probe. - [references/already_fixed.md](references/already_fixed.md) — the live-at-HEAD rule: clean-tree gate, screening a commit, why revert-to-reproduce is worthless, fix-verification vs bug-find. - [references/repro_patterns.md](references/repro_patterns.md) — minimal repro patterns by subsystem. - [references/log_indicators.md](references/log_indicators.md) — severities, greps, multi-line patterns (asserts, backtraces, context). - [references/bug_report_template.md](references/bug_report_template.md) — YAML fields + how to post to pgsql-bugs. - [references/pg_version_helpers.md](references/pg_version_helpers.md) — version / commit / compiler / OS one-liners for the report. --- ## What this skill does *not* do - It does **not** write or submit a patch. MRE + backtrace + bisect, posted as a markdown draft for the human to send. This is deliberate. - It does **not** build sqlsmith for you — if it's missing, `fuzz_sqlsmith.sh` prints install hints and exits. - It does **not** shut the instance down. Explicit teardown only. - It does **not** guess the root cause before there's a deterministic MRE. Triage produces a backtrace; minimisation produces the proof. --- ## Decision tree (TL;DR) ``` PG source dir?