...a very exceptional person (the one in ten thousand they supposedly say "have perfect pitch")
From Wikipedia: "The assumed occurrence of less than 1:10,000 is widely reported, but it is not supported by evidence. However, a review of more recent and international studies indicates prevalence of at least 4% amongst music students."
...they all use what they think is special about themselves to draw undue personal attention
I don't dispute that you've encountered somebody like that, but I think it's unfair to paint everybody with that brush.
...some kind of super power that has no practical use whatsoever
I wouldn't call it a super power, but there are some practical uses:
1. Tune a string instrument when a pitch reference isn't available
2. Easy to play in tune (or at least know when you're out of tune)
3. Dictation and sight-singing are really easy in music theory classes. (Also helps sometimes with identifying songs in music history exams...it usually not So-and-so's symphony in A Major if the song playing isn't in A Major or a closely-related key.)
4. Quickly "chip up" a recently re-strung piano
5. Do a quick muteless pitch raise on a piano that's a whole-step flat, before doing the actual pitch raise and tuning
6. When I'm scheduling an appointment over the phone with somebody who has no idea when their piano was last tuned I'll often ask them to play a few notes on the piano if it's nearby. (They always play white keys.) If the piano is a half-step flat I know to schedule more time for the appointment.
7. Automatically recognize the difference between a 60 Hz hum and feedback in a sound system
8. Use a specific note as an unobtrusive cell phone ringtone. I use this
as my ringtone...it plays an A6, which is really easy to pick out of background noise if you're attuned to it. (The downside is that ocassionally I'll be in Walmart or whatever and there's a toy or beeper with the same pitch and I'm constantly reaching for my phone.)
Most of these things could be done without perfect pitch; they're just easier or faster with.
It also has downsides. As I mentioned earlier, I can't play keyboard instruments that have been transposed (harpsichords in historical tuning or organs/electronic pianos with a transpose setting). I struggle playing the bass in "solo" tuning (transposed up a half step). I get annoyed when a choir goes flat, and it distracts from my enjoyment of the music.
Anyway, I think the main thing that prompted this long response was the suggestion that I see it as a "super power" to be flaunted as a party trick. That's not how I see it, and I rarely bring it up. Whenever it comes up in the context of piano tuning I always emphasize that perfect pitch is neither necessary nor helpful in tuning pianos. But at the same time it is useful for other things, and I can't choose not to have it, so I'll do what I can with the tools I've been given.