Short Version: I ramble on what is much better said by that "thoughts on game design" blog post. On the other hand, if your means of experiencing the game is coming up with your own narrative as many people do in Dwarf Fortress, permadeath can definitely harm the player experience despite being a roguelike....
If you loosely define permadeath, it is one of the oldest game concepts and one of the most important. Do you get to take a hand back in poker? Undo a move in chess? If you're playing a tabletop game of warhammer 40k, or risk 2210 will the other players let you go back five turns? Forget about losing hours of progress and a slow early game, some of these games require a significant investment of time before you even start playing them. This is not to say this is the one true game model, and we're stretching a bit with various elements of strategy, reversible setbacks, entirely different types of play. The idea of a competive FPS with permadeath is quite ridiculous, but when it comes to an RTS or TBS like Starcraft/Dominions, out is out.
If you join a mega modded game of Dominions (60 players, all eras), your odds of winning are absolutely horrible, time investment is high, and you can make irreversible (pretender) mistakes before the game even begins. Of course, Dominions 3 is a cult classic and not a game that can pretend to be as accessible and mainstream as Galactic Civilizations, let alone your XCOM or Sid Meier's Civilization.
Tangent for Keith: You might find yourself interested in Rogue Legacy. It has some conceptual similarities to Spelunky and A Valley Without Wind, although it isn't actually out yet to be judged on it's own merits. As far as I can gather, your expected permadeath lifespan is around three minutes or less, but your ancestors have inherited some of your gold, basecamp upgrades, weaponry, and also various traits such as nearsightedness, flatulence, Alzheimer's, giantism, dwarfism.
Back onto the current topic du jour, permadeath. I'm -not- a universal fan of permadeath, but I generally like it. In particular, I'm getting trashed in the crawl early game right now, despite formerly excelling at it. Permadeath brings a particular value to the loosely defined "Dungeon Crawler" genre that a save system simply cannot. Permadeath is a genre equivalent to coin-op platformers with their limited lives and continues. Outside of coin-ops, the gold standard of platformers is the checkpoint system, and a few indie PC platformers will let you save wherever. Unfortunately for roguelikes, a continue system either makes the game too easy, excuses some bad design (I think DCSS unique monsters are mostly superior to TOMEs), or traps the player in the "You've already lost but just don't know it yet." situation Keith tries so hard to avoid in AI War design.
With a traditional roguelike, there is a critical lack of the middle ground equivalent of checkpointing... and no real tested method to replace it. TOME does let you bring a character back to life, Shiren lets you bring some persistence to the game with unlocks and NPCs and yet it will still kill you dead, while games like Crawl and Nethack just kill you dead and punish you on your next run with ghosts and bones. Permadeath is the best available albeit imperfect method of making your turn to turn decisions actually matter, rather than just be grinding/clicking/reloading to pass time until you unlock your next skill point or plot choice that actually matters.
On the other hand, more typiclal RPGs work just fine with many variations on a save system. They can even provide interesting and challenging fights although most choose not to. Avernum/Exile and Ultima VI spring to mind as examples of challenging fights. Japanese-style RPGs provide an interesting case study in the dangers of grinding. Grinding is a great pressure valve; unlike your Contra, you know you can beat this game no matter how bad you are as long as you keep trying. Unfortunately, the competing pressures to have secrets, rare drops, sell strategy guides, to be a completionist can ruin the challenge of a game. In some discussions on roguelikes I've seen recently, I've heard people raise the question of what use is creating content the player might not see, when it should all be interesting and compelling. That's the kind of trap that burned me out on the final fantasy series after the internet (gamefaqs) came around and I became compelled to find everything as part of some grand character flaw. Finding or stealing this rare content gives you a fleeting feeling of satisfaction, but combined with the spare gold and experience you pick up along the way you get less compelling, less skin of your teeth battles. For expedience's sake I'm going to skip over the Diablos, the Skyrims, the Mass Effects, the Witchers, the Fallouts, the Gothics.
I apologize if I haven't managed to keep a single thought/hypothesis from start to finish, I've been typing this up of and on in stolen minutes. I'm don't think I even have a central hypothesis I'm trying to prove, though the central point seems to be the comparison to coin-op/checkpoints/save anywhere platformers. Permadeath enables a certain amount of competition against the computer, actual challenge that (as an ideal at least) makes minute to minute decisions relevant rather than being busywork between cutscenes.