encore run fails with 'revoke public: deadlock detected (SQLSTATE 40P01)' when initializing multiple databases in parallel

[nickpokhilko]

Bug description

When encore run brings up a local cluster that contains many databases, the role-setup step occasionally fails with a Postgres deadlock:

✔ Creating PostgreSQL database cluster... Done!
❌ Running database migrations... Failed: ensure db roles <db>: revoke public: ERROR: deadlock detected (SQLSTATE 40P01)
ensure db roles <db>: revoke public: ERROR: deadlock detected (SQLSTATE 40P01)

The failure is intermittent (race condition) and reproduces more often on machines with many local databases / namespaces, e.g. after switching between feature branches that each spawn their own cluster.

Root cause

In cli/daemon/sqldb/db.go, ensureRoles runs:

_, err = adm.ExecContext(ctx, "REVOKE ALL ON DATABASE "+safeDBName+" FROM public")
if err != nil {
    return fmt.Errorf("revoke public: %v", err)
}

ensureRoles is invoked concurrently for every database in the cluster. All of these REVOKE ... FROM public statements contend for locks on the shared public role / system catalogs and can deadlock.

The GRANT statements that follow already have a 5-attempt retry loop with the explicit comment:

// We've observed race conditions in Postgres to grant access. Retry a few times.

…but the REVOKE above it has no retry, so a single 40P01 aborts the whole cluster startup.

Suggested fix

Wrap the REVOKE ALL ON DATABASE ... FROM public call in the same retry loop used for the GRANT statements (5 attempts × 250 ms backoff is enough — deadlock resolution releases the loser immediately).

Happy to send a PR if it's helpful.

Environment

• Encore CLI: v1.57.0
• Postgres image: `encoredotdev/postgres:18` (also reproduced on `:15`)
• OS: macOS 25.4.0 (darwin/arm64)
• Cluster: 4 local databases across multiple namespaces

Reproduction

1. Have several Encore apps / namespaces with local clusters running (4+ DBs total).
2. Stop the daemon and remove the sqldb containers (`docker rm -f $(docker ps -aq --filter "name=sqldb-")`), keeping the volumes.
3. Run `encore run`. After 1–3 attempts the deadlock surfaces on `revoke public`.

[ekerfelt]

Thanks for reporting. #2434 was released in v1.57.1. Update your encore instance and see if it resolved the issue

[WillSewell]

Hitting this on v1.57.1 (with PR #2434's retry already applied), reproduces 100% across 3+ runs. App has 33 service databases initializing in parallel.

The failing statement here is grant admin role encore-admin rather than revoke public, but it's the same ensureRoles code path and I suspect the same root cause.

Daemon log cascade

From ~/Library/Caches/encore/daemon.log:

10:58:54.609  db=REDACTED  ensure db roles REDACTED: grant admin role encore-admin: ERROR: deadlock detected (SQLSTATE 40P01)
10:58:54.866  db=REDACTED    grant admin role encore-admin: context canceled
10:58:55.118  db=REDACTED             grant admin role encore-admin: driver: bad connection
10:58:55.124  db=REDACTED        grant admin role encore-admin: driver: bad connection
10:58:55.361  db=REDACTED             grant admin role encore-admin: sql: connection is already closed
10:58:55.365  db=REDACTED            grant admin role encore-admin: sql: connection is already closed
10:58:55.864  db=REDACTED            grant admin role encore-admin: sql: connection is already closed
10:58:55.865  db=REDACTED              grant admin role encore-admin: sql: connection is already closed
10:58:55.869  db=REDACTED        grant admin role encore-admin: sql: connection is already closed
10:58:55.876  db=REDACTED          grant admin role encore-admin: sql: connection is already closed
10:58:55.876  db=REDACTED          grant admin role encore-admin: sql: connection is already closed

It exhausts all 5 retries on the deadlock. The other databases then collapse via context cancellation / closed superuser connection rather than getting a chance to retry their own deadlocks, so the whole cluster setup aborts in ~1.3s.

Why the current retry isn't sufficient (at least this is what claude thinks...)

The RoleAdmin block runs five GRANTs touching shared role objects (encore_services, encore-migrator, pg_read_all_data, pg_write_all_data) plus a REASSIGN OWNED, all executed concurrently across every service DB. With this many sessions contending for locks on the same pg_authid rows in different orders, deadlocks aren't a race condition any more, they're the steady state.

The fixed 250ms backoff with no jitter makes things worse: every losing session sleeps the same amount and retries in lockstep, so the same deadlock pattern recurs on every attempt until the budget is gone.

Environment

• Encore CLI: v1.57.1
• Postgres image: encoredotdev/postgres:18 (default since v1.57.0)
• OS: macOS 25.4.0 (darwin/arm64)
• Cluster: 33 service databases in a single namespace

[ekerfelt]

Yes, it seems like the REASSIGNED OWNED BY statement exacerbated the existing role grant deadlock. We're changing the retry strategy to a proper mutex on the cluster instead: #2442

[ekerfelt]

Fix is out in v1.57.3