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

On Mon, Sep 10, 2012 at 11:12 AM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> 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.

That's kind of the reason why I suggested up thread tring to decouple
the *starting* of the backend with the "options" to PQ connect...

A "Helper function" in libpq could easily start the backend, and
possibly return a conninfostring to give PQconnectdb...

But if they are decoupled, I could easily envision an app that
"pauses" it's use of the backend to allow some other libpq access to
it for a period.

You'd have to "trust" whatever else you let talk on the FD to the
backend, but it might be useful...
--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.