To me, realism is only good up until the point at which the player can be come engrossed within the experience. This usually means more accurate physics, more detailed textures/models and more recently; believable AI. These advances make the experience more enjoyable and more impressive.
On the other hand, realism can be taken too far in the context of a game. Dying or having your screen completely blurred due to a single gunshot isn't an improvement to gameplay, it is a detriment to it. Living in a constant state of fear or frustration is a sensation best left to survival horror games and platformers (respectively).
Simulators are the place for unbridled realism. Whether it be a cacophony of air traffic controllers or the recording of actual live ammunition being fired, sims are the home for those who wish to experience the joys and frustrations of real life without the risk.
Ultimately the balance lies within the hazey lines between gritty, heart-wrenching realism and enjoyable, exhilarating gameplay and fantastical story-telling. COD4 is a good example of finding the right balance. It is realistic enough to allow the players to feel like the Armies #1 new soldier...braving the harshness of Iraq some undisclosed eastern European nation. But it is also simplified and exaggerated enough to make it exciting, well paced and fun.
