Re: Separate connection handling from backends

On 12/5/16 2:14 PM, David Fetter wrote:
>> One solution to this would be to segregate connection handling from actual
>> backends, somewhere along the lines of separating the main loop from the
>> switch() that handles libpq commands. Benefits:
> [interesting stuff elided]
>
> What do you see as the relationship between this proposal and the
> earlier one for admission control?
>
> https://www.postgresql.org/message-id/4B38C1C5020000250002D9A5@gw.wicourts.gov

Without having read the paper reference in that email or the rest of the 
thread...

I think my proposal would completely eliminate the need for what Kevin 
proposed as long as the "connection" layer released the backend that it 
was using as soon as possible (namely, as soon as the backend was no 
longer in a transaction). This does assume that the connection layer is 
keeping a copy of all user/session settable GUCs. I don't think we need 
that ability in the first pass, but it would be very high on the desired 
feature list (because it would allow "transaction-level" pooling).

Actually, we could potentially do one better... if a backend sat idle in 
transaction for long enough, we could "save" that transaction state and 
free up the backend to do something else. I'm thinking this would be 
similar to a prepared transaction, but presumably there'd be some 
differences to allow for picking the transaction back up.

One big difference from what Kevin describe though: I don't think it 
makes sense for the connection layer to be able to parse queries. I 
suspect it would take a very large amount of work to allow something 
that's not a full-blown backend to parse, because it needs access to the 
catalogs. *Maybe* it'd be possible if we used a method other than 
ProcArray to register the snapshot that required, but you'd still have 
to duplicate all the relcache stuff.

BTW, it just occurred to me that having this separation would make it 
relatively easy to support re-directing DML queries from a replica to 
the master; if the backend throws the error indicating you tried to 
write data, the connection layer could re-route that.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)