I didn't have that much trouble with the bit in Half-Life 2 the original poster mentions. Sure, I died more times than I could remember, but I did that throughout the game (thank Valve for autosave) and the hardest part was just getting to the infinite ammunition crate after every two shots. The part of that game that really made my rage however was the second Strider battle just before the Citadel: first I had to run screaming past two of them as they stomped everywhere and blasted my allies, then crawl through a skeleton of a building with infinitely respawning soldiers (I killed at least thirty on my first play through before I realised they just respawned) and those same Striders shooting at me the moment I was anywhere near them, then once I finally got to the rocket crate at the top of the building there was no cover whatsoever therefore I had to shoot faster than they could (I usuall just switch to Easy difficulty now). I consider that to be the true final boss of the game, as the remainder is just crushing everything with the super gravity gun.
Also, the Avatar in Dawn of War II. By that point all my squads were Level 20 and I was tearing through xenos and bosses with fantastic speed, almost nothing could harm me, and I was just killing time and collecting wargear before getting to the final mission. Then I decided I wanted another suit of Terminator armour, which the Avatar mission provides, and I spent the next half and hour or so just chipping away at that monstrosity's health while fleeing at the first sign of any of its attacks. Fucking space elves...
Finally, in a more "or so I heard" note, I haven't played them myself but Touhou games are notorious for having an absurd increase in difficulty at the fourth stage. Patchouli, Satori, Aya, Murasa and especially the Prismriver Sisters are punches to the crotch compared to what comes before them.
