guard: every batch fails with 42P08 — untyped $2 params in jsonb_build_object (applyDecision)

[DamieMoon]

Summary

The guard scheduler fails on every batch with SQLSTATE 42P08 (could not determine data type of parameter $2). The cause is untyped bind parameters passed into jsonb_build_object(...) inside applyDecision(). Because pgx uses the extended protocol (prepared statements), PostgreSQL must infer parameter types at PREPARE time, and jsonb_build_object(VARIADIC "any") gives it nothing to infer from → deterministic failure on every call.

Net effect: the guard subsystem has never completed a single batch on this deploy. Near-duplicate detection and auto-archive are non-functional. Core store/query/retrieval are on a separate code path and are unaffected — ctx otherwise works normally.

Verified by live reproduction against the running DB (PREPARE-only, no data mutated).

---

Environment

Repo checkout	GottZ/ctx @ branch root, commit 7091825
PostgreSQL	18.4 (Debian)
Extensions	pgvector 0.8.2, timescaledb 2.27.1
Image	pgvector-timescaledb:pg18
Driver	pgx (extended protocol / prepared statements)
Host	MacBook Pro (Mac14,9), Apple M2 Pro (10-core: 6P/4E), 16 GB RAM
OS	macOS 26.5 (build 25F71), arm64
Container runtime	OrbStack (Docker API v29.4.0, client+server), aarch64

First failure observed ~3 min after container creation. Persists across container restarts and host reboot.

---

Symptom (daemon logs, verified)

Every guard batch (60s interval) produces, in order:

guard: apply decision failed ... error":"mark checked: ERROR: could not determine data type of parameter $2 (SQLSTATE 42P08)"
   (block_id = whichever block is processed first in the batch)
current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02)
   + guard: update state failed     (for every subsequent block in the batch)
scheduler: guard batch error ... commit unexpectedly resulted in rollback

The error string mark checked: identifies the failing site as the else branch of applyDecision() (the needs_review/clean path, which wraps the error with mark checked: %w). The first block in a batch is typically clean, so the else branch fails first; the transaction aborts (25P02), which then takes down every subsequent block in the same batch, and the final COMMIT rolls back.

---

Root cause (verified)

File: go/internal/guard/guard.go — func applyDecision(...) (L185-237 @ 7091825).

Both UPDATE branches build a JSONB metadata patch with bare parameters:

else branch (the one that surfaces in the error), L215-230:

_, err := tx.Exec(ctx,
    `UPDATE context_blocks SET
        guard_status = $2,
        metadata = COALESCE(metadata, '{}'::jsonb) || jsonb_build_object(
            'guard_status', $2,
            'guard_checked_at', $3,
            'guard_similarity', $4,
            'guard_matched_id', $5,
            'guard_is_cross_scope', $6,
            'guard_threshold_duplicate', 0.98,
            'guard_threshold_review', 0.92
        ),
        updated_at = now()
    WHERE id = $1`,
    blockID, result.Decision, checkedAt, result.Similarity, matchedIDVal, result.IsCrossScope,
)

near_duplicate branch, L195-209 — same pattern (note its column assignment is the literal guard_status = 'archived_dup', so only the jsonb_build_object carries the untyped params):

metadata = COALESCE(metadata, '{}'::jsonb) || jsonb_build_object(
    'guard_status', $2,
    'guard_checked_at', $3,
    'guard_similarity', $4,
    'guard_matched_id', $5,
    'guard_is_cross_scope', $6
),

Parameter bindings (identical in both branches):

Param	Go value	Go type	Intended PG type
$1	blockID	string	uuid/text
$2	result.Decision	string	text
$3	checkedAt	string (RFC3339)	text
$4	result.Similarity	float64	float8
$5	matchedIDVal	string	text
$6	result.IsCrossScope	bool	bool

jsonb_build_object has signature jsonb_build_object(VARIADIC "any"). Every value argument is "any", so PostgreSQL cannot derive a concrete type for $2–$6 at PREPARE time and aborts with 42P08. The error caret points at the jsonb_build_object line.

Note the guard_status = $2 column assignment in the else branch does not rescue the inference: although guard_status is character varying, the same $2 is also used in the "any" context, and PG reports the undetermined parameter against the jsonb_build_object usage.

---

Reproduction (verified — live DB, PREPARE only, no rows mutated)

Failing form:

PREPARE guard_fail AS
UPDATE context_blocks SET
    guard_status = $2,
    metadata = COALESCE(metadata, '{}'::jsonb) || jsonb_build_object(
        'guard_status', $2, 'guard_checked_at', $3, 'guard_similarity', $4,
        'guard_matched_id', $5, 'guard_is_cross_scope', $6
    ),
    updated_at = now()
WHERE id = $1;
-- ERROR: could not determine data type of parameter $2 (SQLSTATE 42P08)

Working form (explicit casts):

PREPARE guard_ok AS
UPDATE context_blocks SET
    guard_status = $2::text,
    metadata = COALESCE(metadata, '{}'::jsonb) || jsonb_build_object(
        'guard_status', $2::text, 'guard_checked_at', $3::text, 'guard_similarity', $4::float8,
        'guard_matched_id', $5::text, 'guard_is_cross_scope', $6::bool
    ),
    updated_at = now()
WHERE id = $1;
-- PREPARE

---

Fix (verified)

Add explicit casts inside both jsonb_build_object(...) calls in applyDecision(). ~2 changed lines per branch.

         metadata = COALESCE(metadata, '{}'::jsonb) || jsonb_build_object(
-            'guard_status', $2,
-            'guard_checked_at', $3,
-            'guard_similarity', $4,
-            'guard_matched_id', $5,
-            'guard_is_cross_scope', $6
+            'guard_status', $2::text,
+            'guard_checked_at', $3::text,
+            'guard_similarity', $4::float8,
+            'guard_matched_id', $5::text,
+            'guard_is_cross_scope', $6::bool
         ),

Apply the same casts to the else branch (which additionally has the two literal 0.98 / 0.92 keys — those need no change). Casting the guard_status = $2::text column assignment is optional and harmless.

In-repo precedent: writeAuditLog() in the same file (L256-266) already casts its bind params explicitly ($1::uuid, $2::uuid, ...), which is exactly why that statement prepares cleanly. The fix just brings applyDecision() in line with that existing pattern.

---

Impact

• Guard scheduler has never completed a batch since DB creation.
• All blocks remain at the insert-default guard_status = 'active'; 0 have ever been guard-processed.
• Near-duplicate detection + auto-archive are effectively disabled.
• Each failed batch is one aborted transaction, rolling back any guard state updates batched within it.
• Unaffected: core store / query / retrieval (separate code path).

---

Open questions (unverified — for maintainer)

• This is likely surfaced or worsened by PG18's stricter parameter-type inference. It may have been latent — silently working — on older PostgreSQL versions or under a different pgx prepare mode. Does it reproduce on your targeted PG version?
• Would running this statement under QueryExecModeSimpleProtocol mask the failure? If so, the explicit-cast fix is still the correct one (it's protocol-independent), but it would explain why the bug wasn't caught earlier.

[GottZ]

Fixed in v1.6.6 — thanks for the issue, it went straight to the patch. The full root-cause writeup with the in-repo writeAuditLog precedent and the PREPARE-only reproduction meant there was nothing left to investigate.

Two notes on what we found in the fix:

1. PG18 rejected the "optional and harmless" version of the column cast. With $2::text only inside jsonb_build_object and a bare guard_status = $2 on the column assignment, PG threw inconsistent types deduced for parameter $2 — text versus character varying. So the fix that actually landed has guard_status = $2::text on both sites — anchoring the bind to a single type. Heads-up for anyone hitting the same pattern.

2. We ran a 10-agent audit across the rest of the codebase (jsonb/json/agg, ARRAY/unnest, NULL binds, COALESCE/CASE, migrations, handler+MCP paths) to check for sibling sites. No other untyped-bind-into-VARIADIC "any" cases — the writeAuditLog style with explicit $N::uuid casts is otherwise consistent throughout. Empirical probe in .project/bench-session-38-pg18-audit/ also confirmed the real SQLSTATE under pgx-extended is 42P18 (indeterminate_datatype); 42P08 is what the wrapper surfaces.

You're now in CONTRIBUTORS.md. Cheers.