Pair programming can be powerful with the right people and the right problem. It's a situational tool that can speed up progress in certain cases. Sometimes diving deeply into code is scary and having someone else right there can provide a feeling of safety that's conducive to making real progress. Also, sometimes programmers can have complimentary skills that can help them solve hard problems together on the spot. Pair programming sessions with the right people can also be intensely productive - people sometimes perform at a higher level when they're being watched. I've had very productive 3 hour coding sessions with team mates.
What I don't like is weekly or recurring pair programming sessions. That's always a bad idea. Pair programming is a powerful tool but should be used judiciously and situationally.
20 years ago I worked for Adobe on the Acrobat team. They brought me in because they needed some Windows expertise to help a team of talented developers who had mostly coded on the Mac.
I don't think the term "pair programming" had been invented yet - at least I'd never heard of it - but when I was in the office I often spent a few hours a day on "house calls". People would ping me and I made the rounds advising them on Windows issues.
It was focused time. No one needed my help much of the time, but when they did, we could work through a problem quickly and they would be better equipped to handle it on their own in the future. They always "drove", I just watched and discussed and helped troubleshoot.
It was really great. As you said, what made it work was that I was there when someone asked for my assistance, not just because "pair programming" was the thing to do.
I had a role as a mentor/go-to-guy for a complicated codebase and had a similar experience. I really enjoyed helping people out and loved the positive interaction, I would never have called it pair-programming and funnily enough we did have some kind of pair programming initiative at the time.
If I were to give any advice to dev leads/teams/management that wants to introduce pair programming, it's good to put people of differing capabilities and expertise together. That way there's some value being added, and you're spreading around the arcane knowledge of your platform.
In general I agree. I've found pair programming to be immensely beneficial and productive for some cases.
I wouldn't go so far as to say recurring sessions are always bad, but I do think that not all tasks benefit from having a pair work on it.
When I have been very attuned with my teammates in terms of the system we were building and how we were building then pairing sessions became much less valuable on work that was very low novelty and was some iteration of a well-established pattern.
What I don't like is weekly or recurring pair programming sessions. That's always a bad idea. Pair programming is a powerful tool but should be used judiciously and situationally.