Bug: pgque.maint() VACUUM branch fails because VACUUM cannot run inside PL/pgSQL function

[NikolayS]

Summary

pgque.maint() has a branch that executes VACUUM dynamically when maint_operations() returns vacuum work. PostgreSQL rejects VACUUM inside a PL/pgSQL function.

This is usually hidden when autovacuum is enabled because maint_tables_to_vacuum() returns no rows, but installations where that branch activates will see pgque.maint() fail instead of maintaining.

Source behavior

In pgque.maint():

elsif f.func_name = 'vacuum' then
    sql := format('vacuum %I.%I', ...);
    execute sql;

Evidence

Disposable DB probe by overriding/simulating maint_tables_to_vacuum() output:

select * from pgque.maint_operations();
-- includes: vacuum | pgque.queue

select pgque.maint();

Observed:

ERROR: VACUUM cannot be executed from a function
CONTEXT: SQL statement "vacuum pgque.queue"
PL/pgSQL function maint() line 20 at EXECUTE

Impact

If PgQue expects pgque.maint() to perform vacuum maintenance for rotated/expired tables, that path cannot work as implemented.

pg_cron job calling select pgque.maint() will fail when vacuum work appears.

Suggested fix options

• Do not run VACUUM inside pgque.maint().
• Return vacuum commands from an advisory function for external scheduler execution.
• Schedule vacuum commands directly via pg_cron as standalone SQL statements.
• Split maintenance: PL/pgSQL-safe operations in maint(), vacuum outside function.

Test

Create a deterministic condition where maint_operations() returns a vacuum row, then assert the maintenance strategy does not fail.

Environment

Tested on main at 9b3f89f.

[NikolayS]

Fix: Option A — drop VACUUM from maint()

PR: #119

Option chosen: A — replace the elsif f.func_name = 'vacuum' block with continue. VACUUM rows from maint_operations() are silently skipped. Autovacuum handles pgque metadata tables by default. A pg_cron example for autovacuum-off installations is in the function comment.

This matches PgQ heritage: the original PgQ engine never ran VACUUM inside its PL/pgSQL maintenance functions.

---

Red commit c297ce6 — test output (failing)

CREATE FUNCTION
NOTICE:  precondition OK: maint_operations() returned 1 vacuum row(s)
DO
ERROR:  VACUUM cannot be executed from a function
CONTEXT:  SQL statement "vacuum pgque.queue"
PL/pgSQL function maint() line 33 at EXECUTE
PL/pgSQL function inline_code_block line 5 at assignment

Green commit a0c98eb — test output (passing)

CREATE FUNCTION
NOTICE:  precondition OK: maint_operations() returned 1 vacuum row(s)
DO
NOTICE:  PASS: maint_vacuum - maint() completed without error (returned 0)
DO
CREATE FUNCTION

[NikolayS]

REV follow-ups from #119 (comment) addressed. Three new commits pushed to fix/maint-vacuum:

• 737f800 docs: fix maint() vacuum description (#110) — BLOCKING resolved: removed "vacuum of expired tables" claim; added VACUUM limitation, autovacuum default, and pg_cron guidance.
• 29a78b7 fix(maint): notice on VACUUM skip, note on extra_maint — B1: raise notice emitted when a VACUUM row is skipped; B3: function comment warns that user-supplied queue_extra_maint functions are also subject to the no-VACUUM-in-PL/pgSQL rule. Both sql/pgque-api/maint.sql and sql/pgque.sql updated.
• 57f5ceb test: mixed-case assertion for maint() VACUUM skip — optional gap: asserts non-VACUUM operations still execute when VACUUM rows appear in the same maint_operations() result.

PR body updated with B2 changelog one-liner. CI running.

[NikolayS]

PR #119 mixed-case test fixed in 227ab4a.

Root cause: the original assert v_result > 0 was wrong — pgque.maint() returns the SUM of extra_maint hook return values, and a fresh queue with no rotation work due correctly returns 0. Per REV r2, replaced with a queue_extra_maint sentinel: a pgque_test_extra_maint(text) function that inserts into public.pgque_test_sentinel and returns 1 is registered on the test queue, then the test asserts both v_result >= 1 (accumulated hook return) and sentinel_count >= 1 (hook actually ran). CI running on all 5 PG versions.

[NikolayS]

Loop 3 fix — commit 225c964

Root cause (from CI log, run 25159515564)

The sentinel function added in commit 227ab4a was declared `language sql` but contained `return 1;` — a PL/pgSQL keyword that is a syntax error in SQL-language functions. CI failed at CREATE FUNCTION time with:

```
psql:tests/test_maint_vacuum.sql:79: ERROR: syntax error at or near "return"
LINE 4: return 1;
```

This happened on all five PG versions (14–18) identically.

Fix approach

Switch the sentinel function from `language sql` to `language plpgsql` with a proper `begin … return 1; end;` body. The schema-qualified insert into `public.pgque_test_sentinel` and the schema-qualified function name in `queue_extra_maint` are both correct and unchanged.

Commit

225c964

CI status

Run https://github.com/NikolayS/pgque/actions/runs/25160064987 in progress — will update once complete.

[NikolayS]

CI green — run https://github.com/NikolayS/pgque/actions/runs/25160064987

All 5 PG versions pass: PG 14 success, PG 15 success, PG 16 success, PG 17 success, PG 18 success.