psql Shortcuts¶

Everything in Alert Status is one SELECT * FROM pghf.alert_status away — but typing (or remembering) a hand-rolled query with the right severity filter and the right friendly_time() formatting every time you open a terminal gets old fast. tools/pghf_sql_shortcuts.sql, shipped in this repo, is a set of ready-made psql variables — :hc, :hc2d, :hc_c, :hc2d_w, :hce, :hce_c, and 106 more — that turn that query into a two-keystroke habit. Source it once from ~/.psqlrc and every shortcut is available in every interactive psql session, against any database with pg_health_framework installed.

This is a client-side convenience only. The file is never installed into a database, never touched by make, and isn't part of the extension bundle — it's pure psql \set variable definitions, the same mechanism psql's own manual documents for building personal query shortcuts.

Setting it up¶

Locate the file. It ships at tools/pghf_sql_shortcuts.sql in this repository. Keep it wherever suits you — inside a checked-out copy of the repo, or copied somewhere permanent like ~/.pghf_sql_shortcuts.sql.
Reference it from ~/.psqlrc. psql reads ~/.psqlrc once, automatically, at the start of every interactive session. Create the file if you don't already have one, and add a single line:

\i /absolute/path/to/pghf_sql_shortcuts.sql

Use an absolute path — ~/.psqlrc is read from whatever directory you happen to be in when you launch psql, so a relative path would resolve differently session to session.

Open a new psql session. ~/.psqlrc only runs at startup, so an already-open session won't pick up the change — start a fresh one (or run \i /absolute/path/to/pghf_sql_shortcuts.sql by hand in your current session to load it immediately).

That's it — every shortcut below is now available at the prompt, in any database, as long as that database has pg_health_framework installed (see the caveat below).

Naming: `hc<time><_severity>` and `hce<time><_severity>`¶

Every shortcut name starts with hc ("health check") — a polled-only shortcut — or hce ("health check, event") — its on-event-only counterpart — optionally followed by a time scope, optionally followed by a severity scope. The two families are otherwise identical in shape: same columns, same time buckets, same severity suffixes — the only thing that differs between hc* and its hce* twin is which half of check_execution_mode it queries. See Two families, one split below for why they're kept apart rather than merged into one shortcut.

Time scope — omit it for no time filter (every check, however long ago it last ran), or pick one of 1m 5m 15m 30m 1h 2h 4h 6h 8h 12h 1d 2d 1w. A time-scoped shortcut filters to checks whose last_run_at falls within that long ago — "has run at least once in the last <time>." For an hce* shortcut, that's "the triggering event fired at least once in the last <time>," not "the condition is still true now" — see Two families, one split.
Severity scope — omit it for no severity filter within that scope (every current_status, ok/info included), or pick one of three suffixes:

Suffix	Matches	Meaning
`_w`	`current_status IN ('warning', 'critical')`	Needs attention, in the two familiar alert tiers
`_c`	`current_status IN ('critical')`	Critical only
`_e`	`current_status IN ('error_in_performing_check', 'invalid', 'not_evaluable', 'skipped')`	Something's broken, not just bad — a check that errored outright, a threshold evaluator that raised, a check with no threshold configured, or one that's currently self-skipping

This is a strict, literal match on those exact status names — deliberately not the rank-floor convention the rest of the framework uses elsewhere (e.g. pghf.list_alert_status(p_min_status => 'warning'), which also pulls in everything that outranks warning on the severity ladder). _w here means exactly warning-or-critical, nothing else — which is precisely why _e exists as a separate suffix: without it, a check whose evaluator started raising (invalid) or that started failing outright (error_in_performing_check) wouldn't show up under _w or _c at all, even though both outrank critical on the ladder. Reach for _e specifically when you want to sweep for framework/configuration problems, as distinct from _w/_c's "the thing being monitored looks bad" sweep. For hce*, _e also catches an on-event check that hasn't been evaluated yet (not_evaluable) — see pghf.evaluate_pending_event_alerts().

Combine both freely: :hc2d_w is "warning or critical, and has run in the last 2 days, among polled checks." :hce2d_w is the same, among on-event checks instead. :hc alone (no time, no severity) is the fully unfiltered base query over polled checks; :hce alone is the same over on-event checks.

Why an underscore, not a hyphen. psql variable names accepted by \set may contain only letters, digits, and underscore — nothing else. Tested directly against psql: \set foo-bar ... is rejected outright with invalid variable name: "foo-bar", and even where a punctuation character doesn't cause an outright error, :name-with-punctuation in a command only interpolates the alphanumeric part and leaves the rest as literal, unsubstituted text. Underscore is the only punctuation character psql's variable-name grammar accepts, so _w/_c/_e — not -w/-c/-e — is the only version that actually works.

Two families, one split: `hc` vs `hce`¶

An on-event check's pghf.alert_status row means something different from a polled check's — "this specific event last happened at time T," not "this condition is still true right now" — so mixing the two into one alphabetical list would misrepresent both. Rather than hide on-event rows from the shortcuts entirely, every shortcut carries an explicit AND check_execution_mode = 'polled' (the hc* family) or AND check_execution_mode = 'on_event' (the hce* family), so the two are always kept in separate result sets. Every non-retired check's alert_status row shows up under exactly one family, never both, never neither.

There's no combined hc*-plus-hce* shortcut — query pghf.alert_status directly, or call pghf.list_alert_status(p_include_on_event => true), when you genuinely want both kinds in one result set. See On-event checks for the full model behind that distinction.

The full table¶

All 112 shortcuts — 56 hc* (polled) plus their 56 hce* (on-event) twins, 14 time scopes × 4 severity variants each. Every one shares the same columns and only differs in its WHERE clause — see Output columns below. Swap hc for hce in any cell below to get its on-event counterpart (e.g. hc2d_w → hce2d_w).

Time scope (`last_run_at >=` this long ago)	all statuses	`_w` (warn+crit)	`_c` (crit only)	`_e` (error/invalid/not_evaluable/skipped)
(no filter — all time)	`hc` / `hce`	`hc_w` / `hce_w`	`hc_c` / `hce_c`	`hc_e` / `hce_e`
1 minute	`hc1m` / `hce1m`	`hc1m_w` / `hce1m_w`	`hc1m_c` / `hce1m_c`	`hc1m_e` / `hce1m_e`
5 minutes	`hc5m` / `hce5m`	`hc5m_w` / `hce5m_w`	`hc5m_c` / `hce5m_c`	`hc5m_e` / `hce5m_e`
15 minutes	`hc15m` / `hce15m`	`hc15m_w` / `hce15m_w`	`hc15m_c` / `hce15m_c`	`hc15m_e` / `hce15m_e`
30 minutes	`hc30m` / `hce30m`	`hc30m_w` / `hce30m_w`	`hc30m_c` / `hce30m_c`	`hc30m_e` / `hce30m_e`
1 hour	`hc1h` / `hce1h`	`hc1h_w` / `hce1h_w`	`hc1h_c` / `hce1h_c`	`hc1h_e` / `hce1h_e`
2 hours	`hc2h` / `hce2h`	`hc2h_w` / `hce2h_w`	`hc2h_c` / `hce2h_c`	`hc2h_e` / `hce2h_e`
4 hours	`hc4h` / `hce4h`	`hc4h_w` / `hce4h_w`	`hc4h_c` / `hce4h_c`	`hc4h_e` / `hce4h_e`
6 hours	`hc6h` / `hce6h`	`hc6h_w` / `hce6h_w`	`hc6h_c` / `hce6h_c`	`hc6h_e` / `hce6h_e`
8 hours	`hc8h` / `hce8h`	`hc8h_w` / `hce8h_w`	`hc8h_c` / `hce8h_c`	`hc8h_e` / `hce8h_e`
12 hours	`hc12h` / `hce12h`	`hc12h_w` / `hce12h_w`	`hc12h_c` / `hce12h_c`	`hc12h_e` / `hce12h_e`
1 day	`hc1d` / `hce1d`	`hc1d_w` / `hce1d_w`	`hc1d_c` / `hce1d_c`	`hc1d_e` / `hce1d_e`
2 days	`hc2d` / `hce2d`	`hc2d_w` / `hce2d_w`	`hc2d_c` / `hce2d_c`	`hc2d_e` / `hce2d_e`
1 week	`hc1w` / `hce1w`	`hc1w_w` / `hce1w_w`	`hc1w_c` / `hce1w_c`	`hc1w_e` / `hce1w_e`

Output columns¶

Every shortcut returns the same eight columns — only the WHERE clause varies:

Column	Meaning
`check_id`, `alert_name`	Identity
`current_status`	The literal confirmed status right now
`status_duration`	`pghf.friendly_time()` of how long the check has held that status
`last_run_at`	Raw timestamp of the check's most recent run
`last_ran`	`friendly_time()` of how long ago that was
`last_ok_at`	Raw timestamp the check last confirmed `ok` — `NULL` if never
`since_ok`	`friendly_time()` of that, blank if `last_ok_at` is `NULL`

Retired checks are always excluded (WHERE NOT check_is_retired), and results are ordered by check_id alone — plain alphabetical, not worst-severity-first like pghf.list_alert_status() elsewhere in this framework. That's deliberate: run the same shortcut twice in a row and the same check lands on the same row both times, which matters more than severity ordering when you're eyeballing a fixed terminal window for what changed between two invocations.

Worked examples¶

Everything below assumes a psql session already connected to a database with pg_health_framework installed, and ~/.psqlrc set up per Setting it up above.

Everything, unfiltered — the base query, useful as a sanity check that the catalog and alert_status are populated at all:

mydb=# :hc
  check_id  |            alert_name             | current_status | status_duration |          last_run_at          | last_ran | last_ok_at | since_ok
------------+------------------------------------+-----------------+------------------+--------------------------------+----------+------------+----------
 PGHF01-003 | Connection saturation              | ok              | 3d 4h 5m 26s     | 2026-08-26 09:12:03.441201+10 | 3d 4h... | 2026-08-26 09:12:03... | 3d 4h...
 PGHF04-002 | Blocked sessions                   | ok              | 3d 4h 5m 26s     | 2026-08-26 09:12:03.441201+10 | 3d 4h... | 2026-08-26 09:12:03... | 3d 4h...
 PGHF11-009 | Superuser login roles              | critical        | 1h 15m           | 2026-08-29 12:02:03.441201+10 | 1h 15m   |            |
 ...
(175 rows)

What's critical right now, regardless of when it last ran:

mydb=# :hc_c
  check_id  |          alert_name           | current_status | status_duration | ...
------------+--------------------------------+-----------------+------------------+ ...
 PGHF11-009 | Superuser login roles as % of ... | critical     | 1h 15m           | ...
(1 row)

What's warning-or-critical and still fresh — a check that stopped running two weeks ago and was critical back then is a stale-monitoring problem, not necessarily a live one; :hc2d_w excludes it:

mydb=# :hc2d_w
  check_id  |          alert_name           | current_status | status_duration | ...
------------+--------------------------------+-----------------+------------------+ ...
 PGHF11-009 | Superuser login roles as % of ... | critical     | 1h 15m           | ...
(1 row)

Config/framework hygiene sweep — checks with no threshold, an erroring evaluator, or that are self-skipping, in the last day:

mydb=# :hc1d_e
  check_id  |            alert_name             | current_status |  ...
------------+------------------------------------+-----------------+ ...
 PGHF05-016 | pg_stat_io eviction pressure ...  | not_evaluable    | ...
 PGHF12-002 | Spock subscriptions enabled        | skipped          | ...
(2 rows)

Quick "did that manual run actually happen" check right after calling run_and_evaluate() by hand:

mydb=# CALL pghf.run_and_evaluate(p_run_key => 'seeded-5m');
mydb=# :hc1m

What on-event checks have fired recently, and what severity they last reported — the hce* counterpart to :hc1d, over check_execution_mode = 'on_event' instead of 'polled':

mydb=# :hce1d
  check_id  |          alert_name           | current_status | status_duration | ...
------------+--------------------------------+-----------------+------------------+ ...
 X01-003    | widget job failed              | critical         | 6m               | ...
(1 row)

Its _c/_w/_e suffixes work the same way as hc*'s: :hce_c is "every on-event check whose last-reported severity was critical, regardless of when," :hce1d_e is "on-event checks that fired in the last day and came back not_evaluable" — a real backlog signal if pghf.evaluate_pending_event_alerts() isn't scheduled, or has fallen behind.

Repeating a shortcut automatically — psql's built-in \watch re-runs the last executed query, so run a shortcut once and follow it with \watch:

mydb=# :hc_c
  ... (current critical checks) ...
mydb=# \watch 30

This re-runs the exact same :hc_c query every 30 seconds, right in the terminal — no extra tooling, no extra typing.

One caveat: this is per-database¶

~/.psqlrc runs once at session startup and doesn't know what database you'll \c into afterward, so all 112 \set definitions load unconditionally, every session — including a session against a database that doesn't have pg_health_framework installed at all. Running a shortcut there fails exactly the way running the equivalent hand-typed SELECT ... FROM pghf.alert_status would:

otherdb=# :hc
ERROR:  relation "pghf.alert_status" does not exist

That's expected, not a bug in the shortcuts file — \i just loads a set of variable definitions; nothing runs (or can fail) until you actually invoke :hc. Loading it unconditionally is what makes it available the instant you connect to a database that does have the extension, without needing a per-database ~/.psqlrc or a manual \i every time.

Customizing¶

tools/pghf_sql_shortcuts.sql is plain text — open it directly if you want different time buckets, a different severity grouping, or extra columns. Every shortcut follows one of two identical templates, hc* or hce* (only the time-interval literal and the severity IN (...) list change within a family; only the check_execution_mode literal differs between the two families), so a new variant is a matter of copying an existing \set line and editing those pieces. One thing worth knowing if you do: any single quote you introduce inside the embedded SQL — a string literal like interval '5 minutes', or a status name like 'critical' — must be doubled ('') to survive psql's own \set argument parsing, since the whole value is itself wrapped in a single-quoted token. Verify a new shortcut by \echo-ing it before relying on it — \echo :your_new_shortcut prints the fully reconstructed SQL text, letting you confirm the quoting survived before it ever reaches the query engine.

Continue to the Catalog book, the Reference Guide, Adding Your Own Checks, or the Events book — this is the last chapter of the User Guide.