This is not a challenge.
This is metagaming, whereby you go to square 1, because you didn't meet the arbitrary goals the game has set you. It's learning not about the gameworld or the game mechanics but of artificial boundaries that limit your character builds "just because" no one bothered to give viability to another set of solutions. Or botched character progression.
So challenges
should be entirely transparent and predictable? Going into a combat encounter the player should already know what to expect, yes?
No. Unexpected challenge is fine provided that it does not force you to meet arbitrary conditions only certain roles can fulfill and others cannot to progress in any way in the game.
Let's consider this example: 10 hours into the game you face a dragon. Here's the thing - so far you've played a lite-gunslinger with a flair for medicine and diplomacy, which has pushed you forward for better and for worse in the game world. If you were to face the thing head on you'd die, because you lack a) skills and b) equipment.
IFF the player was informed (or could acquire that information but neglected to do so) that they might encounter dragons, and failed to make preparations he has himself to blame. However, even in this situation you could change to the goal of the encounter: instead of "Kill the Dragon", you get "Survive (X turns) or "Escape the dragon by reaching the Southern part of the tunnel". Such simple solution reinforces the importance of character builds, affects gameplay and leaves no player behind - everyone is accommodated, but not everyone can get the same results.
Opening a door should be preceded by information on what's on the other side, in case the player accidentally kills himself by acting on incomplete or lacking information? Apparently taking a risk and preparing for any eventuality is meta-gaming.
Then give the player ability to acquire this information. Yes, there should be option to listen through the door and see if there are any enemies behind it. If he is careless, then the game can punish him howevr it fancies. Taking risk is one thing, acting blind (because the game didn't bother to give you any options) and fitting the right pattern, especially with save/load mechanic is another.
RPGs should avoid this model:
whereby at the beginning you select a bunch of blocks, only to realise that no block fits the given hole. Such sorter games are pretty spastic, and this is part of the reason why for a long time cRPGs were seen as PnP's retarded younger brother.
In PnP DM does create challenges which are manageable to his players and their characters. This is common sense. It is the job of the players to use all those skills they have at their disposal to overcome difficulties. Unless DM is a total dick that enjoys spawning dragons on 2nd level characters, that is.
Now I am not saying that the player ought to be free from responsibilty. If he made a good engineer or doctor who cannot into social situations he should obviously fail all the quests that require diplomatic approach. Perhaps he should learn early on the importantce of his limits and avoid quests that do not fit his character, because he can make the matters worse for others and himself. In fact creating unwinable fights and other challenges is something I strongly support. Provided that by using creatively tools that the player has at his disposal he can achieve *some* results - maybe suboptimal, or catastrophic one, but at least some.
Again, why should there even be skills there are nearly useless? That are checked 2-3 times in entire campaign, if at all? Either you cut them out and polish what you have or make them meaningful and relevant to the context.
Nah, makes the game too obvious. A ton of nearly useless skills equals a ton of unique encounters and challenges. I mean, we aren't talking about cutting it down to a single skill or say two skills, right, even-though there probably will be a small number of skills that stay viable or op during the entire game. So you're already allowing for comparatively useless skills as long as they have more than a handful of uses. Your argument is essentially rooted in what you personally consider useless and little else.
It is rooted in A) The magnitude of using the skill (the impact it has on the gameplay) and B) Frequency of using the skill/item. If A and B have low value then the skills is useless, and has little place there. Putting such skills is just bloating the game.