Claude Code Instructions for cat_tools

GitHub CI

After every push, monitor GitHub CI in a background subagent until all jobs pass or a failure is confirmed. Use gh pr checks <pr> --watch when the branch has an open PR; otherwise (a branch with no PR yet, or a push to master) use gh run watch for the pushed commit. Investigate and fix failures immediately rather than leaving them for the user to notice.

(.github/workflows/ci.yml’s changes job computes the actual per-push changed file set and exposes docs_only; the heavy test, pg-upgrade-test, and legacy-extension-update-test jobs skip when needs.changes.outputs.docs_only == 'true' — i.e. every changed file in that push matches **.md/**.asc. Unlike the old workflow-level paths-ignore, this is evaluated per push/commit, not over the whole PR diff, so a doc-only commit on a PR that also touches code still gets the skip, and the workflow (including the required all-checks-passed check) always triggers and reports rather than being skipped outright by GitHub. When unsure, check gh run list for the pushed commit and monitor whatever run appears; if none does, there is nothing to watch.)

Where a test-matrix dimension belongs

The full set of things we test is itself a matrix (PostgreSQL major, UPDATE vs. CREATE EXTENSION, etc.). It’s a real tradeoff, not a rule with one exception — but bias toward putting a new dimension in make over ci.yml: it can then be run locally, and it never spins up an extra container/job. Needing real isolation to mean anything (e.g. pg_tle’s dedicated cluster) is one good reason to put a dimension in ci.yml instead, not the only possible one — weigh the actual tradeoff, just start from a bias toward make.

Build/test system (pgxntool)

This repo’s build is driven by pgxntool (embedded under pgxntool/). Its docs are not auto-loaded, so for any non-trivial build or test work, read pgxntool/README.asc and pgxntool/CLAUDE.md first. High-value gotchas that those docs explain:

  • DATA, MODULES, DOCS, and installcheck are PGXS variables/targets, not pgxntool’s; pgxntool only wraps/seeds them. Don’t assume they belong to pgxntool.
  • make test returns non-zero on test regressions (pgxntool 2.3.0+): installcheck itself is still marked .IGNORE, but test’s own recipe now checks test/regression.diffs and exits non-zero if it’s non-empty. make verify-results remains the stricter, documented CI gate: it depends on $(TEST_DEPS) directly, not on test itself (deliberately — test’s own early exit would otherwise abort the chain before verify-results got to inspect and report the diff), and applies a stricter pgtap-aware check on top. A bare make test failing locally is now a real signal too, just not the documented gate.
  • test/install/*.sql runs once, committed, before the suite in the same pg_regress invocation, so its state persists into every test. test/build/*.sql are separate build sanity checks run before the suite.
  • Versioned .sql files are generated from their .sql.in sources and gitignored. Anything referencing them at Make parse time is subject to two-phase-make timing.

Bug Fixes

Comment the fix where it isn’t self-evident, but keep it concise — no novels. Do NOT recount the bug’s history (what a past version got wrong) UNLESS the same mistake could realistically be made again; if it could, briefly state the guard fact that prevents it. Never repeat the same comment verbatim in adjacent code — write it once and reference it (“same as above”).

Git

Never delete a branch without explicit user approval. This includes git push origin --delete, git branch -d, and git branch -D. Always ask first.

Always open PRs against the main repo (Postgres-Extensions/cat_tools), not a fork.

Prefix with CI: any commit/PR subject whose change affects ONLY CI (workflow files, CI-only scripts) and does not touch the main test suite/build. A change that also touches the test suite/build does not get the prefix, even if CI-related.

References to PRs and issues in committed files

A reference to a GitHub PR or issue inside a committed file (SQL/code comments, .github/workflows/ci.yml comments, CLAUDE.md, test/install/load.sql, docs) MUST be a full URL, e.g. https://github.com/Postgres-Extensions/cat_tools/issues/28 — never a bare #28 (a bare number is meaningless when the file is read outside GitHub), if it’s something a reader might still need to act on or look up — an open TODO, a workaround to revisit once some other issue lands, a known limitation. For a reference that’s purely historical context (explaining why past code looks the way it does, where the issue is already resolved and there’s nothing left to do), a bare #28 is fine — readers are less likely to need to chase it down, and it’s less noise. When in doubt, use the full URL. Referencing by number is always fine in GitHub-native text (PR/issue titles and descriptions, review comments).

Pull request descriptions

The maintainer builds the squash commit message from the PR description, so the opening of the description is used directly as the basis for that commit message. Write it accordingly:

  • Make the opening stand alone as a good commit message: no leading header/title line (the PR title is the subject), concise, self-contained. Multiple paragraphs are fine.
  • Lead with the substantive change and why. Keep incidental changes (minor doc tweaks, dependency/action version bumps, small cleanups) OUT of the opening — put them lower or omit them; the diff carries those details for anyone who wants them.
  • Do NOT add a marker delimiting the “commit message” from “the rest”. Just let the opening carry its own weight, with any extra context following after it.
  • Do NOT hard-wrap paragraphs: write each paragraph as a single long line (blank line between paragraphs). Hard-wrapping at ~80 columns conflicts with how GitHub builds the squash commit message from the description.
  • Length past the opening is fine — backstory and detail are often worth keeping. Being hard to scan is the actual problem: if there’s enough detail to justify it, give the rest real structure (headers, bullet lists, separate sections for “what changed” vs. “how it was verified”), not one undifferentiated block of prose.
  • Watch for two habits specifically: being more verbose than the point actually needs — structure alone doesn’t fix over-length — and “leaking” context from how the work evolved. An earlier idea that got revised or dropped before anything merged describes the process, not the result; it doesn’t belong in the description of what actually shipped.

SQL file conventions

Rules for what to track in git:

  1. If a .sql.in file exists, track the .sql.in and not the corresponding .sql.
  2. If no .sql.in exists, track the .sql directly (e.g. historical pre-0.2.0 files).
  3. Version-specific install scripts (e.g. sql/cat_tools--0.2.2.sql.in) are tracked BY DEFAULT. They enable update testing (install an old version, ALTER EXTENSION UPDATE, verify) and, because a new MAJOR PostgreSQL version can unpredictably break installing an OLDER extension version, keeping old versions committed lets CI catch when a version stops installing on a newer PG. See https://github.com/Postgres-Extensions/pgxntool/issues/51.
  4. Update scripts (e.g. sql/cat_tools--0.2.1--0.2.2.sql.in) MUST be tracked — they are essential to the update path and have no substitute.
  5. EXCEPTION to rule 2: a version that changes little AND ships no nontrivial update-path machinery MAY omit its generated install script (little test-coverage value; it is regenerated from sql/cat_tools.sql.in at build time). Track it whenever the version carries meaningful changes or a nontrivial update script — a complex update warrants the committed install script for update-test coverage and provenance.
  6. Version-specific files MUST NEVER be edited manually — always edit sql/cat_tools.sql.in and regenerate.
  7. EXCEPTION to rule 2 (and to pgxntool’s own “don’t .gitignore a skipped version, rm it once” guidance — pgxntool/README.asc, “Don’t .gitignore a Skipped Version”): sql/cat_tools--stable.sql.in (the pseudo-version default_version sits at between releases, see RELEASE.md) IS gitignored, not tracked. That guidance is about a version that’s normally tracked but occasionally skipped — a one-time, transient leftover file. stable is the opposite case: it’s permanently current between releases, so unlike a skipped version it would be regenerated and re-diffed on every single source edit if tracked, for zero test-coverage value (CI already builds+tests a fresh install from source on every push regardless). See sql/.gitignore’s comment and RELEASE.md step 4.

CI: PostgreSQL version support

Policy: Never support a fresh install on any PostgreSQL version where the extension update path is known to be broken — a version that cannot be updated to is not truly supported.

Both PG10 and PG11 are dropped as of 0.3.0. The ALTER TYPE ... ADD VALUE statements in the update script cannot run inside an extension update script on PG11 or earlier (PROCESS_UTILITY_QUERY context); this restriction was lifted in PG12. Because a version that cannot be updated to is not truly supported, PG10 and PG11 support is dropped entirely. cat_tools 0.3.0 supports PG12+.

Test-load modes (TEST_LOAD_SOURCE)

test/install/load.sql installs the suite’s dependencies once, committed, before the pgTAP suite. The Makefile exports the mode (and update range) as placeholder GUCs via PGOPTIONS; TEST_LOAD_SOURCE must be fresh, update, or existing (anything else is a hard parse-time error):

  • fresh (default): CREATE EXTENSION cat_tools at the current version.
  • update: CREATE EXTENSION at TEST_UPDATE_FROM (default 0.2.2) then ALTER EXTENSION cat_tools UPDATE — to TEST_UPDATE_TO if set, else to the current version. Running the SAME suite/expected output asserts an updated database behaves identically to a fresh install. make test-update is the shorthand.
  • existing: the extension is ALREADY installed (by binary pg_upgrade, or an ALTER EXTENSION UPDATE performed outside the suite). load.sql does not touch it; it only asserts presence + current version and creates the test roles. Pair with CONTRIB_TESTDB=<db> EXTRA_REGRESS_OPTS=--use-existing so pg_regress runs against that database instead of dropping/recreating a throwaway one.

CI jobs

  • test also runs the widest update path we support — bin/test_existing update-scenario installs 0.2.2, ALTER EXTENSION UPDATEs to current, structurally diffs against a fresh install, then runs the full suite in existing mode — on every major it already covers (pg: [12..18]). PG12 is the floor for this path — PG11 and earlier cannot run ALTER TYPE ... ADD VALUE in extension update scripts (lifted in PG12).
  • legacy-extension-update-test is PG10-only: it exercises the pre-0.2.2 update scripts (0.2.0→0.2.2 and 0.2.1→0.2.2, which install only on PG10 and target 0.2.2, not the current version) plus the 0.2.2→0.2.3 view-rebuild. 0.2.2 is the update-from floor only for backward-compat: the 0.2.0/0.2.1 install scripts fail on PG11+/PG12+.
  • pg-upgrade-test binary-pg_upgrades a real database and then runs the suite against it in existing mode. Two shapes:
    • old_pg>=11: install 0.2.2 directly → plant guard → pg_upgrade → update to current. A fresh 0.2.2 install builds the views with the pg_upgrade-safe omit_column (!= ALL), so it survives pg_upgrade as-is.
    • old_pg=10: the FULL real-world journey — install 0.2.0, bridge-update to 0.2.3 on the old cluster → plant guard → pg_upgrade → update to current. The bridge must reach 0.2.3, not 0.2.2: the shipped 0.2.0→0.2.2 / 0.2.1→0.2.2 scripts do NOT fix the views (their omit_column used the no-op != ANY, leaving relhasoids/relhaspkey in _cat_tools.pg_class_v); the pg_class_v DROP+CREATE rebuild that strips those columns lives in the 0.2.2→0.2.3 update. 0.2.3 is also the furthest a PG10 cluster can reach (0.2.3→0.3.0 uses ALTER TYPE ... ADD VALUE, unrunnable in an update script before PG12); the post-upgrade step then updates 0.2.3→current on the new cluster. This proves a 0.2.3 reached via the bridge update (not a fresh install) survives pg_upgrade. Both flows plant the dependency guard and run through bin/test_existing.

When working on a new version: review and expand these matrices. A new version’s install script may support more PG versions, enabling testing of the update path from older cat_tools versions on newer PostgreSQL.

Code Style

Comments

Always use block comment format for multi-line comments in SQL files:

/*
 * First line of comment.
 * Second line of comment.
 */

Never use -- line comments for multi-line explanations.

Be concise — humans have limited context too, not just AI. Say the point in as few words as it needs, not as many as the topic could support.

Usually, explain something at the first place it’s mentioned, not somewhere later that assumes context the reader hasn’t reached yet — but this isn’t absolute. One real exception: you’re already in the middle of explaining A, and need to mention B (which also needs its own explanation) — stopping to fully explain B right there often confuses the explanation of A more than deferring B to its own natural spot would. Use judgment. When a comment points to another spot in the same file, say above or below so the reader doesn’t have to search both directions.

Closing non-indentable blocks

When closing a code block that cannot be indented to show its nesting (e.g. SQL \endif, DO $$...$$, shell heredocs, column-0 fi/esac) AND that block contains nested blocks, label the closer with a comment naming which block it closes — e.g. shell esac # basename dispatch, or a named dollar-quote DO $DO$ ... $DO$ for a DO block. Where the language rejects a trailing comment on the closer (psql \endif warns “extra argument ignored”), put the label on the immediately following line instead (e.g. \endif then -- end \if :cat_tools_mode_existing).

Terminology

“upgrade” refers to a PostgreSQL cluster (pg_upgrade); “update” refers to an extension (ALTER EXTENSION ... UPDATE). cat_tools' version-to-version scripts are “update scripts” — never “upgrade scripts.”