The Day of the Jackal, Frederick Forsyth's first novel, was turned down by many publishers because - as it concerns an assassin who is after General de Gaulle - they reasoned that readers wouldn't be gripped as they would know the main character doesn't succeed in his aim, and therefore there would be no tension.
They were wrong - it became an international best seller and a tension-packed film. Why? We know the main character won't succeed, because de Gaulle wasn't assassinated. So why are we waiting to see if he will be?
The main character in The Day of the Jackal is an exception to the usual rule. He is an Infallible Hero. Every setback he's already planned for. He's got lots of passports, knows how to disguise a car and smuggle guns through customs. He's ruthless about killing anyone who gets in his way. He runs rings around the poor old police, plodding along in his wake. He appears invincible. We know he won't succeed, so instead of the more usual: Will the hero succeed in their quest (the answer usually being Yes), the question becomes, firstly, how can this Infallible Hero be stopped? and secondly, Who is this Infallible Hero?
We're not stupid, us readers. We know most stories start at A and end with Z, whether they're a romance (which always ends with a kiss and Happy Ever After) or a murder mystery (the detective finds out who dunnit) or a thriller (the secret is unmasked). It's how we get to Z that matters, not what Z is. The more Z appears impossible to achieve (the lovers have a quarrel, the main suspect is murdered, the trail goes cold etc) the more we like it.
The easiest way to make Z impossible to achieve is to make the main character fallible. They muck things up. They get it wrong. They forget the important gadget. They go off in a huff. Or make a bad decision. Just like us, in fact. They are fallible, but achieve their goal in the end.
We often pitch our fallible characters against apparently infallible antagonists, often authority figures like parents, head teachers, megalomaniac bosses or evil corporations. Think of The Terminator. Arnie's robotic character is unstoppable but the poor fallible humans have to stop him somehow. How will they do that? It's impossible! But by the end they do. They get to Z (against all the odds, as the cliche has it).
If our main protagonist was infallible, and got everything right AND achieved their goal, how irritating would that be? We'd be utterly fed up with them. So we need to know that the apparently infallible hero won't succeed a la Day of the Jackal. Similarly, if our apparently infallible antagonists turn out to be infallible and the poor hero fails utterly, then the story is limp and ends unsuccessfully. (How to make apparently down beat endings up beat is the subject of another post.)
It's the struggle we like. We hope that in real life our struggle will be rewarded by success. If there's no struggle, then we hope the rewards won't follow. Sadly real life isn't fair like this, but fiction can be. As an author part of our job is to make it so.