IMO as games development seems to focus more and more on console technology, it won't be that big of a problem. Developers will get more and more efficient.
512 MB is actually a lot to work with on the PS3 (compared to previous consoles vs PC specs of the time), but just as important is the bandwidth and default harddrive which allows for caching and results into a lot more memory for intelligently streaming games engines within independent game sections.
Executable code is usually tiny compared to the size of graphics and audio. Luckily most graphics and audio are very suitable for streaming. The texture data don't have to fit into the GDDR3 memory, actually by using the XDR Ram as well this adds a lot of potential bandwidth.
The PS3 has been designed to be a streaming monster, meaning lots of data for future games will flow through its architecture instead of having all the data in memory at once. (Resistance: Fall of Man and Motorstorm are examples of games which only streamed their lossless 7.1 audio, but the rest was all in memory at once for each track or level). The better quality (or more diverse) the assets that are being streamed become the more space will be required and so the storage space of Blu-Ray is a huge benefit, as well as its predictable speed results (data which can be cached onto the harddrive for much better performance at sustained speeds)
People often compare the available console memory with PCs, but a direct comparison is flawed to begin with. Gaming PCs use Windows, Windows/DirectX eats up a lot of memory (in addition to CPU cycles), especially Vista demands memory for itself, consoles can be far more efficient. Windows gets more demanding with every major new release, new console firmwares however will become more and more efficient.
What I think you will see is that at some point you won't see higher quality textures due to bandwidth/RAM bottlenecks, with regard to executable code and streaming audio there's enough RAM and bandwidth to push the Cell/RSX towards full potential.
Take for example the Neo Geo:
Main RAM: 64KB
Sound RAM: 2KB
Video RAM: 128KB
http://uk.youtube.com/watch?v=fqb0ea31fUI
As you can see the Neo Geo was much better at games than 1990 PCs sporting many multiple times its amount of available RAM. This could only be achieved due to the ultra fast circulation of data from ROMs (internal and on game cartridges).
Developers just have to step away from PC developing methods. In the past it was more convenient for developers to demand PC gamers to further upgrade their PCs instead of performing better optimizations. The average PC gamer is horribly misinformed with regard to technology, so this could even be beneficial to sales, if a game demanded twice the RAM and processor performance, the game must be twice as impressive, right? 
Luckily the market is changing, the more demanding games are more and more styled with a console mentality in mind. Some legacy engines may however not be really suitable for full redesign.
