Doesn't that turn 3D graphics rendering completely on its head though? Or it simply expands on the normal rendering process? Ie. a shape is drawn in the usual vertex way, then instead of painting a texture per side, every pixel on each visible side is then programmed for colour, reflection value, etc.?
The hardware still does all the rasterization and stuff. Most of the modern graphics cards implement their pipeline with two programmable stages - vertex program and pixel program.
In a "normal" program the T&L unit just transforms the vertex data into clip space (the OpenGL GL_MODELVIEW matrix does that). With a vertex program, you can override that behaviour. However, you cannot (as a rule) introduce new vertices or different connectivity - you feed it a triangle and you get a triangle.
That data is rasterized by the hardware into fragments. Each fragment is really just a pixel with additional data attached (Depth value, stencil value, color, alpha, s/t coordinates etc.). A pixel shader (or fragment program) is called with this information and is supposed to either pass on these values as the output, or modify them. A fixed-function pipeline would e.g. apply a texture based on the s/t value. A pixel shader can do that, too, but it has indefinitely more possibilities. It can, for example, examine the current pixel's normal and modify the alpha value accordingly (that would result in e.g. the fresnal reflection effect mentioned earlier). It could also lighten or darken the pixel to simulate a dirty surface, or mix it with a lightsource color to produce bump mapping effects.
As I said, Doom 3 uses these for a very cool effect: They use very high density meshes for their models and then recalculate them to a low-polygon representation to be used in the game, but in the process encode parts of the "roughness"/detail lost in a special texture map. The pixel shader calculates the "real" appearance based on this info (mostly by applying lightning/shadow calculation). The result is that the Doom3 models look very detailed and high-res (and ultimately scary :-) but aren't so much more complex than the average 3d model of today.
Pixel shader effects are going to be a basic requirement for any 3D game in the coming year. There is no way that you will be able to go without them.
