Materialize write only transactions must hit one table or commit fails

Not specified by the original author.

TL;DR: In Materialize every INSERT inside a single transaction block must hit the same table as the first INSERT, or COMMIT dies with 'internal error, wrong set of locks acquired' and rolls back everything. Keep write-only transactions to one table. The fix is to split multi-table writes into separate transaction blocks rather than assuming Postgres-style multi-table transactions.

Materialize write only transactions must hit one table or commit fails

Keep write-only transactions to one table. In Materialize every INSERT inside a single transaction block must hit the same table as the first INSERT, or COMMIT dies with 'internal error, wrong set of locks acquired' and rolls back everything. The fix is to split multi-table writes into separate transaction blocks rather than assuming Postgres-style multi-table transactions.

Context: Materialize docs BEGIN (materializeinc/agent-skills .agents/skills/materialize-docs/sql/begin/index.md): In Materialize, a write-only transaction may contain only INSERT statements, and every INSERT must target the same table. If a later statement in the block writes to a different table than the first, COMMIT fails with 'internal error, wrong set of locks acquired' and rolls the whole transaction back.

When to use

You hit exactly this: Materialize write only transactions must hit one table or commit fails.

When not to use

A different error, or the same symptom in a different tool. This page only covers the failure above.

Compatibility

the tool named in the title.

Materialize docs BEGIN (materializeinc/agent-skills .agents/skills/materialize-docs/sql/begin/index.md): In Materialize, a write-only transaction may contain only INSERT statements, and every INSERT must target the same table. If a later statement in the block writes to a different table than the first, COMMIT fails with 'internal error, wrong set of locks acquired' and rolls the whole transaction back. Resolved: Keep write-only transactions to one table. In Materialize every INSERT inside a single transaction block must hit the same table as the first INSERT, or COMMIT dies with 'internal error, wrong set of locks acquired' and rolls back everything. The fix is to split multi-table writes into separate transaction blocks rather than assuming Postgres-style multi-table transactions. Source: https://github.com/materializeinc/agent-skills/blob/HEAD/.agents/skills/materialize-docs/sql/begin/index.md