I abhor the use of hover in webdesign and I'm glad that devices like the iPad may spell doom for that particular design idiom. It's bad UX in general, if touch computing pushes it off the cliff into oblivion then good riddance.
Why?
There are places where :hover triggered behavior is beneficial, but these tend to be vastly overwhelmed by the examples where :hover behavior is abused to the point of degrading usability. I'm aware that designers tend to loooove hover menus because they are shiny and cool, but from a usability standpoint we'd be better with a lot fewer of them.
For example, while I completely agree that nested hover menus implemented naively (as many sites do) can be very annoying, single-tier menus with large areas can be an effective way to provide navigation of larger sites without cluttering the main page with dozens of links that most users won't care about. Likewise, while the "falling off the edge" problem is a real one, there are tools like jQuery's hoverIntent plug-in that can make the UI feel much more natural.