pg_background v2.0.4 — Worker SQL Execution Fixes

Summary

Critical patch release fixing four data correctness and behavior bugs in multi-command SQL strings and result type handling. These bugs caused silent data corruption, timeouts without effect, worker crashes on complex types, and incorrect command tag reporting.

Bugs Fixed

1. Multi-command strings: Commands don't see each other's changes

• Impact: Silent data corruption. Multi-command strings like UPDATE t SET x=x+1; UPDATE t SET x=x+1 would only increment once instead of twice.
• Root cause: CommandCounterIncrement() only called after the last command, violating PostgreSQL's visibility semantics.
• Fix: Call CommandCounterIncrement() after each command except the last, matching exec_simple_query() behavior.

2. SET statement_timeout inside SQL strings has no effect

• Impact: Timeout settings ignored in multi-command strings. Pattern from README (SET statement_timeout = '5min'; SELECT slow_function()) did nothing.
• Root cause: Timeout armed once before first command, not re-armed per command.
• Fix: Apply statement_timeout to each command separately when pg_background.worker_timeout is not set. Parsing now counts toward first command timeout.
• Behavior change: Without worker_timeout, statement_timeout now limits each command of a multi-command string (not the whole string). Single-command strings unaffected. Documented in README.

3. Result columns without binary I/O crash the worker

• Impact: Worker crash on queries returning aclitem, arrays of aclitem, or nested types without binary I/O functions.
• Root cause: All columns forced to binary format even if type lacks binary I/O functions.
• Fix: New pgbg_type_has_binary_io() function recursively checks type and all nested types for binary I/O support. Worker sends text format for columns lacking binary I/O, reports choice in RowDescription format code, launcher follows.
• Memoization: Type checks memoized to prevent exponential walk on deeply nested types (verified: dag_30 completes in ~10s, not 3.2B steps).

4. result_info.command_tag differs from worker's tag

• Impact: PREPARE q AS SELECT 1; EXECUTE q reports tag='EXECUTE' while result says SELECT.
• Root cause: Used parse-time tag instead of actual completion tag.
• Fix: Use QueryCompletion tag passed to EndCommand(), with parse-time tag as fallback.

Documentation

• README: Clarified statement_timeout per-command semantics and worker_timeout all-commands semantics.
• docs/ARCHITECTURE.md: Documented portal-based execution (not SPI), type checking walk, memoization strategy.
• CLAUDE.md / .github/copilot-instructions.md: Updated to describe portal execution model.
• Result metadata guarantees: Documented that result_published flag consistency guarantee holds only after worker finishes (multi-command strings overwrite while running).

Testing

All fixes validated with comprehensive regression tests (one per defect):

• Multi-command read/write visibility tests
• Per-command timeout, parsing timeout, worker_timeout semantics
• Complex nested type handling (aclitem, arrays, domains, composites, ranges, multiranges)
• Deeply nested type performance (dag_30 memoization test)
• Format persistence (RowDescription vs. catalog)
• Command tag correctness (PREPARE/EXECUTE)

CI Status: ✅ All 33 checks passing

• PostgreSQL 14–19 beta on Ubuntu 22.04 & 24.04
• Assert-enabled builds, ASan+UBSan, relocatable installs, upgrade paths

Upgrade

No SQL changes. Rebuild and reinstall the extension:

make clean && make && sudo make install

Then restart PostgreSQL or detach/relaunch workers to pick up new binary.

Backwards Compatibility

Behavior changes are fixes to previously-broken functionality:

• Multi-command strings now work correctly (were silently broken).
• SET statement_timeout in strings now works (was ineffective).
• Complex result types no longer crash (were failing).
• Command tags now match completion (were misreported).

No breaking API changes. No SQL migration required.

Supported PostgreSQL Versions

• PostgreSQL 14, 15, 16, 17, 18, 19 (beta)

Credits

Fixed by Fenoman (@Fenoman). Authored comprehensive regression test suite covering all defects and edge cases. All CI validations passing across 33 matrix configurations.