[0.6.2] Guarded UPDATE plus another write in one transaction always aborts at COMMIT with 40000 "retry" (possibly fixed on main by #429) #433

Version / environment

• Version / build: v0.6.2 prebuilt nodedb-0.6.2-linux-x64.tar.gz (sha256 12439d6099ba1bdd9ccdd618a60c77871f1d62905d48c6261bb40823f04c82aa, git 467f061)
• Deployment mode: Origin — single node (local). WSL2 Ubuntu 24.04, kernel 5.10.16, 4 cores, 12 GB RAM, data dir on ext4.
• Client: tokio-postgres 0.7.18 + deadpool-postgres 0.14.2 over TLS + SCRAM-SHA-256.
• Engine: Document (strict)

Found during a go/no-go evaluation of NodeDB as the system of record for a healthcare booking platform (bookings, payments ledger, clinical records).

Summary

On 0.6.2, a transaction that contains a guarded UPDATE … WHERE pk = $1 AND <non-key column> = … together with any other write always aborts at COMMIT. The error is SQLSTATE 40000, "cross-shard transaction aborted … retry". It happens on a single node with no contention. Retrying never succeeds, so the "retry" hint sends clients into an endless loop.

This may be the same family as #424 (part A), which #429 fixed on main. If so, please confirm and consider a 0.6.x patch release, since 0.6.2 is the latest release. I am filing this separately because on 0.6.2 the symptom is 40000 with a retry hint, not XX000.

Steps to reproduce

CREATE COLLECTION bk (id TEXT PRIMARY KEY, state TEXT) WITH (engine='document_strict');
CREATE COLLECTION le (id TEXT PRIMARY KEY, amount_sen BIGINT) WITH (engine='document_strict');
INSERT INTO bk (id, state) VALUES ('b1','pending');

BEGIN;
INSERT INTO le (id, amount_sen) VALUES ('e1', 1);
UPDATE bk SET state='confirmed' WHERE id='b1' AND state='pending';
COMMIT;     -- 40000 cross-shard transaction aborted … retry   (every time)

I ran a 16-cell matrix: {superuser, API user} × {simple, extended protocol} × {another write in the transaction or not} × {guard or not}. COMMIT fails exactly when the guard and another write are both present. The guarded UPDATE on its own commits. The other write plus an unguarded UPDATE … WHERE id='b1' also commits.

Expected behavior

The transaction commits. Under contention it may fail with a retryable error, but a retry must be able to succeed.

Actual behavior

COMMIT fails with 40000 every time, and every retry fails the same way.