postgres-jsonb-merge-key-preserve-on-patch
Done. Solution written to ~/postgres-jsonb-merge-key-preserve-on-patch.md and verified live against PostgreSQL 18 (I initialized a throwaway cluster, ran the full test suite, then stopped it).
Summary of the diagnosis and fix:
metadata = COALESCE($4, metadata) is all-or-nothing replacement, not a merge — any non-NULL partial body discards the whole document, wiping reserved sub-objects like metadata.multi_reference. A SELECT-then-UPDATE fix would introduce a lost-update race.metadata || '{"pinned":true}' works only when metadata is a JSON object; on NULL, JSON null, arrays, strings, or numbers, || concatenates and array-wraps the result (verified: '[]'::jsonb || '{"pinned":true}' → [{"pinned":true}]). Hence the jsonb_typeof(metadata) <> 'object' guard is load-bearing.sql
SET metadata =
(CASE WHEN metadata IS NULL OR jsonb_typeof(metadata) <> 'object'
THEN '{}'::jsonb ELSE metadata END)
|| '{"pinned":true}'::jsonb
-- unpin: ... END) - 'pinned'Verification performed:
1. Reproduced the clobber with the old COALESCE form.
2. Assertions confirming multi_reference survives verbatim through pin + unpin.
3. Non-object inputs (NULL, [], "scalar", null) normalize to {"pinned":true} — no array wrap.
4. Two-session concurrency test: a pin and an unrelated key merge both survive, reserved key intact — proving no read-modify-write race.
The markdown is self-contained with runnable SQL/bash, exact observed outputs, reusable helper functions, and caveats (shallow merge, policy on explicitly patching reserved keys).
# Evidence - Problem class: postgres-jsonb-merge-key-preserve-on-patch - Model: openrouter/deepseek/deepseek-v4.1-flash - Solved: 2026-09-17T18:17:12.306Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "A PATCH endpoint that accepted a whole-document jsonb replace (metadata = COALESCE($4, metadata)) clobbers reserved sub-objects when a client sends only a partial document (e.g. {\"pinned\":true} erasing metadata.multi_reference). Fix idiom without a read-modify-write race, in the SAME UPDATE: pin -> (CASE WHEN metadata IS NULL OR jsonb_typeof(metadata) <> 'object' THEN '{}'::jsonb ELSE metadata END) || '{\"pinned\":true}'::jsonb ; unpin -> ... - 'pinned'. The jsonb_typeof guard is load-bearing: || on a non-object operand wraps operands in an ARRAY instead of merging. Verified live: reserved key survived verbatim through pin+unpin.", "environment": "", "language": "sql", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "postgres-jsonb-merge-key-preserve-on-patch", "provider": "openrouter", "solved_at": "2026-09-17T18:17:12.307Z", "version": ""}Done. Solution written to ~/postgres-jsonb-merge-key-preserve-on-patch.md and verified live against PostgreSQL 18 (I initialized a throwaway cluster, ran the full test suite, then stopped it).
Summary of the diagnosis and fix:
metadata = COALESCE($4, metadata) is all-or-nothing replacement, not a merge — any non-NULL partial body discards the whole document, wiping reserved sub-objects like metadata.multi_reference. A SELECT-then-UPDATE fix would introduce a lost-update race.metadata || '{"pinned":true}' works only when metadata is a JSON object; on NULL, JSON null, arrays, strings, or numbers, || concatenates and array-wraps the result (verified: '[]'::jsonb || '{"pinned":true}' → [{"pinned":true}]). Hence the jsonb_typeof(metadata) <> 'object' guard is load-bearing.sql
SET metadata =
(CASE WHEN metadata IS NULL OR jsonb_typeof(metadata) <> 'object'
THEN '{}'::jsonb ELSE metadata END)
|| '{"pinned":true}'::jsonb
-- unpin: ... END) - 'pinned'Verification performed:
1. Reproduced the clobber with the old COALESCE form.
2. Assertions confirming multi_reference survives verbatim through pin + unpin.
3. Non-object inputs (NULL, [], "scalar", null) normalize to {"pinned":true} — no array wrap.
4. Two-session concurrency test: a pin and an unrelated key merge both survive, reserved key intact — proving no read-modify-write race.
The markdown is self-contained with runnable SQL/bash, exact observed outputs, reusable helper functions, and caveats (shallow merge, policy on explicitly patching reserved keys).
# Evidence - Problem class: postgres-jsonb-merge-key-preserve-on-patch - Model: openrouter/deepseek/deepseek-v4.1-flash - Solved: 2026-09-17T18:17:12.306Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "A PATCH endpoint that accepted a whole-document jsonb replace (metadata = COALESCE($4, metadata)) clobbers reserved sub-objects when a client sends only a partial document (e.g. {\"pinned\":true} erasing metadata.multi_reference). Fix idiom without a read-modify-write race, in the SAME UPDATE: pin -> (CASE WHEN metadata IS NULL OR jsonb_typeof(metadata) <> 'object' THEN '{}'::jsonb ELSE metadata END) || '{\"pinned\":true}'::jsonb ; unpin -> ... - 'pinned'. The jsonb_typeof guard is load-bearing: || on a non-object operand wraps operands in an ARRAY instead of merging. Verified live: reserved key survived verbatim through pin+unpin.", "environment": "", "language": "sql", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "postgres-jsonb-merge-key-preserve-on-patch", "provider": "openrouter", "solved_at": "2026-09-17T18:17:12.307Z", "version": ""}