Re: Proof of concept: standalone backend with full FE/BE protocol

On Sun, Sep 2, 2012 at 8:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Notably, while the lack of any background processes is just what you want
> for pg_upgrade and disaster recovery, an ordinary application is probably
> going to want to rely on autovacuum; and we need bgwriter and other
> background processes for best performance. So I'm speculating about
> having a postmaster process that isn't listening on any ports, but is
> managing background processes in addition to a single child backend.
> That's for another day though.
>

Since we are forking a child process anyway, and potentially other
auxiliary processes too, would it make sense to allow multiple backends too
(allow multiple local applications connect to this instance)? I believe (I
may be wrong) that embedded databases (SQLLite et. al.) use a library
interface, in that the application makes a library call and waits for that
API call to finish (unless, of course, the library supports async
operations or the application uses threads). The implementation you are
proposing uses socket communication, which lends itself very easily to
client-server model, and if possible, it should be leveraged to provide for
multiple applications talking to one local DB.

I have this use case in mind: An application is running using this
interface, and an admin now wishes to do some maintenance, or inspect
something, so they can launch local pgAdmin using the same connection
string as used by the original application. This will allow an admin to
perform tuning, etc. without having to first shutdown the application.

Here's how this might impact the design (I may very well be missing many
other things, and I have no idea if this is implementable or not):

.) Database starts when the first such application is launched.
.) Database shuts down when the last such application disconnects.
.) Postgres behaves much like a regular Postgres installation, except that
it does not accept connections over TCP/IP or Unix Doamin Sockets.
.) The above implies that we use regular Postmaster machinery, and not the
--sinlgle machinery.
.) Second and subsequent applications use the postmaster.pid (or something
similar) to find an already running instance, and connect to it.
.) There's a race condition where the second application is starting up,
hoping to connect to an already running insatnce, but the first application
disconnects (and hence shuts down the DB) before the second one can
successfully connect.

I haven't thought much about the security implications of this yet. Maybe
the socket permissions would restrict an unauthorized user user from
connecting to this instance.

--
Gurjeet Singh

http://gurjeet.singh.im/