I've worked in varying degrees of 'agileness' My experience in an end deliverable scenario - ie there is a website to be built by a deadline, then the project is over - is that full agile is a waste of time. The same fuckups happen: designs change, clients change their mind, scopes creep, people miss things. And they need to be dealt with. Plus there's the overhead of all the agile comms, which is meant to avoid the fuckups but never seems to.
I'm sure it works well where it belongs, I get it for a continuous integration, ongoing product. But for me, I think the agile evangelists are full of it. They act like before agile people would just work in isolation with no consultation between accepting the job and delivering the end product. I'm sure the whole thing is to increase project managers pay while reducing their workload and responsibility.
I think it's important to separate two things you touch on in your post:
1) those who think they are full of it
2) different project management models
(and important to know that I am not a qualified Agile PM in anyway - though am thinking about doing a course - so won't get all terminology correct)
I think you get #1 type people no matter what model you're referring too, as no project should ever be run without flexibility, nor should anyone believe they have "all the answers".
With regards to the Agile idea, in theory (and when its execited well) it actually addresses some of those "fuckups" you mention, in that it allows for the possibility of clients (or internally) to change their mind, scopes to creap, people missing things ...etc.
ie, if during one "sprint" a client puts forward a new idea or feature they want, that can be scoped and added into a future sprint without affecting the work already planned, unlike a more rigid PM model.
The Agile model is more relevant for larger projects, where most would expect changes to happen from either side as you will always learn things - and change minds - as the project progresses. And yes, this can be catered for within a non-Agile model as well, but Agile does seem to make it clear up front that things will change, so let's take it step by step so we can think about this large project in smaller, more manageable chunks.
But as with anything, there should always be flexibility allowed for, which is usually where any real issues arise (when there's a lack of).
Dan is also correct. I've always thought of "Agile" as something I've done in my own way (or in the team I was part of own way) for a lot of my PM career. Maybe formalising some of the processes along the way.