Lo Pros, Represent

Excuse my ignorance, but I've never seen one 'in the flesh' - what stops it being massively uncomfortable to ride? Is it like riding a road bike in a tucked position where you're holding the bottom of the drops the whole time?

Yes, that is the exact point. So it is as comfortable as riding in the drops all the time which would be a problem for many who ride along wondering what that bit below the hood is for.

Not exactly, many of them (especially the small front wheel jobs) are designed for pursuit and aero bars, meaning the top tube distance can be very short, which is why hipsters who have put risers and a short stem on look like they are riding a clown bike. If you are using pursuit bars then it's probable the position will be similar to an aggressive road drop.

There are examples of both on this thread.