The current megapixel leader in digital cameras is the new Kodak 50 megapixel sensor, built in the 48 x 36mm form factor. Basically, we're talking about a sensor that's twice the size of a full-frame 35mm dSLR sensor and can be used in the 6x6 and 645 medium format cameras (where the film size is either 56mm x 41.5mm or 56mm x 56mm) without any major problems, plus the high-end lens manufacturers have made some stunningly high quality view camera lenses matched to these modern sensors.
If you figure the size of each pixel, it works out to be about 0.0058 mm wide. Meanwhile, the popular 12 megapixel 1/1.7" sensors have reached the breathtakingly small width of 0.0019 mm. I don't think most folks understand exactly how absurd these 12 megapixel sensors really are until you start making real apples-to-apples comparisons.
What happened if you made a sensor with the same pixel pitch as a 12 megapixel 1/1.7"sensor? Well, in the 48x36 form factor we're really talking about a 500 megapixel sensor. Or, in APS-C terms, a mere 100 megapixels.
This is absurd. I have little doubt that if Kodak cranked out one of these super-high-resolution sensors right now, at least somebody would buy it.
You won't realize just how absurd this is until you start to characterize the properties of a point-and-shoot camera. Now, I've got better things to do than be a measurebator, but I was bored and had my camera handy one time and started taking a sequence of shots of San Francisco in different apertures on my G7 so that I could run a comparison. What I found is that the sharpest aperture of the G7 is f/4. If I stop down any farther, it starts to get blurry while viewing 100% zoomed in. And if you work out airy disks for given apertures and compare that to pixel size, you realize that this shouldn't be a surprise at all.
And this isn't much of a problem, given that you have to work really hard to get any semblance of depth of field on a point-and-shoot camera because of the tiny sensor and correspondingly short focal length lenses. But can you imagine trying to shoot at f/4 all the time on a medium format camera? There's just not enough depth of field to take good shots without hitting the diffraction limit, even if you've got movements, for most shots you'd want to take.
Now, I think there is a reasonable market to be made for a digital SLR that has somewhat less crazy resolution than this. Maybe a 25 megapixel APS-C sensor. It'll look nasty at high ISOs, but most good wildlife pictures have been taken with film that has, at most, ISO 100. More likely either ISO 25 Kodachrome or ISO 50 Velvia.
I also suspect that with a properly high-end lens, most likely configured as a view camera so that you can get enough motions to shoot at a high aperture and mounted on a tripod, a chunk of professionals would be able to take advantage of it and take high resolution shots on a fairly compact camera.
But I also know that when such a camera is introduced, it will most likely be picked up in bulk by measurebators, replace perfectly good 10-12 megapixel bodies in the camera lineup, and be used in such a way that most of the shots taken are no better than what you'd get from a 10-12 megapixel sensor and probably worse.
DPreview did something interesting recently. They started listing pixel area on their site. So, better point and shoot cameras are really only a matter of some marketing group running with it. You don't even need to go all the way to APS-C sensor sizes to get improved noise and dynamic range. I suspect there's a size somewhere between 2/3" and 4/3" at 8-10 megapixels that will give you a visible improvement in quality and noise. The trick is to change the game by advertising things like "Bigger pixels for lower noise!" or "Biggest pixels of all 10 megapixel cameras!"
Now, I was quite fascinated to be proved wrong. I was under the impression that diffraction was an inherent optical property, but it turns out that you can get around it, at least under some circumstances and with some careful optical designs using metamaterials.
But, in the end, it doesn't really change much for me. I am fairly convinced that there will be a stage of camera design, off in the future, that will completely break the mold of a camera that we've been accustomed to.
You can put a pinhole atop each pixel on the sensor. Or make a microlens array that lets you capture more of the scene at different focal lengths. Or maybe a liquid microlens atop each pixel. I really don't know, but there's tons of room for exploration and I doubt anybody's going to be too interested in funding much research in applying the state of the art in whacked-out-optics to digital cameras until they have to do things other than add 2 more megapixels to an existing design and then trumpet it as a great advance.