Migration DDL still races under concurrent EnsureStorageExists: XX000 "tuple concurrently updated" (follow-up to #282)

[jeremydmiller]

Summary

PostgresqlMigrator is hardened against the CREATE SCHEMA concurrency race (#282 — catches 42P06 duplicate_schema and 23505 unique_violation in the WriteSql DO block), but the rest of the migration DDL is still not concurrent-safe. When two sessions lazily ensure storage at the same time against the same database, the broader idempotent DDL (CREATE OR REPLACE FUNCTION mt_immutable_*, CREATE TABLE IF NOT EXISTS, etc.) can fail with:

Npgsql.PostgresException : XX000: tuple concurrently updated

This SQLSTATE is not caught by #282 (which only wraps the CREATE SCHEMA statement), so it propagates and breaks the operation.

Background / prior fix

#282 ("Make CREATE SCHEMA migration DDL concurrent-safe against pg_namespace race", commit 6b6d296) wrapped the schema create in a sub-block:

BEGIN
  EXECUTE 'CREATE SCHEMA IF NOT EXISTS ...';
EXCEPTION
  WHEN duplicate_schema THEN NULL;   -- 42P06
  WHEN unique_violation THEN NULL;   -- 23505
END;

That covers the pg_namespace insert race only. The residual failure happens later, on the catalog updates emitted by migration.WriteAllUpdates(...) and executed one statement at a time in executeDelta (src/Weasel.Postgresql/PostgresqlMigrator.cs, WriteSql ~L95, executeDelta ~L115).

XX000: tuple concurrently updated is PostgreSQL's generic "two transactions updated the same system-catalog row concurrently" error. It is thrown by idempotent catalog DDL such as CREATE OR REPLACE FUNCTION when two backends update the same pg_proc row simultaneously. It is transient — a retry succeeds because the row is no longer being updated concurrently.

How it reproduces downstream

Seen in Marten CI as a flaky failure of a conjoined (multi-tenant, multiple databases on one cluster) test, query_before_saving. A conjoined multi-tenant store opens several connections and runs EnsureStorageExistsAsync concurrently; those concurrent lazy migrations race on the shared catalog DDL and one backend gets XX000.

• Marten CI evidence: run 26342664380 (marten PR Opt-in System.Text.Json source-generation context (#4540 item 2) marten#4549).
• Downstream tracking issue: Flaky CI tests: feature_flag_positive + conjoined-tenancy query_before_saving (intermittent, unrelated failures) marten#4552. That test will stabilize once Marten consumes a Weasel build with this fixed.

Proposed remediation

The migration DDL Weasel emits is idempotent (IF NOT EXISTS, CREATE OR REPLACE), so a transient catalog-concurrency error is safe to retry. Options (not mutually exclusive):

1. Bounded retry in executeDelta on the known-transient concurrency SQLSTATEs, with a small backoff:
   • XX000 tuple concurrently updated (match on message — XX000 is internal_error, generic, so guard on the message text or e.SqlState == "XX000" && message contains "tuple concurrently updated")
   • 40001 serialization_failure
   • 40P01 deadlock_detected
2. Widen the DO-block guard the way Make CREATE SCHEMA migration DDL concurrent-safe against pg_namespace race #282 did, but the broader DDL isn't all wrapped in a single catchable block, so a retry around statement execution is the more general fix.

A retry around cmd.ExecuteNonQueryAsync in executeDelta is the cleanest spot since each statement is already executed individually there.

Notes

• XX000 is a catch-all SQLSTATE; the retry condition should additionally match the message (tuple concurrently updated) so we don't blanket-retry unrelated internal_errors.
• This is a follow-up to Make CREATE SCHEMA migration DDL concurrent-safe against pg_namespace race #282, not a duplicate — different SQLSTATE, different DDL.