Re: Middleware Messages for FE/BE - Mailing list pgsql-hackers

Hi Simon,

On 8/19/21 4:33 AM, Simon Riggs wrote:
> The current FE/BE protocol assumes that the client is talking to the
> server directly. If middleware wants to do something like load
> balancing, the only current option is to inspect the incoming commands
> and take action from that. That approach performs poorly and has
> proven difficult to maintain, limiting the functionality in Postgres
> ecosystem middleware.
>
> It would be useful to have a way to speak to middleware within a
> session or prior to each command. There are ways to frig this and
> obviously it can always be done out-of-core, but there is a clear
> requirement for various client tools to agree a standard way for them
> to send messages to middleware rather than the database server. If we
> get PostgreSQL Project approval for this, then authors of client and
> middleware tools will know how to interoperate and can begin adding
> features to take advantage of this, allowing the Postgres ecosystem to
> improve and extend its middleware.
>
> Byte1('M')
> Identifies the message as a middleware message. (Or perhaps use 'U'
> for User Message?).
>
> Int32
> Length of message contents in bytes, including self.
>
> Int64
> Routing/class identifier, allowing middleware to quickly ignore whole
> classes of message if not appropriate. We would run some kind of
> allocation scheme to ensure unique meaning, probably via the Postgres
> Wiki. The first 2 billion values would be reserved for allocation by
> the PostgreSQL Project itself, values beyond that open for external
> allocation.
>
> Byten
> The message itself, where n is the remaining bytes in the message.
>
> The message is intended for middleware only. The server always ignores
> these messages, with an optional debug facility that can be enabled to
> allow printing NOTICEs to allow testing.
>
> I will supply a patch to any agreed message format, together with a
> new libpq call to utilise this.
>
> In summary: the patch is easy, the point is we need agreement to allow
> and encourage interoperation between clients and middleware.
>
> Thoughts?
>

I would say that this is a PostgreSQL protocol v4 thing, as there is a 
bit more to it.


There is a need to trigger middleware functionality, but you need to 
query the middleware stack first on what it supports - failover, load 
balancing, ... And, what type of language is that ? SQL query ? Not all 
middleware support general SQL queries.


Once you have established what is supported and what isn't you need the 
client driver (libpq is the very easy part) - so lets include Java, C#, 
Rust, golang, ... - to understand their environment to trigger the 
correct message in the configured scenario. F.ex. how is multiple 
application clusters connecting to the same middleware instance 
(connection pool) going to coordinate their 'M' message ?


If you are looking to configure the middleware instance then we can just 
use the existing protocol with FE/Q; that said pgagroal has its own 
binary protocol for admin payload.


Could you expand on typical scenarios that you want to see implemented ?


Best regards,

  Jesper