Mostly a fantasy trope, I think?
I mean, I like pain as a whole thing, and have been given the responsibility of delivering to a reasonable number of other people, and the overwhelming consensus seems to be that actual vaginal pain is more distractingly uncomfortable than fun.
Like, people enjoy working up to things that they feel should be impossibly huge, but the whole trick to doing that (I have found) is to graduate the experience so that it doesn't actually hurt at the time.