In high resolution planetary imaging there is a simple rule of thumb for capturing all the details that your optical system is capable of delivering. Use Nyquist Critical sampling, 2 pixels across Full Width at Half Max (FWHM). For small telescopes the FWHM is determined by the diffraction limit (the FWHM of the Airy disc), for larger telescopes it is determined by atmospheric seeing. For diffraction, Nyquist Sampling is achieved when the focal ratio (f-number) is 4 times the pixel width in microns (roughly). Lens is diffraction-limited at f8? Use pixels that are 2 microns wide. In practical planetary imaging one changes the focal ratio (using tele-extenders) to match one's pixels, rather than the other way around. Eg the Canon 600mm/4.0L IS lens is almost good enough to be called diffraction-limited on-axis. So, to Nyquist-sample it, one needs to tele-extend it so that it operates at f24 on 6micron pixels (I achieved f28 by using a 5x tele-extender + a 1.4x). Compares quite well with an astronomical telescope when shooting Saturn, comparo given here:
http://www.samirkharusi.net/televue_canon.html
Discussion on Nyquist sampling in planetary imaging given here, with examples:
http://samirkharusi.net/sampling_saturn.html
These principles have long been well established. So, the "ultimate" smallest useful pixel size, based purely on diffraction, will be roughly 2microns for lenses diffraction-limted at f8. That's around 200 megapixels on a 35mm format chip. We have a very, very long way to go, and that's for f8... When pixels get cheap, the rules change to overkill, and overkill for f8 diffraction-limited optics begin at about 200 megapixels on 35mm format. Will we ever get there? Do people actually "need" 200 megapixels to achieve their desired print sizes? A very few, yes. For most one would expect something under 50megapixels as adequate for A4 or A3 prints. Obviously for them, the vast majority, chips smaller than 35mm format, combined with superb, smaller lenses will make more sense. Will prints continue to be the end-game for consumers? Dunno. Perhaps an HD TV display (2 megapixels, achievable by a camera phone) will be good enough for Joe Public.
Photo lenses aren't telescopes. The example based on a Canon 600mm f/4 is good, but most photolenses aren't telephoto designs either. Wide-angle lenses, zooms, macro lenses, etc. put many problems on the table of the lens designer. Even more if those retrofocus or vario designs have to be "fast", considering size, cost and operation (AF) constraints. On the other hand, the type of detail and the capture device are very important. A bayer sensor introduces several constraints. Typical low contrast detail in photographs isn't like bright spots on a dark background. Etc.
A 200MP sensor is a possibility... sometime, in the future. But right now, say in a 2 years timeframe, what can we expect? In the photographic industry, I think the 35mm format will bring more resolution to the sensors. The MF marks are the point of reference here. Time ago 22MP was the exclusive territory of MF cameras, and then Canon jumped into the battle. So I expect competition from the 35mm format in the 33-39Mp domain, the current exclusive territory of MF cameras (from Canon at least). Does 50MP or 60MP cameras make any sense? Considering prints, yes, but only for a few professionals. It is a very small market, very, very small. Alternatives to prints? Web? TV? Cinema? Even lower resolution is needed there!
So, there are cost (supply) variables at play, technical considerations (like diffraction), but also demand considerations. For large parts of the market, professional photographic market (reportage, fashion, advertising)... How much is needed even considering a wide margin? A professional buys a Hasselblad 50MP if it makes some difference. So I think there is a near limit due to practical reasons based on demand considerations, not technical reasons. The argument is: we can increase pixels at no cost, free, so why not to do it? All we had this discussion years ago, but now the situation is different. Currently we have 50-60Mp cameras, and 20Mp cameras are normal in the prosumer segment (Canon 5D Mark II, Sony A900). So we are talking of further increases... The point is that it only does a marginal difference for the "product" the professionals sell to the clients (photos), so the industry will look for alternative ways of providing better tools, for a price. Maybe not more pixels, but the same number of pixels with more quality, more detail per pixel (the Bayer mosaic!), etc.