Find Postgres Bug
Testing & Qualityfind-postgres-bugFind Postgres Bug is an Agent Skill for testing & quality published in the digoal/blog repository on GitHub: a SKILL.md instruction file an AI agent can load to do this task.
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.
How to use this skill
Bring this guide into your coding agent with a prompt tailored to the tool you use.
- Open your project in Codex.
- Copy and paste the prompt below.
- Review files and risks before approving any action.
Inspect the Agent Skill at https://github.com/digoal/blog/blob/HEAD/skills/find-postgres-bug/SKILL.md and all supporting files. Report provenance, permissions, scripts, dependencies, and security risks. Propose an exact installation into .agents/skills/find-postgres-bug, but wait for my approval before writing files or running scripts.
Copying this prompt does not install or run the skill. Review third-party files before use.
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:
- The skill never auto-stops the instance. Run
scripts/stop_pg.shexplicitly when done. - 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.
- 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 commitcould re-introduce a fixed bug. Surface this to the user and do not build on top of it. Nevergit commit,git checkout,git restore, orgit stashaway 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/Undois 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):
- Environment — OS / kernel / compiler / PG version / branch / commit / build flags (incl. cache-poison mode).
- Summary — 1–3 factual sentences, no judgement.
- Reproduction — the MRE: file path + inline
.sql. - Actual vs expected — verbatim crash/output vs expected, plus a backtrace or server-log tail.
- Why is this a bug — the spec / docs / invariant it violates.
- 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.shprints 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