Improve Retry Logic in sqlcon for CockroachDB Transient Errors (SQLSTATE Classes 08 & 57)

Preflight checklist

• I could not find a solution in the existing issues, docs, nor discussions.
• I agree to follow this project's Code of Conduct.
• I have read and am following this repository's Contribution Guidelines.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Ory Network Project

No response

Describe your problem

Hi Ory team,

Following up on our recent joint discussions and ongoing integration between Ory and CockroachDB, we've observed transient SQL errors during load and topology changes (e.g., rolling upgrades, node restarts). These are typically in SQLSTATE classes 08 (connection exceptions) and 57 (operator intervention), and while infrequent, they can cause request retries to fail if not handled appropriately in application code.

This pattern aligns with the intent of sqlcon.IsError and may serve as a clean enhancement to that function to cover these additional SQLSTATE classes.

Describe your ideal solution

I recommend operation-level retries for transient failures, and we’ve implemented a working example of this pattern in Go:
👉 cockroach-go-with-retry

Workarounds or alternatives

• Extend sqlcon.IsError or create a dedicated helper to catch and classify retry-able CockroachDB-specific SQL errors (notably from SQLSTATE 08xxx and 57P01).

• Ensure this utility is integrated where SQL errors are surfaced, particularly in services that rely on Ory's SQL abstraction for persistence.

Version

v0.0.675

Additional Context

No response

Start in sqlcon/error.go at sqlcon.IsError and compare its current classification with the linked CockroachDB retry example. Trace where SQL errors are surfaced in services using the SQL abstraction, then verify that SQLSTATE classes 08 and 57, including 57P01, are classified consistently for retry handling.