I've started reviewing this patch and experimenting with it, so let me share some initial thoughts.
1) not handling session state (yet)
I understand handling session state would mean additional complexity, so I'm OK with not having it in v1. That being said, I think this is the primary issue with connection pooling on PostgreSQL - configuring and running a separate pool is not free, of course, but when people complain to us it's when they can't actually use a connection pool because of this limitation.
So what are your plans regarding this feature? I think you mentioned you already have the code in another product. Do you plan to submit it in the pg13 cycle, or what's the plan? I'm willing to put some effort into reviewing and testing that.
I too would like to see the plan of how to make this feature complete.
My concern here is that for the pgjdbc client at least *every* connection does some set parameter so I see from what I can tell from scanning this thread pooling would not be used at all.I suspect the .net driver does the same thing.
FWIW it'd be nice to expose it as some sort of interface, so that other connection pools can leverage it too. There are use cases that don't work with a built-in connection pool (say, PAUSE/RESUME in pgbouncer allows restarting the database) so projects like pgbouncer or odyssey are unlikely to disappear anytime soon.
Agreed, and as for other projects. I see their value in having the pool on a separate host as being a strength. I certainly don't see them going anywhere soon. Either way having a unified pooling API would be a useful goal.
I also wonder if we could make it more permissive even in v1, without implementing dump/restore of session state.
Consider for example patterns like this:
BEGIN; SET LOCAL enable_nestloop = off; ... COMMIT;