In single-player experiences especially, I think a difficulty curve is absolute necessity to keep players engaged. In my mind, an increase in difficulty over time gives a sense of progress; like anything with the capability to learn, the more you perform a task the better you become at it, so a lack of increase in the skill required to "win" creates stagnation and complacency in the player. In plain English, without a rise in difficulty, the player says, "I have no need to get better, so why should I?"
Having said that, however, I think one of the issues that the OP is seeing is a problem with the rate of change of difficulty. I have a feeling this is an impossible to perfect situation because different people improve at different rates. A curve that rises too slowly causes boredom and the aforementioned complacency, while a curve rising too fast, or one that rises suddenly is punishing the player, rather than challenging him or her. This is one of the issues I have with many physics-based puzzle games - the early challenges start out stupefyingly easy, which lulls the player into a comfort zone, then suddenly the puzzles become mind-bendingly hard or crypic [http://tvtropes.org/pmwiki/pmwiki.php/Main/FakeDifficulty] to the point you have to effectively consult the answers [http://tvtropes.org/pmwiki/pmwiki.php/Main/GuideDangIt] for step 1, or else flounder with trial and error [http://tvtropes.org/pmwiki/pmwiki.php/Main/TrialAndErrorGameplay]. It's a terrible misconception game designers have, that once you teach the basic mechanics one-by-one, a player instantly knows exactly how to string them together; which is totally wrong. One thing Portal did right was teach the mechanics, then teach how they all link together and only then did they set the player free.
What I think the OP's other issue with the difficulty curve is a lack of perspective. A game getting harder for the sake of getting harder is also pointless. A player needs to be shown "after-the-fact" and as a part of the main story (i.e. not via replaying the game) what their improvement is worth. I've found that Monster Hunter Tri and Half Life 2 have done this very well, albeit with varying degrees of time between the moments. In the case of the latter; the "super gravity gun" final chapter gives the player a super-weapon, but also gives some perspective about what they've been through; how the combine soldiers (especially the elites) which were once a significant challenge are now effectively cannon-fodder, a moment the player has already experienced with Civil Protection. Many games show you prior what the "big goal" is and tell you "you've got to get better before you fight him", but few actually show you the benefit later on beyond being able to take down the one who's been hovering over you indirectly all game.