Re: Extensibility of the PostgreSQL wire protocol

On Fri, Feb 19, 2021 at 8:48 AM Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:

> With the hooks that exist today, would it possible to write a background
> worker that listens on a port, instead of postmaster? Can you launch
> backends from a background worker? And communicate the backend processes
> using a shared memory message queue (see pqmq.c).
>

Yes. That's similar to how mine work: A background worker that acts as a
listener for the new protocol which then sets up a new dynamic background
worker on accept(), waits for its creation, passes the fd to the new
background worker, and sits in a while (!got_sigterm) loop checking the
socket for activity and running the protocol similar to postmaster. I
haven't looked at the latest connection pooling patches but, in general,
connection pooling is an abstract issue and should be usable for any type
of connection as, realistically, it's just an event loop and state problem
- it shouldn't be protocol specific.

I would recommend this approach: write a separate program that sits
> between the client and PostgreSQL, speaking custom protocol to the
> client, and libpq to the backend. And then move that program into a
> background worker process.
>

Doing protocol conversion between libpq and a different protocol works, but
is slow. My implementations were originally all proxies that worked outside
the database, then I moved them inside, then I replaced all the libpq code
with SPI-related calls.

> > In a recent case, letting the browser talk directly to the database
> > allowed me to get rid of a ~100k-sloc .net backend and all the
> > complexity and infrastructure that goes with
> > coding/testing/deploying/maintaining it, while keeping all the
> > positives: per-query compression/data conversion, querying multiple
> > databases over a single connection, session cookies, etc. Deployment
> > is trivial compared to what was before. Latency is down 2x-4x across
> > the board.
>
> Querying multiple databases over a single connection is not possible
> with the approach taken here. Not sure about the others things you listed.
>

Accessing multiple databases from the same backend is problematic overall -
I didn't solve that in my implementations either. IIRC, once a bgworker is
attached to a specific database, it's basically stuck with that database.

--
Jonah H. Harris