One thing you have to realize that 90%+ of the photos you see retouched on dpreview or retouchpro are done by people who have a copy of photoshop and an internet connection, but lack skills, talent and training (frankly the last is not as important). Then, of course, either out of similar deficiency or some social motivation, such posts are invariably attended by much praise.
I can't figure whether the Hollywood tabloids are on a budget or if it's just a west coast aesthetic, like plastic surgery.
IMO, if I can look at it and know 100% it was photoshopped, it's been overworked. I'm not saying I don't do this if a client wants to pay for it, just that I'm not proud of what I do
With respect to individual photography (as opposed to commercial), my personal opinion as that the emotional purpose is to capture a specific moment of a person's life. 40 years from now, instead looking at a photo and going "Oh look, there's Sarah with those braces. Remember what a fuss they were?", what are you going to say? "Oh look, what smooth plastic skin and bright teeth she had?" Bah. It's no different than what a mortician does to a body. Everyone says how wonderful the body looks even though it doesn't look anything like the person did.