Pros: 1. Simplified memory model: no need in DSM, shm_mq, DSA, etc
shm_mq would remain useful, and the others could only be dropped if you also dropped process-model support entirely.
1. Breaks compatibility with existed extensions and adds more requirements for authors of new extension
Depends on how much frightening preprocessor magic you're willing to use, doesn't it? ;)
Wouldn't be surprised if simple extensions (C functions etc) stayed fairly happy, but it'd be hazardous enough in terms of library use etc that deliberate breakage may be beter.
2. Problems with integration of single-threaded PLs: Python, Lua,...
Yeah, that's going to hurt. Especially since most non-plpgsql code out there will be plperl and plpython. Breaking that's not going to be an option, but nobody's going to be happy if all postgres backends must contend for the same Python GIL. Plus it'd be deadlock-city.
That's nearly a showstopper right there. Especially since with a quick look around it looks like the cPython GIL is per-DLL (at least on Windows) not per-interpreter-state, so spawning separate interpreter states per-thread may not be sufficient. That makes sense given that cPython its self is thread-aware; otherwise it'd have a really hard time figuring out which GIL and interpreter state to look at when in a cPython-spawned thread.
3. Worser protection from programming errors, included errors in extensions.
Mainly contaminating memory of unrelated procesess, or the postmaster.
I'm not worried about outright crashes. On any modern system it's not significantly worse to take down the postmaster than it is to have it do its own recovery. A modern init will restart it promptly. (If you're not running postgres under an init daemon for production then... well, you should be.)
4. Lack of explicit separation of shared and privite memory leads to more synchronization errors.
Accidentally clobbering postmaster memory/state would be my main worry there.
Right now we gain a lot of protection from our copy-on-write shared-nothing-by-default model, and we rely on it in quite a lot of places where backends merrily stomp on inherited postmaster state.
The more I think about it, the less enthusiastic I am, really.
