Well, in both your ship and your elf town example, the blockade and the unblocking action are somewhat unconnected. I don't know how anybody would actually do this, but as the elf town example comes from an actual game, someone obviously did. I agree, this sounds like extremely lazy design.
But why shouldn't I imply to a player that eventually he will have to enter area X once the roadblock is gone? This can give him a goal and create some tension, at least if that area sounds like an interesting place, maybe thanks to some in-game lore. It just should be clear what must be done to remove the roadblock.
I think the best way I could put it would be, which of these scenarios would bring you more joy and which would bring you more frustration?:
A) You won a free five-day trip to Disney World that starts today! You leave your hometown, start to drive there, and on the way, your car breaks down. You try to fix it all afternoon, nothing works, so you call a towing service and arrange to rent a car the next morning. The rent-a-car place doesn't have any cars available for rental, so you have to wait another day until one is available. Finally, everything works out and you make it to Disney after two days of hassle. You enjoy the three days that remained of what was supposed to be your five-day trip.
B ) You're in your hometown and your car breaks down. You try to fix it all afternoon, nothing works, so you call a towing service and arrange to rent a car the next morning. The rent-a-car place doesn't have any cars available for rental, so you get a friend to drive you home and wait for tomorrow when one will be available. When you get to the car rental place, not only do they have a car ready for you, but you're their 1,000,000th customer and as a result you've won a free three-day trip to Disney World! You hop in your car, drive to Disney without issue, and enjoy your three-day trip.
To me, this is the problem with telling the player the mission is to go to area X before he can actually go there. He wants to be there. He feels like he should be there. But he's not allowed to go there. It feels bad! I don't think it creates tension - rather, it's more likely to create
anticlimax. Tension comes from feeling like something big is about to come to a head, for better or worse. If, on the way, I find out I actually have to do Y before I can go to area X, I'm going to be looking past Y (to X). I'm going to care much less about Y as a result, and become bored of Y. And when I finally get to X, unless I
really care about what I'm doing there, the experience at X will be hurt too because any tension I had felt was broken by a period of boredom.
I think those three days at Disney are going to be a much more joyous, visceral, and memorable experience in Scenario B.
A statement like "the seas are too rough" is indeed pretty bad here, because what could the player do to calm the seas? This really feels like an arbitrary flag set by the designer, and if all the player can do is solve some quests that are completely unrelated to the reason for the roadblock, he is invariably forced to think in terms of game mechanics, i.e. an abstract layer. Personally, I think it is that abstraction that we want to avoid if we wish to keep immersion. But as long as we can answer the questions "Why is the roadblock there?" and "Why hasn't anybody else removed it yet?" with in-game reasons, things should be fine. (The elf town example fails at the second question, as just about anybody could have either pushed the boy aside or gathered the fruit. No reason to leave this to the heroes.)
You make an interesting point about the "disconnect" between obstacle and solution being part of the kill-joy effect here. I think that this disconnect is just one particularly egregious type of plot flag that makes the player think "do I really have to do this", though. As a good example, let's use your idea of needing an invisibility spell to get into a sacred church because you're not a member of the clergy (and let's assume that you need to do something non-trivial to get that invisibility spell, like clear a magic ruins-themed dungeon). When the player is sent off, are they going to think this is a reasonable ask from the story? Perhaps, perhaps not. Most (not all) RPGs tend toward power fantasy dynamics (and high-magic settings to boot).
So while I'm trudging through the dungeon to get the plot coupons I need to get into the sacred church to get another plot coupon that lets me do what I wanted to do in the first place (wow that's a long clause - imagine
playing it), I'm inevitably going to feel like I would have rather broken into the church at night or asked my Thief party member to sneak in and grab the Sacred Torch or ask the Bishop I did a sidequest for four towns back for help or beat up that Bishop and take his hat or simply
ask a clergyman for help because we kind of need this to re-seal the ancient demon that's been terrorizing the world.
It's certainly possible to frame this kind of roadblock in a way that it feels realistic and logical (and maybe even plays on the player's biases and emotions) - maybe the church is revered the world over and is guarded night and day by hundreds of Papal Knights who know each other by face and the church is clearly working against you and your allies for one reason or another. If you can deftly get this information into the narrative, very few will question why you'd need invisibility to get what you need. And I guess that sort of speaks to both your point and mine, that framing is really important when using "roadblocks" in a game.