We were somewhere around Barstow, on the edge of the desert, when the
drugs began to take hold. I remember MM saying
something like:

I've been puzzling how modern pipe insulation works. If the
temperature in a room is constantly at 0 dec C, then surely EVERYthing
in the room - pipework, insulation etc - is at the same temperature?
(Given that there is no hot water flowing through the pipes.)

So what actually does the foam insulation around pipes do to protect
them?

All insulation can do is slow down heat transfer, so a pipe in the
situation you describe, with no flow of water, will eventually fall to
0degC too, and lower, if the room temp goes down. All it does is buy
time until temps rise again or water flows before freezing.
No magic involved.