Tips on TCP socket to postgresql middleware

[eugene]

On Saturday, 19 February 2022 at 20:13:01 UTC, Chris Piker wrote:
> In general it should buffer data in RAM to avoid exerting back 
> pressure on the input socket and to allow for dropped 
> connections to the PG database.

If I get it right you want to restore connection
if it was closed by server for some reason.

I use special SM for that purpose, see [this 
picture](http://zed.karelia.ru/0/e/db-link.jpg)

In each state where this SM has to send/recv data, it takes
sending/receiving SM from a pool and commands them to
perform the task. Upon reaching IDLE state this machine
send some messsge to the user (another SM) of the connection
and seats in this state until the user detects connection lost
(in which case it sends M2 to DB-LINK SM). Then DB-LINK
goes to WAIT state, where it starts a timer and when it expires,
it goes to CONN state, where it tries to reconnect (using
sending SM - when connection is ready we get POLLOUT on socket).

You can have as many such connectors as you want,
so you have multiple connections within single thread.

I often use two connections, one for perform main task
(upload some data and alike) and the second for getting
notifications from PG, 'cause it very incovinient to
do both in a single connection.