find-postgres-bug

name

find-postgres-bug

description

Find latent bugs in a local PostgreSQL source tree (REL_xx_STABLE branch or HEAD) the way a core hacker does: build a heavily-poisoned debug instance (cassert + cache-discard + -O0/-ggdb3 + core dumps), fuzz it (sqlsmith) until the backend crashes, triage the core into a minimal reproducer (MRE), git-bisect the introducing commit, and render a community-style pgsql-bugs markdown report. The instance never auto-shuts-down. Use when the user says "find a bug in postgres", "fuzz postgres", "hunt for a crash", "test a recent commit", "write a repro", "bisect this crash", "把 PG 跑起来找 bug", "fuzz 一下 postgres", "看看这个 commit 会不会出问题", etc. Triggers whenever the user points at a PG source dir and wants to hunt bugs.

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.

---

Quick start

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.

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:

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

Step 1 — Poison the build & start (the 90% lever)

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 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)

# 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 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

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

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.

./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; log reading in 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

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

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:

./scripts/find_suspicious_commits.sh 30 HEAD

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

./scripts/analyze_commit.sh <hash> 600   # message + diff slice

Before spending any time on a candidate, screen it (Step 0 rule 3):

./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 into YAML, then:

./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)

./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; field reference in 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:

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 — poisoned-build flags, cache-discard mechanisms, valgrind/ASan, pitfalls.
• references/fuzzing.md — sqlsmith usage, seeding, targeted fuzzing, SQLancer/amcheck alternatives.
• 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 — commit-log-as-regression-signal + where bugs hide, for the targeted probe.
• 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 — minimal repro patterns by subsystem.
• references/log_indicators.md — severities, greps, multi-line patterns (asserts, backtraces, context).
• references/bug_report_template.md — YAML fields + how to post to pgsql-bugs.
• 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?
  ├─ NO  → ask for it; suggest $HOME/postgres
  └─ YES → git status --short  (Step 0 pre-flight)
            ├─ dirty / staged revert? → STOP, ask the user; never commit it
            └─ clean → build_and_start_pg.sh  (poisoned: cassert+cache-discard+-O0)
            ├─ banner says "Cache poison: none"? → re-enable PG_POISON_CACHE=1
            └─ up → seed schema → fuzz_sqlsmith.sh (long run)
                     ├─ crash (new core / PANIC)?
                     │    ├─ reproduces on UNMODIFIED HEAD? ── no ─→ not a finding
                     │    ├─ triage_core.sh → backtrace
                     │    ├─ minimise → MRE (run_repro.sh: "SERVER IS DOWN")
                     │    ├─ bisect_run.sh good..bad → first-bad commit
                     │    └─ gen_bug_report.sh → post upstream (NO patch)
                     └─ clean → targeted probe:
                          find_suspicious_commits → check_already_fixed.sh (skip if fixed)
                          → seed those objects → re-fuzz / focused repro on HEAD
                          (NEVER revert a fix to reproduce); or run longer / switch branch