In my estimation, the rise of cavalry came about directly as a result of the deterioration of Roman infantry, and not the other way around as many seem to believe. This came largely through the barbarization of the legions, a critical issue even during the reign of Trajan. Increasingly, Rome simply relied upon auxiliaries to fight their wars, and gradually these foreign auxiliaries became the core legions.
As a result, the discipline of the legion was a thing of the past as the migration age began, and so they were forced to adopt a much greater cavalry arm in order to counter the many incursions into Imperial territory.
Getting back to the question at hand, was cavalry a major weakness for the legions, I would argue no, even during the early Imperial age. They faced a variety of cavalry based enemies (Dacia, Alans, Parthia) and generally triumphed over them in pitched battles, with only a few disastrous exceptions, like the infamous Cannae.
Further, contrary to popular belief, Roman cavalry of the early Imperial era was quite effective for its role as legionary support. Of course, as Italia wasn't known for her horses or horsemen, often the cavalry would be allied or auxiliary.
In regard to the early feudal age, heavy cavalry came to play such a profound role not as a result of the stirrup, but because of the lack of cohesion and discipline in the infantry of the age. The common peasant levies, poorly armed and armored, simply had no hope of standing against a heavy cavalry charge.
However, it's no surprise that as soon as a cohesive, well disciplined infantry once again emerged in Europe (Swiss Pikemen, Landsknechts), even the most heavily armed knights were quickly proven to be out of their league. At this point, the role of European cavalry gradually came to resemble that of Roman cavalry, largely as support for the infantry.