lib/db: lock_timeout=8000 on wallco's own psql connections so a contended boot ALTER can't wedge the whole server before app.listen

71c86fd26408a222c35e79876539f27a91f89e84 · 2026-05-31 00:02:54 -0700 · Steve Abrams

Boot ran unconditional ALTER TABLE all_designs migrations via synchronous
execSync(psql) with no lock_timeout; under concurrent DB load the ALTER queued
for ACCESS EXCLUSIVE behind a long reader and hung the single-threaded event
loop forever, so :9905 never bound and every Generator/Studio endpoint returned
connection-refused. PGOPTIONS lock_timeout is scoped to wallco's own psql procs
(other sessions unaffected); contended statements now fail fast (caught by the
existing try/catch; boot migrations are IF-NOT-EXISTS no-ops). lock_timeout
bounds lock-WAIT only, so long catalog reads are unaffected. Override via
WALLCO_LOCK_TIMEOUT_MS.

Files touched

M lib/db.js

Diff

commit 71c86fd26408a222c35e79876539f27a91f89e84
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun May 31 00:02:54 2026 -0700

    lib/db: lock_timeout=8000 on wallco's own psql connections so a contended boot ALTER can't wedge the whole server before app.listen
    
    Boot ran unconditional ALTER TABLE all_designs migrations via synchronous
    execSync(psql) with no lock_timeout; under concurrent DB load the ALTER queued
    for ACCESS EXCLUSIVE behind a long reader and hung the single-threaded event
    loop forever, so :9905 never bound and every Generator/Studio endpoint returned
    connection-refused. PGOPTIONS lock_timeout is scoped to wallco's own psql procs
    (other sessions unaffected); contended statements now fail fast (caught by the
    existing try/catch; boot migrations are IF-NOT-EXISTS no-ops). lock_timeout
    bounds lock-WAIT only, so long catalog reads are unaffected. Override via
    WALLCO_LOCK_TIMEOUT_MS.
---
 lib/db.js | 21 +++++++++++++++++++--
 1 file changed, 19 insertions(+), 2 deletions(-)

diff --git a/lib/db.js b/lib/db.js
index bce7582..1cd3b7b 100644
--- a/lib/db.js
+++ b/lib/db.js
@@ -23,11 +23,28 @@ const PSQL_CMD = (process.platform === 'linux')
   ? `sudo -n -u postgres psql ${DB_NAME} -At -q`
   : `psql ${DB_NAME} -At -q`;
 
+// lock_timeout guard (2026-05-30): every call here is a SYNCHRONOUS execSync,
+// so any psql that blocks waiting on a Postgres lock hangs the entire
+// single-threaded node event loop until the lock clears. At BOOT this is fatal:
+// the unconditional `ALTER TABLE all_designs ...` migrations (ensureUpscaleBytes/
+// ensureUserRemoved/...) need ACCESS EXCLUSIVE, and on this busy multi-session
+// box they queue behind a long catalog reader — wedging the boot BEFORE
+// app.listen so :9905 never binds and the whole site looks dead (Generator/Studio
+// hooks all return connection-refused). PGOPTIONS sets lock_timeout for wallco's
+// OWN psql connections only (other sessions spawn their own psql, unaffected), so
+// a contended statement fails fast (caught by callers' try/catch + the boot
+// migrations are IF-NOT-EXISTS no-ops) instead of hanging forever. lock_timeout
+// bounds only time spent WAITING for a lock — long-running reads (the 6-40s
+// json_agg catalog load) are NOT affected.
+const EXEC_ENV = Object.assign({}, process.env, {
+  PGOPTIONS: `${process.env.PGOPTIONS ? process.env.PGOPTIONS + ' ' : ''}-c lock_timeout=${process.env.WALLCO_LOCK_TIMEOUT_MS || '8000'}`,
+});
+
 // Stdin-fed psql call. NEVER shell-pipe SQL as a -c "${sql}" argument —
 // double-quote injection. (SEC-1 fix in commit cf388a2 covers this for the
 // last remaining shell-pipe site.)
 function psqlQuery(sql) {
-  return execSync(PSQL_CMD, { input: sql, encoding: 'utf8', maxBuffer: 200_000_000 }).trim();
+  return execSync(PSQL_CMD, { input: sql, encoding: 'utf8', maxBuffer: 200_000_000, env: EXEC_ENV }).trim();
 }
 
 // PG value escaper for inline SQL — matches the project's existing inline-string style.
@@ -55,7 +72,7 @@ function pgEsc(v) {
 const PSQL_CMD_STRICT = `${PSQL_CMD} -v ON_ERROR_STOP=1`;
 function psqlExecLocal(sql) {
   try {
-    return execSync(PSQL_CMD_STRICT, { input: sql, encoding: 'utf8', maxBuffer: 200_000_000 }).trim();
+    return execSync(PSQL_CMD_STRICT, { input: sql, encoding: 'utf8', maxBuffer: 200_000_000, env: EXEC_ENV }).trim();
   } catch (e) {
     const stderr = (e.stderr ? e.stderr.toString() : '').trim();
     const stdout = (e.stdout ? e.stdout.toString() : '').trim();

← 31a8d96 Publish drunk-animals batch 5 (39341,39366,39367,39370,39371 · back to Wallco Ai · fix luxe triage report: real data (567 clean, 98% drunk-anim 87d7964 →