← back to Wallco Ai
night-builder: split CTE → 2 separate psql calls (PG data-modifying CTE snapshot bug)
cb7f3a51e676bb9950cc1e54f1db1a8f1dbbba59 · 2026-05-20 07:40:19 -0700 · Steve Abrams
The previous CTE-based fix (commit f67ea3a) inserted PLACEHOLDER rows
but the UPDATE branch returned no id — symptom was 21 new PLACEHOLDER
rows accumulating between worker restart and bulk-UPDATE catchup.
Root cause: PG's data-modifying CTEs share one snapshot, so 'fixed' AS
UPDATE couldn't see the row that 'inserted' AS INSERT had just added.
Per docs: 'order in which the specified updates actually happen is
unpredictable.'
Replaced with two sequential psql calls:
1. INSERT ... RETURNING id → captures the new row's id
2. UPDATE ... WHERE id=<that> → patches image_url with the real id
Two round trips but reliable. Workers restarted to pick up patch.
Files touched
M scripts/night-builder.js
Diff
commit cb7f3a51e676bb9950cc1e54f1db1a8f1dbbba59
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed May 20 07:40:19 2026 -0700
night-builder: split CTE → 2 separate psql calls (PG data-modifying CTE snapshot bug)
The previous CTE-based fix (commit f67ea3a) inserted PLACEHOLDER rows
but the UPDATE branch returned no id — symptom was 21 new PLACEHOLDER
rows accumulating between worker restart and bulk-UPDATE catchup.
Root cause: PG's data-modifying CTEs share one snapshot, so 'fixed' AS
UPDATE couldn't see the row that 'inserted' AS INSERT had just added.
Per docs: 'order in which the specified updates actually happen is
unpredictable.'
Replaced with two sequential psql calls:
1. INSERT ... RETURNING id → captures the new row's id
2. UPDATE ... WHERE id=<that> → patches image_url with the real id
Two round trips but reliable. Workers restarted to pick up patch.
---
scripts/night-builder.js | 40 ++++++++++++++++++----------------------
1 file changed, 18 insertions(+), 22 deletions(-)
diff --git a/scripts/night-builder.js b/scripts/night-builder.js
index 8e8a024..39f712b 100755
--- a/scripts/night-builder.js
+++ b/scripts/night-builder.js
@@ -806,29 +806,25 @@ function generateOne(track, cw) {
for (const v of variants) {
if (!fs.existsSync(v.path)) continue;
const cwTitle = `${category} · ${v.slug}`;
- // Single-round-trip CTE — INSERT the colorway child with
- // PLACEHOLDER image_url (we don't know its id yet), then
- // immediately UPDATE that same row to use its assigned id.
- // Prevents the recurring "9k rows stuck at PLACEHOLDER"
- // bug that left those designs invisible on /designs because
- // designHasImage's regex requires /by-id/\d+ (Steve fix,
- // 2026-05-20 — see git log for the bulk PG correction).
- const cwSql = `
- WITH inserted AS (
- INSERT INTO spoon_all_designs (brand, kind, width_in, height_in, panels, generator, prompt, image_url, local_path, dominant_hex, category, is_published, notes, parent_design_id, created_at)
- SELECT brand, kind, width_in, height_in, panels, generator, prompt || E'\\n[colorway-variant of #${id}]', '/designs/img/by-id/PLACEHOLDER', '${v.path.replace(/'/g, "''")}', '${v.ground.replace(/'/g, "''")}', '${cwTitle.replace(/'/g, "''")}', TRUE, E'colorway variant: ${v.slug} (ground=${v.ground}, figure=${v.figure}) of parent #${id}', ${id}, NOW()
- FROM spoon_all_designs WHERE id=${id}
- RETURNING id
- ),
- fixed AS (
- UPDATE spoon_all_designs s
- SET image_url = '/designs/img/by-id/' || s.id::text
- FROM inserted WHERE s.id = inserted.id
- RETURNING s.id
- )
- SELECT id FROM fixed;`;
+ // Two-step fix for the "PLACEHOLDER image_url" bug — see
+ // git log + the bulk PG UPDATE on 2026-05-20. The earlier
+ // single-statement CTE (INSERT + UPDATE in one) failed
+ // because PG's data-modifying CTEs share a snapshot, so the
+ // UPDATE didn't see the just-inserted row and the URL stayed
+ // PLACEHOLDER. Two separate psql calls — INSERT...RETURNING
+ // captures id, then a follow-up UPDATE writes the real URL.
+ const insertSql = `
+ INSERT INTO spoon_all_designs (brand, kind, width_in, height_in, panels, generator, prompt, image_url, local_path, dominant_hex, category, is_published, notes, parent_design_id, created_at)
+ SELECT brand, kind, width_in, height_in, panels, generator, prompt || E'\\n[colorway-variant of #${id}]', '/designs/img/by-id/PLACEHOLDER', '${v.path.replace(/'/g, "''")}', '${v.ground.replace(/'/g, "''")}', '${cwTitle.replace(/'/g, "''")}', TRUE, E'colorway variant: ${v.slug} (ground=${v.ground}, figure=${v.figure}) of parent #${id}', ${id}, NOW()
+ FROM spoon_all_designs WHERE id=${id}
+ RETURNING id;`;
try {
- const cwId = execSync(`/opt/homebrew/opt/postgresql@14/bin/psql dw_unified -At -c "${cwSql.replace(/"/g, '\\"').replace(/\n/g, ' ')}"`, { encoding: 'utf8' }).trim();
+ const cwId = execSync(`/opt/homebrew/opt/postgresql@14/bin/psql dw_unified -At -c "${insertSql.replace(/"/g, '\\"').replace(/\n/g, ' ')}"`, { encoding: 'utf8' }).trim();
+ if (cwId && /^\d+$/.test(cwId)) {
+ // Immediately patch the image_url so the row passes
+ // designHasImage's /by-id/\d+ regex on /designs.
+ execSync(`/opt/homebrew/opt/postgresql@14/bin/psql dw_unified -At -c "UPDATE spoon_all_designs SET image_url='/designs/img/by-id/${cwId}' WHERE id=${cwId};"`, { encoding: 'utf8' });
+ }
logLine(` cw child id=${cwId} (${v.slug}) of parent ${id}`);
} catch (e2) {
logLine(` cw INSERT FAIL ${v.slug}: ${e2.message.slice(0, 200)}`);
← 2279beb Bulk 2-color fix + full-room murals + hue-sync 4-color quart
·
back to Wallco Ai
·
night-builder: debug-log url-patch step + ship self-healing 2ea2f75 →