backfill-effective-date: raw BEGIN/COMMIT incompatible with Supabase pooler transaction mode

[ethanbeard]

Symptom

After fixing #786 (the missing engine.connect() call) so v0.29.1 phase B can actually run, the next failure is:

Migration v0.29.1 reported status=partial
{"phases":[{"name":"schema","status":"complete"},{"name":"backfill_effective_date","status":"failed","detail":"UNSAFE_TRANSACTION: Only use sql.begin, sql.reserved or max: 1"}]}

Root cause

src/core/backfill-effective-date.ts wraps each batch in raw BEGIN/COMMIT strings via engine.executeRaw:

if (isPostgres) {
  await engine.executeRaw(`BEGIN`);
  await engine.executeRaw(`SET LOCAL statement_timeout = '600s'`);
}

try {
  for (const r of rows) {
    // ... UPDATE pages ...
  }
  if (isPostgres) await engine.executeRaw(`COMMIT`);
} catch (e) {
  if (isPostgres) {
    try { await engine.executeRaw(`ROLLBACK`); } catch { /* ignore */ }
  }
  throw e;
}

postgres-js refuses raw BEGIN calls outside sql.begin() / sql.reserved() when configured with prepare: false + max: 1 (the Supabase pgbouncer-safe shape). The runtime error is UNSAFE_TRANSACTION: Only use sql.begin, sql.reserved or max: 1.

This bites Supabase users specifically because the only working network path to many projects' Postgres is the pgbouncer transaction-mode pooler (port 6543). The direct port (5432) often resolves only over IPv6 and is unreachable without the IPv4 add-on. v0.30.1's ConnectionManager dual-pool work helps — when a code path goes through it — but backfillEffectiveDate calls engine.executeRaw('BEGIN') directly, bypassing the manager's transaction handling.

Reproduction

1. Supabase project on a plan without IPv4 add-on (so direct 5432 is IPv6-only and unroutable from many networks).
2. Pull v0.30.2.
3. Apply v0.29.1 orchestrator: phaseBBackfill + phaseCVerify miss engine.connect() call #786 fix (or any other workaround so phase B actually executes).
4. Run gbrain apply-migrations --yes. Phase B fails with UNSAFE_TRANSACTION.

Workarounds (none ideal)

1. Remove the explicit BEGIN/COMMIT. Auto-commit per UPDATE is acceptable for additive idempotent work like this backfill. Loses SET LOCAL statement_timeout (it requires a transaction scope), but for typical brain sizes (we ran on 477 pages) the per-row UPDATE is fast enough that the timeout isn't load-bearing. This is what we ran with locally.

2. Route through ConnectionManager. The proper fix per the v0.30.1 hardening direction. ConnectionManager.withTransaction() (or equivalent) knows to use sql.begin() correctly under pgbouncer transaction mode.

3. Detect pooler mode and skip the transaction wrap. Heuristic similar to the existing prepare: false detection in db.ts — port 6543 on Supabase = transaction-mode = no explicit BEGIN.

Local patch

We removed the BEGIN/COMMIT entirely as the simplest unblock:

-      if (isPostgres) {
-        await engine.executeRaw(`BEGIN`);
-        await engine.executeRaw(`SET LOCAL statement_timeout = '600s'`);
-      }
+      // BEGIN/COMMIT removed — Supabase pooler transaction mode rejects
+      // explicit transactions via postgres-js. Auto-commit per UPDATE is
+      // acceptable for additive idempotent work.
       try {
         for (const r of rows) {
           // ... UPDATE pages ...
         }
-        if (isPostgres) await engine.executeRaw(`COMMIT`);
       } catch (e) {
-        if (isPostgres) {
-          try { await engine.executeRaw(`ROLLBACK`); } catch { /* ignore */ }
-        }
         throw e;
       }

This isn't the right upstream fix — workaround (2) is — but might be useful as a baseline diff.

Environment

• gbrain v0.30.2 (master @ c1e2a6d)
• Bun v1.3.13
• Supabase Postgres via pgbouncer transaction-mode pooler (port 6543)
• Direct port: IPv6-only, no route from local network (no IPv4 add-on)
• macOS 25.3.0 (Darwin)

This may be a more general issue — anywhere gbrain core code uses raw BEGIN/COMMIT via executeRaw will break on the same configuration. Worth a git grep -E "executeRaw\\(\\\(BEGIN|COMMIT|ROLLBACK)" src/` audit.

Related: #786 (the missing connect() call that gates this code path running at all).

[ethanbeard]

Update — verified that this is independent of pooler routing

After enabling Supabase IPv4 add-on (so the direct port becomes reachable) and re-testing with GBRAIN_DISABLE_DIRECT_POOL unset and GBRAIN_DATABASE_URL pointed at the direct connection (port 5432), the error reproduces identically:

$ GBRAIN_DATABASE_URL="$SUPABASE_DB_DIRECT_URL" gbrain reindex-frontmatter --yes --force
UNSAFE_TRANSACTION: Only use sql.begin, sql.reserved or max: 1

So the title's framing ("incompatible with Supabase pooler transaction mode") is misleading. The real constraint is postgres-js with max: 1 refuses raw BEGIN calls regardless of port or transaction mode. The Supabase pooler doesn't make it worse — it just made me notice it first because the pooler was the only working path until IPv4 was enabled.

Confirms the right upstream fix is workaround #2 from the issue (route through ConnectionManager / use sql.begin()), not workaround #3 (pooler-detection heuristic). The pooler isn't the discriminator — max: 1 is.

Suggested retitle: "backfill-effective-date: raw BEGIN/COMMIT incompatible with postgres-js max:1 client config"

Same workaround (remove the explicit transaction wrap) still works as a baseline; ConnectionManager-based fix is still preferred upstream direction.

[garrytan]

Closing — fixed in master via the v0.30.3 fix-wave (PR #776, merged at ff53a4c9); the canonical fix for this is #740 (UNSAFE_TRANSACTION on backfillEffectiveDate).

Thanks for the report — please re-test on the latest release and reopen if anything still reproduces.