What causes "failed to commit empty sample instance cleanup" in bytebase/bytebase?

Confirm database connectivity; a connection-level error here means the pool served a dead connection — enable connection lifetime/health checks (ConnMaxLifetime, ping before use).

bytebase/bytebase · warning

failed to commit empty sample instance cleanup

Error message

failed to commit empty sample instance cleanup

What it means

When the cleanup row-select finds no eligible setup (sql.ErrNoRows), the function commits the (empty) transaction to release it and returns an empty SampleInstanceCleanupResult. This wrapper is returned when that no-op commit fails — the database connection was lost or the transaction was already aborted between the SELECT and the COMMIT.

Source

Thrown at backend/store/sample_instance.go:283

		return nil, errors.Wrap(err, "failed to begin sample instance cleanup")
	}
	defer tx.Rollback()

	var workspace string
	err = tx.QueryRowContext(ctx, `
		SELECT workspace FROM sample_instance_setup
		WHERE deleted_at IS NULL AND (
			(activated_at IS NULL AND updated_at <= $2)
			OR (activated_at IS NOT NULL AND expires_at IS NOT NULL AND expires_at <= $1)
		)
			AND workspace > $3
		ORDER BY workspace
		FOR UPDATE SKIP LOCKED
		LIMIT 1
	`, now, staleBefore, afterWorkspace).Scan(&workspace)
	if errors.Is(err, sql.ErrNoRows) {
		if err := tx.Commit(); err != nil {
			return nil, errors.Wrap(err, "failed to commit empty sample instance cleanup")
		}
		return &SampleInstanceCleanupResult{}, nil
	}
	if err != nil {
		return nil, errors.Wrap(err, "failed to lock sample instance setup for cleanup")
	}
	setup, err := getSampleInstanceSetup(ctx, tx, workspace)
	if err != nil {
		return nil, err
	}
	result := &SampleInstanceCleanupResult{WorkspaceID: workspace, Found: true}
	if err := callback(ctx, &SampleInstanceSetupTx{tx: tx, workspace: workspace, replica: setup.ReplicaID}, setup); err != nil {
		result.CallbackErr = err
	}
	if err := tx.Commit(); err != nil {
		return nil, errors.Wrap(err, "failed to commit sample instance cleanup")
	}
	return result, nil

View on GitHub (pinned to 1870550677)

Solutions

Confirm database connectivity; a connection-level error here means the pool served a dead connection — enable connection lifetime/health checks (ConnMaxLifetime, ping before use).
Treat this as transient: the next cleanup tick re-runs the select and will take the same empty path once connectivity is restored.
If 'transaction is aborted' appears, find the earlier error inside the same transaction (e.g. the SELECT timeout) and address its root cause.
Avoid holding the cleanup transaction open long; the empty path should commit immediately after the no-rows SELECT.

Example fix

// before: stale pooled connection fails the empty commit
res, err := store.WithLockedSampleInstanceSetupForCleanup(ctx, now, staleBefore, cursor, cb)

// after: keep pool connections healthy so empty commits succeed
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(1 * time.Minute)
res, err := store.WithLockedSampleInstanceSetupForCleanup(ctx, now, staleBefore, cursor, cb)

Defensive patterns

Strategy: retry

Validate before calling

// recycle stale connections so empty-transaction commits do not hit dead sockets
db.SetConnMaxIdleTime(1 * time.Minute)
db.SetConnMaxLifetime(5 * time.Minute)
if err := db.PingContext(ctx); err != nil {
	return // defer cleanup tick
}

Type guard

func isAbortedTxn(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == pgerrcode.InFailedSQLTransaction
}

Try / catch

res, err := store.WithLockedSampleInstanceSetupForCleanup(ctx, now, staleBefore, cursor, cb)
if err != nil && strings.Contains(err.Error(), "failed to commit empty sample instance cleanup") {
	// harmless: nothing to clean anyway; retry on the next tick
	log.Printf("empty cleanup commit failed, will retry: %v", err)
	return nil
}

Prevention

Set pool idle/lifetime limits below any intermediary (NAT, LB, pgbouncer) idle timeout.
Do not hold cleanup transactions open across long callbacks; commit promptly on the empty path.
Treat empty-cleanup commit failures as benign — no data was at stake — and retry next tick.
Enable TCP keepalives on the Postgres connection string to prune dead connections early.

When it happens

Trigger: Calling WithLockedSampleInstanceSetupForCleanup where no row matches the stale/expired predicate AND tx.Commit() of the empty transaction fails: connection dropped after the SELECT, database restart, statement timeout aborting the transaction, or context cancelled at the commit boundary.

Common situations: Long-lived cleanup connection going stale between ticks and failing on the first commit; Postgres restarted while the cleanup loop held the idle transaction; 'current transaction is aborted' after an earlier warning/timeout in the same transaction.

Related errors

AI-assisted analysis of bytebase/bytebase@1870550677 (2026-09-06). Data as JSON: /api/errors/f30ed5358edbbdc8. Report an issue: GitHub.