PostgresProvider.connectWithSchema races on concurrent first-time startup and fails with pg_namespace_nspname_index duplicate key

[affandar]

Title: PostgresProvider.connectWithSchema races on concurrent first-time startup and fails with pg_namespace_nspname_index duplicate key

Summary

Concurrent first-time startup against PostgreSQL can fail inside the duroxide-pg provider when multiple workers call PostgresProvider.connectWithSchema(...) at the same time. The losing workers fail with:

duplicate key value violates unique constraint "pg_namespace_nspname_index"

This happens during schema creation/initialization in the PostgreSQL provider. It leaves callers thinking worker startup hung or partially failed.

Environment

• Host: macOS
• Client app: PilotSwarm local TUI embedding 4 workers in one process
• Database: local PostgreSQL
• Provider path: PostgresProvider.connectWithSchema(...)
• Observed on first startup immediately after dropping the duroxide-related schemas

Repro

1. Drop the duroxide/application schemas so the next run starts from a clean database.
2. Start 4 workers concurrently against the same PostgreSQL database and same duroxide schema.
3. Each worker calls PostgresProvider.connectWithSchema(store, schema) during startup.

Pseudo-code:

await Promise.all([
  worker0.start(),
  worker1.start(),
  worker2.start(),
  worker3.start(),
]);

Where each worker.start() calls into:

await PostgresProvider.connectWithSchema(store, duroxideSchema)

Actual result

Usually 1 worker succeeds and the others fail immediately with an error like:

Failed to connect to PostgreSQL: error returned from database:
duplicate key value violates unique constraint "pg_namespace_nspname_index"

From our startup trace:

worker local-rt-3 start failed ms=33 err=Failed to connect to PostgreSQL: error returned from database: duplicate key value violates unique constraint "pg_namespace_nspname_index"
worker local-rt-0 start failed ms=35 err=Failed to connect to PostgreSQL: error returned from database: duplicate key value violates unique constraint "pg_namespace_nspname_index"
worker local-rt-1 start failed ms=34 err=Failed to connect to PostgreSQL: error returned from database: duplicate key value violates unique constraint "pg_namespace_nspname_index"

Meanwhile one worker succeeds and continues normally.

Expected result

Concurrent callers should be able to race safely on first-time schema initialization. The PostgreSQL provider should either:

• make schema creation idempotent under concurrency, or
• catch and treat the duplicate-schema path as success, then continue initialization.

Impact

This showed up as a blank/hung first-run local TUI startup in PilotSwarm because multiple embedded workers were started concurrently. Reverting to sequential worker startup works around the problem, but the bug appears to be in the duroxide PostgreSQL provider itself rather than in the application.

Notes

• This is specifically in the duroxide-pg / PostgreSQL provider path, not an application-level session-state bug.
• Serial worker startup avoids the issue.
• Once the schema already exists, subsequent startups generally succeed.