Spray foam is a very specialized packing material, often required for use in shipping valuable fragile items. Engineered packaging principles are designed to protect sculptures, vases, large fossils, lamp bases, busts, computers, furniture, chandeliers and other objects of unusual shape. By virtue of the liquid foam expanding by up to 30-60 times the volume of its liquid state, it efficiently protects almost any size, form and weight.
Product innovation over the years has seen the introduction of several different types of spray foam insulation. Primarily in residential and commercial construction, open-cell and closed-cell spray foam is used while high-density spray insulation is used as roofing foam in commercial or industrial construction. Open-cell sprayed-in foam insulation, a soft low-density material, is typically used for interior applications such as wall cavities, underside of roof decks, attics, basement walls and crawlspaces. The open cell structure of low density foamed insulation allows for moisture permeability to help contribute to moisture management and bi-directional drying of the wall assembly.
I don't know if it's common or uncommon, but installing a layer of rigid foam above your roof sheathing in order to interrupt thermal bridging is always a good idea, and is preferable in all respects to a roof assembly without any rigid foam above the sheathing. If you decide to install rigid foam above the roof sheathing, you shouldn't use closed-cell spray foam under the sheathing; either use open-cell spray foam (which is vapor-permeable) or a fluffy insulation like cellulose, mineral wool, or fiberglass (any of which will allow the roof sheathing to dry to the interior).