SQLite: an allocation failure during payload apply can leave its transaction open #74

Summary

When an allocation fails inside cloudsync_payload_apply() called as a SQL function on SQLite, the apply can return SQLITE_NOMEM with the transaction it opened itself still open. The connection is then no longer in autocommit mode until the host rolls back.

PR #65 fixes the same symptom for commit-time failures (a deferred foreign key violated at commit, SQLITE_BUSY on commit): apply now rolls back a transaction it started from autocommit mode. That fix cannot cover allocation failures, for the reason below.

Evidence

test/stress/payload_oom.c (added in #65) fails each allocation of a SQL-function apply in turn. After each failure it checks the transaction state, rolls back explicitly where needed, then retries and checks memory. It deliberately exits nonzero while any attempt leaves a transaction open, so it is not part of make unittest.

Allocation positions depend on the build configuration. No sanitizer diagnostics were reported, and no data or metadata is corrupted: after an explicit ROLLBACK, redelivery applies the payload.

Reproduction steps are in docs/internal/apply-transaction-cleanup.md.

Why the extension cannot clean up itself

After an engine-level allocation failure, SQLite marks the connection as having failed a malloc (or as interrupted). While the outer SQL statement that called cloudsync_payload_apply() is still executing, SQLite rejects the cleanup ROLLBACK issued from inside the function. The public SQLite API gives the extension no way to clear that state from within the call. Working around it through private SQLite state or by replacing application callbacks would create compatibility risks.

Possible directions

1. Host-side recovery boundary. Recovery happens after the failing statement is reset or finalized, where ROLLBACK is allowed. This could be documented guidance, or handled by the language bindings and the network sync entry points where they own the call.
2. Change the SQL apply execution model so the transaction boundary is not owned from inside a running SQL function.

Interim guidance for hosts

After cloudsync_payload_apply() (or a network sync call) fails with SQLITE_NOMEM:

1. Reset or finalize the failed statement.
2. Check sqlite3_get_autocommit().
3. If the host began the operation in autocommit mode and the connection is still inside a transaction, run ROLLBACK before reusing the connection.

If the host owns the transaction, it should follow its own transaction policy rather than rolling back unrelated work blindly.

PostgreSQL is not affected: apply always runs inside the caller's transaction there and never opens or commits one itself.