drivers(sql): the unbacked-conflict-target refusal is SQLite-only — Postgres and MySQL still answer the raw driver error

Filed out of #8445, which fixed the same condition on the SQLite face only. Unassigned; recording what was and was not measured.

What #8445 landed

SqlDriver.upsert now recognises "the ON CONFLICT target is not backed by a PRIMARY KEY or UNIQUE index" and re-raises it as an ADR-0112 envelope (VALIDATION_ERROR / 400), first sentence for first sentence with #8413's remote refusal (#5240).

Recognition is a narrow match on SQLite's own sentence, which is the only channel SQLite fills — it raises a plain SQLITE_ERROR, the same generic code a syntax error carries, so code cannot discriminate. Measured through knex + better-sqlite3 (the message knex builds is the statement, then -, then SQLite's text):

upsert('plain', { email: 'a@b.com', title: 'x' }, ['email'])
  -> name=SqliteError code=SQLITE_ERROR status=undefined
     msg=insert into `plain` (...) values (...) on conflict (`email`) do update set ...
         - ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

What is left

driver-sql serves three dialects. Postgres and MySQL wording for this condition was not measured — the dev container that implemented #8445 has no Postgres or MySQL server to raise it, and #8445's dispatch was explicit that transcribing the other two dialects' text from memory is not acceptable evidence. So on those two dialects the raw driver error still escapes, mapDataError still falls through to its default branch, and the caller still gets the statement text with no code to branch on — #8445's payload argument, unchanged, on two of three dialects.

Two questions, in order:

Measure. What do Postgres and MySQL actually raise here? Postgres is expected to have both a SQLSTATE channel and prose for it; MySQL has no ON CONFLICT syntax at all — knex compiles onConflict().merge() to ON DUPLICATE KEY UPDATE, which takes no conflict target, so the condition may not arise on that dialect in the same form (and if it does not, what it does instead is worth knowing, because silently merging on a different unique key is its own defect).
Then decide the home. If the recognition turns out to span dialects, the natural place is a named predicate in @objectstack/types beside isUniqueViolationError — its UniqueViolationSignature table (codes / errnos / message substrings) is already the shape this question needs, and every consumer already depends on that package. ⚠️ It must stay a separate predicate: isUniqueViolationError answers the OPPOSITE condition (a unique index exists and the row violated it), and merging them would report a working constraint as a missing one.

If (1) shows the dialects have nothing in common, the per-face recognition #8445 landed is the right end state and this card closes as measured-and-declined.

Related: #8445 (the SQLite half, and where the current recognition lives), #8413 (the remote-face twin), #6543 / #6250 (the shared unique-violation predicate this would sit beside), #1116 / #1077 (the envelope pass this continues).

Generated by Claude Code

Triage: lands in packages/drivers/driver-sql (mapDataError / upsert), possibly graduating a shared predicate into @objectstack/types → domain:drivers, pm:queue, type Bug.

Rationale: on Postgres and MySQL the raw driver error (statement text included) still escapes where the contract requires an ADR-0112 envelope — declared ≠ enforced on two of three dialects, with a named landing site and the SQLite twin (#8445) as the template. The maintainer's 2026-08-05 drivers investment freeze covers the driver-memory/driver-mongodb family only; driver-sql is explicitly unaffected.

Dispatch note: step 1 (measure what Postgres/MySQL actually raise) requires live servers the dev containers may not have — the dispatching seat should confirm the measurement channel before dispatch (CI service containers or a maintainer-side reading pasted onto this card), per the "zero-hit needs a counter-probe" discipline. The card already anticipates the MySQL branch may dissolve into a different defect (silent merge on a different unique key); that outcome is a valid measured-and-declined close for the MySQL half, not a failure. The isUniqueViolationError adjacency warning in the body (opposite condition, must stay a separate predicate) should be quoted in the dispatch brief.

Size/model suggestion: M, mode:subagent, model: opus.

Triage seat Routine, session session_01KKqo1q7JddqjVbabF1ujMj.

Generated by Claude Code

Environment gate for question (1), measured rather than assumed. This card was held on "the dev container has no Postgres or MySQL to raise the condition" — inherited from #8445. Half of that is no longer true.

Postgres — measurable, no docker needed. PG16 server binaries are present:

$ ls /usr/lib/postgresql/16/bin/ | grep -E '^(initdb|pg_ctl)$'
initdb
pg_ctl
$ which psql
/usr/bin/psql

This is the same route #8269's dev took (system PG16 binaries, started locally, no container runtime). So Postgres wording for the unbacked-conflict-target condition can be raised for real and transcribed from the actual error object — which is the standard of evidence #8445's dispatch demanded.

MySQL — not measurable here, and docker is not an escape hatch. No server binaries of any kind (mysqld, mariadbd both absent; only the php8.4-mysql client library is installed), and the daemon is unreachable:

$ docker info   # binary exists at /usr/bin/docker
DOCKER DAEMON UNREACHABLE (exit 1)

Why this does not block the card. The repo already has the convention for a partially-measurable dialect matrix — packages/drivers/driver-sql/src/live-dialect-matrix.testkit.ts, which reads OS_TEST_POSTGRES_URL / OS_TEST_MYSQL_URL (12 sites each) and reports a live cell as un-run when its env var is absent rather than passing vacuously. That is the honest home for the MySQL half: declared, addressable by anyone with a server, and visibly not-measured — instead of wording transcribed from memory, which #8445's dispatch already ruled out as evidence and which Prime Directive #10 would make a declaration nothing enforces.

So the scope this card can actually carry today:

Raise the condition on a real Postgres 16 and record code / SQLSTATE / message verbatim. Postgres is expected to answer 42P10 here — ⚠️ expected, not measured; the measurement decides, and if it disagrees the measurement wins.
Answer the MySQL sub-question of (1) that needs no server: knex compiles onConflict().merge() to ON DUPLICATE KEY UPDATE, which takes no conflict target. Whether the condition can arise at all on that dialect is readable from the compiled SQL — and if it cannot, the issue's own note stands that silently merging on a different unique key is its own defect worth a separate card. The live MySQL cell stays declared-unmeasured either way.
Decide the home only after (1) — per the card's ordering, and keeping any shared predicate separate from isUniqueViolationError, which answers the opposite condition.

Not dispatching yet: #8568 pins driver-sql's refusal wording against driver-turso's across the two faces, and is in flight. Serialising behind it so this card's dev inherits that pin rather than racing it — the pin is the thing that will keep a dialect generalisation from drifting the two faces apart.

Generated by Claude Code

Claim: drivers seat, session session_01VoxQqG5FiUHZKCST7KDoZC, branch claude/issue-8567-unbacked-conflict-target-dialects.

File surface: packages/drivers/driver-sql/src/** — the recognition at sql-driver.ts:1016 and its one call site at :5075, plus whatever test lands under driver-sql/src/. Possibly packages/types/** if step (1) shows the dialects share a signature; ⛔ not packages/spec (that is the spec seat's lane regardless of who needs it), and ⛔ not docs/adr/** or .claude/skills/**.

Serial constraint: gated on #8579 (#8568's cross-face parity pin) reaching main. It is ready, all 32 checks green, and sitting in the merge queue now. Dispatch follows the merge, not this comment — a dialect generalisation is precisely the change that pin exists to catch, and the dev should inherit it rather than race it.

Premise verified on origin/main myself, not taken from the card. At sql-driver.ts:1016:

function isUnbackedConflictTargetError(error: unknown): boolean {
  const message = error instanceof Error ? error.message : String(error ?? '');
  return /ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint/i.test(message);
}

One SQLite sentence, one call site. Postgres prose for this condition is not that sentence, so the regex cannot match and the raw error still escapes — the card's premise holds exactly as filed. The function's own docstring already names this card as the follow-up and states the gate as "measuring the other two dialects' text rather than transcribing it from memory", which is the standard the dispatch will hold the dev to.

⚠️ One measurement note for the record: I first probed with grep "const isUnbackedConflictTargetError" and got zero hits — it is declared function, not const. A zero-hit reported as a finding would have said the predicate does not exist. Counter-probed before concluding, per the standing rule; the eighth instance of that trap today.

Environment gate is settled above in 5287338308 — Postgres measurable from system PG16 binaries, MySQL not measurable here and staying declared-unmeasured through the existing live-dialect-matrix convention rather than transcribed from memory.

Model: opus, matching triage's suggestion. Not the claude-fable-5 tier: enveloping an error that already threw leaves the accept/reject set identical — the same upserts fail, they fail legibly — and any predicate graduating into @objectstack/types widens surface outside the spec semantic lane. That is implementation judgment, not a contract change.

Generated by Claude Code

Serial gate satisfied — #8579 merged to main as a3b1d264f, so this card's dev inherits #8568's cross-face parity pin rather than racing it. Dispatched to an os-dev subagent, model: opus, mode:subagent, branch claude/issue-8567-unbacked-conflict-target-dialects.

The brief holds the dev to the standard this card was filed on: question (1) is the deliverable, even if (2) turns out to be "change nothing." Specifically —

Postgres must be raised on a real cluster started from the container's PG16 binaries and observed through knex + pg, the way the driver reaches it, not through psql — code, severity, SQLSTATE, constraint and the full message transcribed from the actual error object.
The 42P10 expectation is stated in the brief as falsifiable, not as an assumption: if the measurement disagrees, the measurement wins and the dev says so. The dev is also asked whether 42P10 is reachable by any other condition, because if it is, a code-only predicate over-matches and the message has to narrow it.
MySQL's server-free half is answerable and in scope — read the compiled SQL, since onConflict().merge() becomes ON DUPLICATE KEY UPDATE with no conflict target. Its live cell stays visibly un-run through live-dialect-matrix.testkit.ts; a vacuous green there would be the same declared-≠-enforced defect this card is about.
The isUniqueViolationError adjacency warning is quoted verbatim, flagged as the single most expensive mistake available here — it answers the opposite condition, and merging them would report a working constraint as a missing one.
Measured-and-declined is named as a successful outcome. If the three dialects share nothing, the SQLite-first recognition already landed is the right end state and the measurements are the product. The brief explicitly tells the dev not to manufacture a change to justify the dispatch — that pressure is real when a card has been open a while, and it is how a card like this quietly turns into an over-matching predicate.

Generated by Claude Code

{
  "issue": 8567,
  "status": "done",
  "branch": "claude/issue-8567-unbacked-conflict-target-dialects",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/8591",
  "premise_still_valid": true,
  "summary": "MEASURED FIRST, then decided. Postgres raised for real — throwaway PG 16.13 from system PG16 binaries (initdb + pg_ctl, no docker), driven through knex + pg exactly as SqlDriver.upsert does. VERBATIM off the thrown error object: name='error' (DatabaseError), code='42P10' (string), severity='ERROR', routine='infer_arbiter_indexes', constraint=undefined, detail=undefined, status=undefined, message='insert into \"plain\" (\"email\", \"id\", \"title\") values ($1, $2, $3) on conflict (\"email\") do update set \"title\" = excluded.\"title\" - there is no unique or exclusion constraint matching the ON CONFLICT specification'. The card's stated expectation HELD, so nothing was overturned. Critically, the card's other question answered YES: 42P10 IS reachable by other conditions — 'select id from plain order by 7' and 'group by 9' both return code=42P10 ('ORDER BY/GROUP BY position N is not in select list'; 42P10 is invalid_column_reference), so a code-only predicate over-matches and the message must carry the verdict; the code channel is deliberately unread on BOTH dialects (SQLite's is the generic SQLITE_ERROR). Also measured: a PARTIAL unique index raises the identical 42P10 + sentence (correct — it cannot be an arbiter). MySQL answered without a server, from the compiled SQL: knex compiles onConflict(['email']).merge(['title']) on mysql2 to 'insert into `plain` (`email`, `id`, `title`) values (?, ?, ?) on duplicate key update `title` = values(`title`)' — the conflict target is dropped before the statement leaves the process, so the condition CANNOT arise there; the live MySQL cell is declared un-run via live-dialect-matrix.testkit.ts rather than skipped-and-forgotten. Decision (2): the dialects share a channel and an alternation shape, so recognition graduated to isUnbackedConflictTargetError in @objectstack/types beside — and deliberately SEPARATE from — isUniqueViolationError (no new dependency edge; driver-turso's private copy stays, since that package has no @objectstack/types dep and its face is libsql/SQLite forever). One clause of the refusal wording moved on BOTH faces in the same commit: 'and SQLite refuses the statement' -> 'and the database refuses the statement', because naming SQLite to a Postgres operator points at the wrong engine; #5240's one-wording rule holds and #8568's parity pin proved the two moved together. SURPRISE WORTH THE PM'S ATTENTION: the disjointness pin went red on its first run and the measurement won — isUniqueViolationError ALREADY claims SQLite's unbacked-target error (its 'unique constraint' limb matches the missing-index sentence ending '...any PRIMARY KEY or UNIQUE constraint'; Postgres escapes only on word order). Filed as #8590, NOT fixed here (it moves verdicts in six consuming packages). It is latent today because upsert is the only site compiling a caller-supplied conflict target and recognition runs first in that catch — but a comment in sql-driver.ts asserted the opposite as fact and reasoned from it, so that comment is corrected in place and the ordering is now documented as load-bearing.",
  "tests": "All run in-worktree under flock /tmp/os-heavy-verify.lock, live PG cell provisioned AND non-vacuous (server timezone=Asia/Shanghai, process TZ=America/New_York, so assertThreeWayZoneSkew passes rather than the matrix proving nothing). `pnpm --filter @objectstack/types test` -> 'Test Files 11 passed (11) / Tests 280 passed (280)'. `pnpm --filter @objectstack/driver-sql test` with OS_TEST_POSTGRES_URL -> 'Test Files 96 passed | 1 skipped (97) / Tests 1703 passed | 32 skipped (1735)' (the 1 skipped file is the MySQL cell, reported not omitted). `pnpm --filter @objectstack/driver-turso test` -> 'Test Files 37 passed (37) / Tests 986 passed (986)'. typecheck clean on all three. REVERSE VERIFICATION, direction predicted before each leg: (a) remove the Postgres limb from UNBACKED_CONFLICT_TARGET -> predicted the PG cell reddens and every SQLite pin holds; observed '3 failed | 11 passed | 1 skipped' — envelope/wording/leak pins red on the live-postgres cell ONLY, all 6 SQLite pins green, and both positive controls green on BOTH cells, which is what proves the limbs are independent. (b) reword driver-turso's face alone back to 'SQLite' -> predicted #8568's parity pin reddens; observed '1 failed | 9 passed', the failure being expect(localErr.message).toBe(remoteErr.message) at line 352. Both legs restored via `git checkout HEAD -- <path>` from the commit (fix committed BEFORE the reverse legs; no git stash). #8568's stale-dist trap respected: driver-turso was only ever run against a freshly rebuilt @objectstack/driver-sql dist, verified by grepping the built bundle for the new wording. Gates re-derived from the actual changed paths with scripts/pm/dispatch-gates.mjs and all green: check:changeset-gate-self-tests, check:objectui-changeset, check:test-source-alias, check:type-source-resolution, check:query-options-erasure, check:type-check-coverage, check:error-code-casing, check:nul-bytes, check-adr-0087-registration, check-changeset-no-major, check-empty-changeset, plus check:partof-closing-keyword run against the real stored PR body.",
  "open_questions": [],
  "out_of_scope_findings": [
    "filed as #8590: `isUniqueViolationError` claims SQLite's UNBACKED-conflict-target error — a superstring collision on the word pair 'unique constraint' inside the sentence '...any PRIMARY KEY or UNIQUE constraint', i.e. the exact inversion the predicate's own docstring warns against. Measured on real driver errors from both dialects (sqlite true/WRONG, pg false/correct). Unlabeled for PM triage: it is a wrong verdict in a shared predicate consumed by six packages, but currently latent because both driver faces envelope the condition before any consumer sees it. Same bug class as #6615, which was fixed centrally for a different phrase pair.",
    "NOT filed, deliberately — MySQL's behaviour instead of the refusal (merging on whichever unique key the row collides with, since the conflict target is compiled away). The card invited a separate `finding` card for it, but filing one would mean describing behaviour nobody has observed: no mysqld/mariadbd exists in this container and the docker daemon is unreachable, so the claim would rest on the compiled SQL alone. That is the transcribed-from-memory evidence #8445's dispatch ruled out. The gap is instead recorded as a REPORTED un-run live cell in sql-driver-upsert-conflict-target-dialects.test.ts, addressable by anyone with a server. Flagging it here so the PM can file it against a runner that has MySQL if they prefer."
  ]
}

Generated by Claude Code

fix(driver-sql): envelope the unbacked-conflict-target upsert refusal (…

fix(drivers): recognise the unbacked conflict target on Postgres too (o…

fix(types): require a violation phrasing in isUniqueViolationError's …

Navigation Menu

Repository navigation

drivers(sql): the unbacked-conflict-target refusal is SQLite-only — Postgres and MySQL still answer the raw driver error #8567

What #8445 landed

What is left

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

drivers(sql): the unbacked-conflict-target refusal is SQLite-only — Postgres and MySQL still answer the raw driver error #8567

Description

What #8445 landed

What is left

Activity

hotlong commented on Aug 13, 2026

os-zhuang commented on Aug 13, 2026

os-zhuang commented on Aug 13, 2026

os-zhuang commented on Aug 13, 2026

os-zhuang commented on Aug 14, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions