As I consider the drowning room, it actually sounds easier to simply put a huge retractable bridge over a very long fall and skip fluids entirely. Aside from flyers it seems perfect.
Keep in mind the bridges will not work with a Titan or other large creature on top of it. Such a bridge is for goblin use, only.
We're drifting fairly far off the aquatic defense questions of the OP, but...
True, a small single-element bridge will not operate if the foe is heavy enough. But if you have a Moria-style support pillar rising up out of the chasm, with a bridge out from either side of the top; when you pull the lever to deconstruct the support, the bridges should become unsupported and collapse, dropping even the largest beast into whatever depths you have arranged.
If you want to be particularly clever, you can probably arrange it so that you have ready-to-go retracting bridges on the sides, that are designed to stay retracted under normal circumstances. You can crank them out temporarily to resume traffic while working on re-setting the main bridge after the attack.
I've been casually considering some chasm-based defense ideas lately; in particular, trying to find interesting options for having a chasm crossing that is semi-routine for everyday dwarf and even caravan use, but under siege becomes a narrow, winding, hazardous walkway covered by ballista fire and marksdwarves behind fortifications. The intersection of cheap-bolt-resistant, flying / perfect climbing, and building destroyer isn't zero; but filtering down to only those able to even access the inner fortress should lead to pretty good results most of the time.
Another related thought: rather than building an elaborate pump stack to deliver magma to invaders near the surface, why not delve a much simpler chasm and deliver the invaders to the magma?
A 100+ level fall should render just about anything into a mix of salsa and recoverable metal scrap; add some magma pre-sorting if desired, and haul it right across the hall to your magma smelters.