/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](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](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](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