if err := tx.Commit(context.Background()); err != nil {
    if pgErr, ok := err.(*pgconn.PgError); ok {
        switch pgErr.Code {
        case "23505": // unique_violation
            return fmt.Errorf("duplicate record: %v", err)
        case "23503": // foreign_key_violation
            return fmt.Errorf("invalid reference: %v", err)
        }
    }
    return fmt.Errorf("commit failed: %v", err)
}

ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

if err := conn.QueryRow(ctx, "SELECT...").Scan(&result); err != nil {
    if errors.Is(err, context.DeadlineExceeded) {
        log.Error("query timeout exceeded")
        return nil, ErrDatabaseTimeout
    }
    return nil, fmt.Errorf("failed to execute query: %w", err)
}

func queryWithRetry(ctx context.Context, db *pgxpool.Pool, query string) error {
    backoff := time.Second
    for attempts := 0; attempts < 3; attempts++ {
        err := db.QueryRow(ctx, query).Scan()
        if err == nil {
            return nil
        }

        if pgErr, ok := err.(*pgconn.PgError); ok && pgErr.Code == "40001" {
            time.Sleep(backoff)
            backoff *= 2
            continue
        }
        return err
    }
    return errors.New("max retry attempts reached")
}

type DatabaseError struct {
    Operation string
    Err       error
}

func (e *DatabaseError) Error() string {
    return fmt.Sprintf("database error during %s: %v", e.Operation, e.Err)
}

func (e *DatabaseError) Unwrap() error {
    return e.Err
}

tx, err := pool.Begin(ctx)
if err != nil {
    return fmt.Errorf("failed to begin transaction: %w", err)
}
defer tx.Rollback(ctx) // will be no-op if tx.Commit() is called

// ... transaction operations ...

if err := tx.Commit(ctx); err != nil {
    return fmt.Errorf("failed to commit transaction: %w", err)
}

if err := row.Scan(&result); err != nil {
    logger.With(
        "query", query,
        "error", err,
        "timestamp", time.Now(),
    ).Error("failed to scan row")
    return nil, fmt.Errorf("failed to scan row: %w", err)
}