Re: Extensibility of the PostgreSQL wire protocol

On 2/19/21 12:18 PM, Damir Simunic wrote:
>
>> On 19 Feb 2021, at 14:48, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>>
>> For example, there has been discussion elsewhere about integrating connection pooling into the server itself. For that, you want to have a custom process that listens for incoming connections, and launches backends independently of the incoming connections. These hooks would not help with that.
>>
>
> Not clear how the connection polling in the core is linked to discussing pluggable wire protocols.

It isn't per se. But there are things pluggable wire protocols can help
with in regards to connection pooling. For example a connection pool
like pgbouncer can be configured to switch client-backend association on
a transaction level. It therefore scans the traffic for the in
transaction state. This however only works if an application uses
identical session states across all connections in a pool. The JDBC
driver for example only really prepares PreparedStatements after a
number of executions and then assigns a name based on a counter to them.
So it is neither guaranteed that a certain backend has the same
statements prepared, nor that they are named the same. Therefore JDBC
based applications cannot use PreparedStatements through pgbouncer in
transaction mode.

An "extended" libpq protocol could allow the pool to give clients a
unique ID. The protocol handler would then maintain maps with the SQL of
prepared statements and what the client thinks their prepared statement
name is. So when a client sends a P packet, the protocol handler would
lookup the mapping and see if it already has that statement prepared.
Just add the mapping info or actually create a new statement entry in
the maps. These maps are of course shared across backends. So if then
another client sends bind+execute and the backend doesn't have a plan
for that query, it would internally create one.

There are security implications here, so things like the search path
might have to be part of the maps, but those are implementation details.

At the end this would allow a project like pgbouncer to create an
extended version of libpq protocol that caters to the very special needs
of that pool.

Most of that would of course be possible on the pool side itself. But
the internal structure of pgbouncer isn't suitable for that. It is very
lightweight and for long SQL queries may never have the complete 'P'
message in memory. It would also not have direct access to security
related information like the search path, which would require extra
round trips between the pool and the backend to retrieve it.

So while not suitable to create a built in pool by itself, loadable wire
protocols can definitely help with connection pooling.

I also am not sure if building a connection pool into a background
worker or postmaster is a good idea to begin with. One of the important
features of a pool is to be able to suspend traffic and make the server
completely idle to for example be able to restart the postmaster without
forcibly disconnecting all clients. A pool built into a background
worker cannot do that.

Regards, Jan

--
Jan Wieck
Principle Database Engineer
Amazon Web Services