Hi Mike,
My day job is actually doing 3D modeling - in my case mainly for real-time flight simulators, but we do spend some of our time doing photo-realistic renders and montaging them in photoshop.
I'd agree with John's concise summary in his post. If you want an OpenSource program to look at then you could do a lot worse than have a look at Blender
http://www.blender.org. The interface is what you might call 'unique', and does have a steep learning curve, but once you get used to it it is very very quick.
In terms of montaging, one of the key features of programs such as lightwave, blender and 3ds is that you can render out separate aspects of the scene as individual images, such as specular passes, diffuse passes and depth passes. These can then be loaded into photoshop as different layers and adjusted to your hearts content without having to re-render the 3d scene (which can take a long time). The depth pass is really useful to try match fogging with a photograph. They also let you match your camera to the realife camera if known (focal-length, f-stop, even number of diaphragm blades in some instance).
The Extended edition of CS3 has some useful new features on the 3d side. You can load 3d objects into photoshop as smart objects, which you can then rotate and move in 3d to match with your scene. You can also edit textures on the 3d model straight from photoshop, and get a realtime update.
Feel free to ask more questions, it can be a complex field as I'm sure you realise, but happy to help!
Cheers,