“Does that mean any major changes in modding, etc for single player?”
Yes. Maybe some background should be given on this change first. As I understand it, Minecraft SP and MP are coded like completely different games. This makes it very hard for Mojang and it’s also the reason why MP is so much buggier than SP. What this will do is make single player run a server in the background. You’ll basically be playing multiplayer by yourself. This shouldn’t change anything on the user end. Though, it does open up the possibility that you will be able to simply invite a friend to your SP world, similar to how it is on the Xbox.
This will change modding though. The new modding API will work like Bukkit, so to install mods (SP/MP) you just drag and drop into a mod (or plugin) folder. The mod API was delayed for 1.4 though (source).
“f it is, might be helpful if you’re somehow stuck in a large biome without a river/lake/ocean nearby… :P”
Even in large biomes, there are plenty of lakes on the surface. I’m sure there are plenty of underground ones too.