feat: automatic transaction retries for postgres

alnr · ory-bot · commit 80dcbac48d1c · 2026-02-03T23:22:03.000Z
GitOrigin-RevId: e7d567988621937fb25e9f41af7892b6f0aad65d
diff --git a/oryx/popx/transaction.go b/oryx/popx/transaction.go
@@ -6,8 +6,11 @@ package popx
 import (
 	"context"
 	"runtime"
+	"strings"
 
 	"github.com/cockroachdb/cockroach-go/v2/crdb"
+	"github.com/jackc/pgconn"
+	pgxconn "github.com/jackc/pgx/v5/pgconn"
 	"github.com/jmoiron/sqlx"
 	"github.com/pkg/errors"
 	"github.com/prometheus/client_golang/prometheus"
@@ -53,6 +56,26 @@ func Transaction(ctx context.Context, connection *pop.Connection, callback func(
 			}))
 		})
 	}
+	if strings.HasPrefix(connection.Dialect.Name(), "postgres") {
+		var err error
+		for attempt := range 10 {
+			_ = attempt
+			err = connection.WithContext(ctx).Transaction(func(tx *pop.Connection) error {
+				return callback(WithTransaction(ctx, tx), tx)
+			})
+			if err == nil {
+				return nil
+			}
+			if e := new(pgconn.PgError); errors.As(err, &e) && e.Code == "40001" {
+				continue
+			}
+			if e := new(pgxconn.PgError); errors.As(err, &e) && e.Code == "40001" {
+				continue
+			}
+			return err
+		}
+		return err
+	}
 
 	return errors.WithStack(connection.WithContext(ctx).Transaction(func(tx *pop.Connection) error {
 		return errors.WithStack(callback(WithTransaction(ctx, tx), tx))
diff --git a/oryx/sqlcon/error.go b/oryx/sqlcon/error.go
@@ -7,12 +7,12 @@ import (
 	"database/sql"
 	"net/http"
 
-	"google.golang.org/grpc/codes"
-
 	"github.com/go-sql-driver/mysql"
 	"github.com/jackc/pgconn"
+	pgxconn "github.com/jackc/pgx/v5/pgconn"
 	"github.com/lib/pq"
 	"github.com/pkg/errors"
+	"google.golang.org/grpc/codes"
 
 	"github.com/ory/herodot"
 )
@@ -87,6 +87,8 @@ func HandleError(err error) error {
 		return errors.WithStack(handlePostgres(err, string(e.Code)))
 	} else if e := new(pgconn.PgError); errors.As(err, &e) {
 		return errors.WithStack(handlePostgres(err, e.Code))
+	} else if e := new(pgxconn.PgError); errors.As(err, &e) {
+		return errors.WithStack(handlePostgres(err, e.Code))
 	} else if e := new(mysql.MySQLError); errors.As(err, &e) {
 		switch e.Number {
 		case 1062: // https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_dup_entry
