[performance] retry db queries on busy errors (#2025)

* catch SQLITE_BUSY errors, wrap bun.DB to use our own busy retrier, remove unnecessary db.Error type

Signed-off-by: kim <grufwub@gmail.com>

* remove dead code

Signed-off-by: kim <grufwub@gmail.com>

* remove more dead code, add missing error arguments

Signed-off-by: kim <grufwub@gmail.com>

* update sqlite to use maxOpenConns()

Signed-off-by: kim <grufwub@gmail.com>

* add uncommitted changes

Signed-off-by: kim <grufwub@gmail.com>

* use direct calls-through for the ConnIface to make sure we don't double query hook

Signed-off-by: kim <grufwub@gmail.com>

* expose underlying bun.DB better

Signed-off-by: kim <grufwub@gmail.com>

* retry on the correct busy error

Signed-off-by: kim <grufwub@gmail.com>

* use longer possible maxRetries for db retry-backoff

Signed-off-by: kim <grufwub@gmail.com>

* remove the note regarding max-open-conns only applying to postgres

Signed-off-by: kim <grufwub@gmail.com>

* improved code commenting

Signed-off-by: kim <grufwub@gmail.com>

* remove unnecessary infof call (just use info)

Signed-off-by: kim <grufwub@gmail.com>

* rename DBConn to WrappedDB to better follow sql package name conventions

Signed-off-by: kim <grufwub@gmail.com>

* update test error string checks

Signed-off-by: kim <grufwub@gmail.com>

* shush linter

Signed-off-by: kim <grufwub@gmail.com>

* update backoff logic to be more transparent

Signed-off-by: kim <grufwub@gmail.com>

---------

Signed-off-by: kim <grufwub@gmail.com>

1 files changed, 17 insertions, 17 deletions

diff --git a/internal/db/bundb/report.go b/internal/db/bundb/report.go
index ee8aa1cb3..3a1e18789 100644
--- a/internal/db/bundb/report.go
+++ b/internal/db/bundb/report.go
@@ -32,15 +32,15 @@ import (
 )
 
 type reportDB struct {
-	conn  *DBConn
+	db    *WrappedDB
 	state *state.State
 }
 
 func (r *reportDB) newReportQ(report interface{}) *bun.SelectQuery {
-	return r.conn.NewSelect().Model(report)
+	return r.db.NewSelect().Model(report)
 }
 
-func (r *reportDB) GetReportByID(ctx context.Context, id string) (*gtsmodel.Report, db.Error) {
+func (r *reportDB) GetReportByID(ctx context.Context, id string) (*gtsmodel.Report, error) {
 	return r.getReport(
 		ctx,
 		"ID",
@@ -51,10 +51,10 @@ func (r *reportDB) GetReportByID(ctx context.Context, id string) (*gtsmodel.Repo
 	)
 }
 
-func (r *reportDB) GetReports(ctx context.Context, resolved *bool, accountID string, targetAccountID string, maxID string, sinceID string, minID string, limit int) ([]*gtsmodel.Report, db.Error) {
+func (r *reportDB) GetReports(ctx context.Context, resolved *bool, accountID string, targetAccountID string, maxID string, sinceID string, minID string, limit int) ([]*gtsmodel.Report, error) {
 	reportIDs := []string{}
 
-	q := r.conn.
+	q := r.db.
 		NewSelect().
 		TableExpr("? AS ?", bun.Ident("reports"), bun.Ident("report")).
 		Column("report.id").
@@ -94,7 +94,7 @@ func (r *reportDB) GetReports(ctx context.Context, resolved *bool, accountID str
 	}
 
 	if err := q.Scan(ctx, &reportIDs); err != nil {
-		return nil, r.conn.ProcessError(err)
+		return nil, r.db.ProcessError(err)
 	}
 
 	// Catch case of no reports early
@@ -118,14 +118,14 @@ func (r *reportDB) GetReports(ctx context.Context, resolved *bool, accountID str
 	return reports, nil
 }
 
-func (r *reportDB) getReport(ctx context.Context, lookup string, dbQuery func(*gtsmodel.Report) error, keyParts ...any) (*gtsmodel.Report, db.Error) {
+func (r *reportDB) getReport(ctx context.Context, lookup string, dbQuery func(*gtsmodel.Report) error, keyParts ...any) (*gtsmodel.Report, error) {
 	// Fetch report from database cache with loader callback
 	report, err := r.state.Caches.GTS.Report().Load(lookup, func() (*gtsmodel.Report, error) {
 		var report gtsmodel.Report
 
 		// Not cached! Perform database query
 		if err := dbQuery(&report); err != nil {
-			return nil, r.conn.ProcessError(err)
+			return nil, r.db.ProcessError(err)
 		}
 
 		return &report, nil
@@ -166,34 +166,34 @@ func (r *reportDB) getReport(ctx context.Context, lookup string, dbQuery func(*g
 	return report, nil
 }
 
-func (r *reportDB) PutReport(ctx context.Context, report *gtsmodel.Report) db.Error {
+func (r *reportDB) PutReport(ctx context.Context, report *gtsmodel.Report) error {
 	return r.state.Caches.GTS.Report().Store(report, func() error {
-		_, err := r.conn.NewInsert().Model(report).Exec(ctx)
-		return r.conn.ProcessError(err)
+		_, err := r.db.NewInsert().Model(report).Exec(ctx)
+		return r.db.ProcessError(err)
 	})
 }
 
-func (r *reportDB) UpdateReport(ctx context.Context, report *gtsmodel.Report, columns ...string) (*gtsmodel.Report, db.Error) {
+func (r *reportDB) UpdateReport(ctx context.Context, report *gtsmodel.Report, columns ...string) (*gtsmodel.Report, error) {
 	// Update the report's last-updated
 	report.UpdatedAt = time.Now()
 	if len(columns) != 0 {
 		columns = append(columns, "updated_at")
 	}
 
-	if _, err := r.conn.
+	if _, err := r.db.
 		NewUpdate().
 		Model(report).
 		Where("? = ?", bun.Ident("report.id"), report.ID).
 		Column(columns...).
 		Exec(ctx); err != nil {
-		return nil, r.conn.ProcessError(err)
+		return nil, r.db.ProcessError(err)
 	}
 
 	r.state.Caches.GTS.Report().Invalidate("ID", report.ID)
 	return report, nil
 }
 
-func (r *reportDB) DeleteReportByID(ctx context.Context, id string) db.Error {
+func (r *reportDB) DeleteReportByID(ctx context.Context, id string) error {
 	defer r.state.Caches.GTS.Report().Invalidate("ID", id)
 
 	// Load status into cache before attempting a delete,
@@ -209,9 +209,9 @@ func (r *reportDB) DeleteReportByID(ctx context.Context, id string) db.Error {
 	}
 
 	// Finally delete report from DB.
-	_, err = r.conn.NewDelete().
+	_, err = r.db.NewDelete().
 		TableExpr("? AS ?", bun.Ident("reports"), bun.Ident("report")).
 		Where("? = ?", bun.Ident("report.id"), id).
 		Exec(ctx)
-	return r.conn.ProcessError(err)
+	return r.db.ProcessError(err)
 }