sync-shopify-products: guard no-op ALTER TABLE with column-count check (TK-11867 FIX 2)

b92149d6d6d08552bf8c5623e5e549f1f053825c · 2026-09-17 04:05:47 -0700 · steve@designerwallcoverings.com

All 6 monitor columns already exist on prod; the unconditional ALTER TABLE demanded
ACCESS EXCLUSIVE and died on lock_timeout (55P03) against ~40 live storefront readers.
Guard it so the lock is only taken when a column is genuinely missing.
Undo: git revert HEAD  or restore shopify/scripts/sync-shopify-products.js.bak-TK11867

Files touched

M shopify/scripts/sync-shopify-products.js

Diff

commit b92149d6d6d08552bf8c5623e5e549f1f053825c
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date:   Thu Sep 17 04:05:47 2026 -0700

    sync-shopify-products: guard no-op ALTER TABLE with column-count check (TK-11867 FIX 2)
    
    All 6 monitor columns already exist on prod; the unconditional ALTER TABLE demanded
    ACCESS EXCLUSIVE and died on lock_timeout (55P03) against ~40 live storefront readers.
    Guard it so the lock is only taken when a column is genuinely missing.
    Undo: git revert HEAD  or restore shopify/scripts/sync-shopify-products.js.bak-TK11867
---
 shopify/scripts/sync-shopify-products.js | 28 +++++++++++++++++++---------
 1 file changed, 19 insertions(+), 9 deletions(-)

diff --git a/shopify/scripts/sync-shopify-products.js b/shopify/scripts/sync-shopify-products.js
index a2ae29d0..10fe21b4 100644
--- a/shopify/scripts/sync-shopify-products.js
+++ b/shopify/scripts/sync-shopify-products.js
@@ -552,15 +552,25 @@ async function syncProducts(quickMode = false) {
   // 5-field monitor columns (idempotent — added once). The mirror now records
   // per-product variant price/shape + description presence so the recurring
   // 5-field canary can run off the mirror instead of a separate live scan.
-  await pool.query(`
-    ALTER TABLE shopify_products
-      ADD COLUMN IF NOT EXISTS body_html text,
-      ADD COLUMN IF NOT EXISTS variant_count int,
-      ADD COLUMN IF NOT EXISTS min_variant_price numeric(10,2),
-      ADD COLUMN IF NOT EXISTS has_sample_variant boolean,
-      ADD COLUMN IF NOT EXISTS has_product_variant boolean,
-      ADD COLUMN IF NOT EXISTS has_description boolean;
-  `);
+  // Only take ACCESS EXCLUSIVE lock if a column is genuinely missing — otherwise this
+  // no-op ALTER head-blocks against the ~40 live storefront readers on Kamatera and dies
+  // on lock_timeout (55P03). Fix: check first, alter only when needed. (TK-11867)
+  const _cols = await pool.query(`
+    SELECT count(*)::int AS n FROM information_schema.columns
+    WHERE table_name='shopify_products'
+      AND column_name IN ('body_html','variant_count','min_variant_price',
+                          'has_sample_variant','has_product_variant','has_description')`);
+  if (_cols.rows[0].n < 6) {
+    await pool.query(`
+      ALTER TABLE shopify_products
+        ADD COLUMN IF NOT EXISTS body_html text,
+        ADD COLUMN IF NOT EXISTS variant_count int,
+        ADD COLUMN IF NOT EXISTS min_variant_price numeric(10,2),
+        ADD COLUMN IF NOT EXISTS has_sample_variant boolean,
+        ADD COLUMN IF NOT EXISTS has_product_variant boolean,
+        ADD COLUMN IF NOT EXISTS has_description boolean;
+    `);
+  }
   // Get last sync time for quick mode
   let lastSync = null;
   if (quickMode) {

← e0a4970f auto-data-snapshot: 2026-09-17T03:58:38 (3 data files) — sho · back to Designer Wallcoverings · auto-data-snapshot: 2026-09-17T05:03:29 (3 data files) — sho 30569281 →