Never had a real facepalm moment I can recall, but I had a few "Dammit!" moments that might qualify.
Consider my first attempt at obsidian farming; I had a magma pipe on the surface, and decided therefore to build both a magma industry on the surface, and to use a channel from there to the magma portion of the farm, held back by a floodgate next to the industry. If that's not clear, I used my forge/glass/smelting area as a reservoir for the magma, stupid mistake number 1, and had the reservoir "controlled" way the hell away from the farm, stupid mistake two. I also had a murky pool next to the magma channel, and decided to use that as a substitute for the brook I planned to feed the water reservoir with. Foolish, but acceptable at the time. What's worse is that I did so by feeding the pool directly into the magma channel. This meant that a) it solidified the magma starting from the point where the magma fed in, creating an obsidian plug and preventing more water from getting down, and b) when I reopened the floodgate to allow the magma to run free, it solidified in the floodgate, blocking it open.
All in all, my first foray into obsidian farming was much Fun. I had a few other teething issues, mostly in the area of how long to leave the water going, but those weren't anywhere near as moronic.
There's also my aborted attempt at fish farming. I built a channel, blocked with a floodgate at the entrance and grates at the exit, to allow fish to come in but not out and air-drown them where they could be butchered without slaughtering my poor dwarves. In what might have been a clever course of action, I decided to have its outflow feed into the reservoir for my obsidian farm, which had another inflow, also from the river. When the reservoir was complete, but not the fish farm, I opened the floodgates and let the water rush in to fill the reservoir. I had foolishly forgotten the water pressure, and it roared through the reservoir, up the farm's outflow, and flooded it completely. Once again, Fun. Could have been worse, though; I'd been planning to feed the well from it also.