Having C syntax, it is even more complicated to see the limit. I thought Nvidia/Microsoft is a bit more foreward looking
Looking forward to hardware that's been available for over a year? That doesn't make much sense...
Now we have to change the language in a year or so once more.
No, that's exactly the opposite of the case. New profiles will need to be created, but the language won't need to change. Profiles implement subsets of a very general programming language (the full Cg spec is functionally equivalent to C). Future profiles will expose additional capabilities of the language (e.g., variable loops, longer maximum programs), but a new language will not be necessary.
and what role Cg is trying to fulfill in the grand scheme of things, in a programmer's point of view,
Write an 80 instruction vertex shader and bind a bunch of incoming vertex data channels into the appropriate attribute registers. The current model is a pain in the ass (especially to optimize and debug longer shaders). Now, imagine instead of 80 lines of vertex shader assembly, you had 200+ lines of fragment shader assembly, and in addition to input data such as texture coordinates and colors, you also have a ton of textures to use.
Using cryptic hardware-centric notation (such as glActiveTextureUnit, or DirectX 8's vertex shader header strings) becomes an extreme chore in these cases -- enough to warrant avoiding including such shaders in programs (especially lower-budget programs).
In addition, art flow for shaders has been poorly managed, as the tools (Maya, 3DS Max, etc.) don't map to hardware well at all, and re-mapping Maya materials onto DX8 pixel shaders takes a bit of programmer work for every model, creating a large stall in the pipeline. Putting the same shader technology in the content creation packages as the game code allows the artist to tweak effects to his/her heart's content without needing much, if any, programmer involvement.
the final release will allow you to write multipass,
As shown in the slideshow, CgFX (toolkit 2.0 release) will simplify the process of creating multipass effects; however, each pass will probably need a unique shader written for it, rather than 1 long shader being automatically compiled into smaller passes.