Hmmm, the title and the anecdotes "Orcs on Stairs" seem to be more about one encounter design than about global plot holes in a campaign.
Sorry. I've been following the Rime discussion in the other thread, so I was addressing the OP's concerns in that broader context, rather than just the "orcs on stairs" scenario.
But for me, it's he same thing. If you run a module, read it from start to end. If you run a campaign, read it from start to end, and if you find plot holes due to your (positively) biased reading with the lens of your experience and the expectations of your group, make the necessary decisions. A campaign is even more variable and complex than a simple module and even more a simple encounter, so it's even more necessary to make your own sanity check. Once more, it's easy to say "all publications suck" without publishing something yourself, I'm pretty sure that if all the complainers tried, they would realize how easy it is for readers to tear new holes through their plot.
Of course, I'm not saying that all publications are perfect, but they are designed to inspire you and give you element, not do the job of DMing for you.
Something I've found is that there's a world of difference between reading an adventure and running it. So many times, I've read through an adventure and thought "Wow, this is really cool! I want to run this." And then, later, when I sit down to prepare it, all sorts of issues pop up that I just didn't see/think of when I was initially reading it.
For example, when I first read through the Acq Inc book's adventure,
Orrery of the Wanderer, I thought it looked like a helluva lot of fun. But when I sat down and was actually running the first episode, the nonsensical nature of the opening scenario suddenly became glaringly obvious - the 1st level PCs are tasked with tracking down two missing city guards in a series of tunnels beneath Waterdeep. At least one of the guards has made it all the way to the end, and yet there's hardly any evidence of the guards having passed through each encounter ahead of the PCs. As an experienced DM, I was able to add some details (footprints in the dust on the floor, for instance) and determine on the fly that the traps reset themselves or that various monsters only showed up after the guards had gone through. But a new DM might struggle a bit with the whole premise.
When I come across nonsensical stuff like this in the middle of the game, I like to talk to my players about it, and we all have a bit of a laugh.
Setting aside issue the OP outlines has nothing to do with plot, the idea that you think plot holes matter is so - quaint.
I mean, have you ever seen a movie?!!
Sorry. "Plot holes" may not have been the best term. As I said above, I was also speaking in the broader context of
@Retreater's Rime discussion.
Has WotC ever made a great adventure? I've played and read through a lot of them, and I can't think of one that really impressed me. Some had at least a few neat ideas, but usually it's "bare bones plot and crazy bad guys with strange abilities, plus a memorable NPC or two".
A lot of Sunless Citadel actually builds on the lore for the ancient red wyrm that does not appear in the adventure at all, and really, the only truly memorable part of the adventure is Meepo and the kobolds.
"Weird semi-vampiric Druid with evil tree" as a final boss was like, eh?
Yeah, I don't really like
The Sunless Citadel either. I do, however, like its two sequels,
The Forge of Fury and
The Speaker in Dreams.
The Standing Stone also looks good, but I haven't had a chance to convert and run it yet.
That said, I think
Red Hand of Doom was probably WotC's best 3e era adventure. It had some issues, for sure, but was generally a pretty solid adventure.
Well, I really liked Tomb of Annihilation. It's not perfect, but the balance of City + Hexcrawl + Really well done Dungeon in a nice jungle environment is cool.
I really like ToA as well. I'm most of the way through a second run-through of it.
Nothing for 4e. The Arc ideas for the main APs (Orcus vs. the Raven Queen) is cool but the realisation atrocious, and I sort of liked Scales of War, but it was very very meandering (and in the end, as most 4e modules, it was mostly a set of encounters that looked cool tactically, not what I'm looking for).
Madness at Gardmore Abbey seems to be pretty well regarded. I own it but haven't run it. (When 5e first came out, I started to convert the adventure but ended up running other things instead.)
One of the most unforgivable points -There's no heist, at least not one the players participate in!
Dragon Heist is like a "how to" of how not to write an adventure. It's got
all the things you're not supposed to do in it, including forcing your players down a railroad and having uber-powerful NPCs show up to resolve the climax while the PCs watch and all that jazz.
In Storm King's Thunder, a village is attacked by cloud giants who are there to steal a magic stone. The adventure starts by the PCs arriving there, learning about everything, and helping to save the villagers who fled the place and are in trouble somewhere else.
The "Great Upheaval" opening adventure was not included in the playtest packet for SKT, and I think it shows. The connective tissue between it and the rest of the adventure is really weak. That said,
Storm King's Thunder itself is a solid adventure that doesn't need a lot of tweaking to run with minimal prep. I really enjoyed both playing through it and DMing it.
I also
really like the "Trouble in Red Larch" opening adventure from
Princes of the Apocalypse. It's a solid little starting town with some fun things to do and a nice mix of adventure and intrigue. It's become my Village of Hommlet for 5e. I've used it several times to kick off longer-running campaigns.
That said,
Princes of the Apocalypse itself has numerous issues. One of the more glaring ones was that as initially written you couldn't actually resolve the opening hook (find the missing delegation) because the authors had omitted the location of the various people you were supposed to find. This has since been rectified via errata, but the adventure still includes numerous glaring omissions. I made a whole thread about it back in the day:
Problems with Princes of the Apocalypse