Re: Built-in connection pooling

On 27.04.2018 18:33, Merlin Moncure wrote:
> On Fri, Apr 27, 2018 at 10:05 AM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> On 27.04.2018 16:49, Merlin Moncure wrote:
>>> *) How are you pinning client connections to an application managed
>>> transaction? (IMNSHO, this feature is useless without being able to do
>>> that)
>> Sorry, I do not completely understand the question.
>> Rescheduling is now done at transaction level - it means that backand can
>> not be switched to other session until completing current transaction.
>> The main argument for transaction level pooling is that it allows not worry
>> about heavy weight locks, which are associated with procarray entries.
> I'm confused here...could be language issues or terminology (I'll look
> at your latest code). Here is how I understand things:
> Backend=instance of postgres binary
> Session=application state within postgres binary (temp tables,
> prepared statement etc)
> Connection=Client side connection
Backend is a process, forked by postmaster.

> AIUI (I could certainly be wrong), withing connection pooling, ratio
> of backend/session is still 1:1. The idea is that client connections
> when they issue SQL to the server reserve a Backend/Session, use it
> for the duration of a transaction, and release it when the transaction
> resolves. So many client connections share backends. As with
> pgbouncer, the concept of session in a traditional sense is not really
> defined; session state management would be handled within the
> application itself, or within data within tables, but not within
> backend private memory. Does that align with your thinking?
No. Number of sessions is equal to number of client connections.
So client is not reserving "Backend/Session" as it happen in pgbouncer.
One backend keeps multiple sessions. And for each session it maintains
session context which included client's connection.
And it is backend's decision transaction of which client it is going to
execute now.
This is why built-in pooler is able to provide session semantic without
backend/session=1:1 requirement.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company