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

Attached is a proof-of-concept patch for talking to a standalone backend
using libpq and a pair of pipes. It works, to the extent that psql and
pg_dump can run without any postmaster present:

$ psql regression
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
$ psql "standalone_datadir = $PGDATA dbname = regression"
psql (9.3devel)
Type "help" for help.

regression=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------------------------+----------+-------
public | a | table | tgl
public | a_star | table | tgl
public | abstime_tbl | table | tgl
public | aggtest | table | tgl
public | array_index_op_test | table | tgl
...

but there's quite a bit of work to do yet before this could be a
committable patch. Some notes:

1. As you can see above, the feature is triggered by specifying the new
connection option "standalone_datadir", whose value must be the location
of the data directory. I also invented an option "standalone_backend",
which can be set to specify which postgres executable to launch. If the
latter isn't specified, libpq defaults to trying the installation PGBINDIR
that was selected by configure. (I don't think it can use the relative
path tricks we use in pg_ctl and elsewhere, since there's no good reason
to assume that it's running in a Postgres-supplied program.) I'm not
particularly wedded to these names or the syntax, but I think this is the
basic functionality we'd need.

2. As far as the backend is concerned, use of FE/BE protocol rather than
traditional standalone mode is triggered by writing "--child" instead of
"--single" as the first argument on the postgres command line. (I'm not
wedded to that name either ... anybody have a better idea?)

3. The bulk of the changes have to do with the fact that we need to keep
track of two file descriptors not one. This was a bit tedious, but fairly
straightforward --- though I was surprised to find that send() and recv()
don't work on pipes, at least not on Linux. You have to use read() and
write() instead.

4. As coded, the backend assumes the incoming pipe is on its FD 0 and the
outgoing pipe is on its FD 1. This made the command line simple but I'm
having second thoughts about it: if anything inside the backend tries to
read stdin or write stdout, unpleasant things will happen. It might be
better to not reassign the pipe FD numbers. In that case we'd have to
pass them on the command line, so that the syntax would be something
like "postgres --child 4,5 -D pgdata ...".

5. The fork/exec code is pretty primitive with respect to error handling.
I didn't put much time into it since I'm afraid we may need to refactor
it entirely before a Windows equivalent can be written. (And I need
somebody to write/test the Windows equivalent - any volunteers?)

6. I didn't bother with passing anything except -D and the database name
to the standalone backend. Probably we'd like to be able to pass other
command-line switches too. Again, it's not clear that it's worth doing
much here until we have equivalent Windows code available.

7. I haven't tried to make pg_upgrade use this yet.

8. PQcancel needs some work - it can't do what it does now, but it could
do kill(conn->postgres_pid, SIGINT) instead. At least in Unix. I have no
idea what we'd do in Windows. This doesn't matter for pg_upgrade of
course, but it'd be important for manual use of this mode.

Although the immediate use of this would be for pg_upgrade, I think that
people would soon drop the traditional --single mode and do anything they
need to do in standalone mode using this method, since psql is so vastly
more user-friendly than a --single backend.

In the longer run, this could provide a substitute for the "embedded
database" mode that we keep saying we're not going to implement. That is,
applications could fire up a standalone backend as a child process and not
need a postmaster anywhere, which would be a lot more convenient for an
app that wants a private database and doesn't want to involve its users in
managing a Postgres server. However, there are some additional things
we'd need to think about before advertising it as a fit solution for that.
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.

Comments? Anyone want to have a go at fixing this for Windows?

regards, tom lane

On 03.09.2012 03:23, Tom Lane wrote:
> 1. As you can see above, the feature is triggered by specifying the new
> connection option "standalone_datadir", whose value must be the location
> of the data directory. I also invented an option "standalone_backend",
> which can be set to specify which postgres executable to launch. If the
> latter isn't specified, libpq defaults to trying the installation PGBINDIR
> that was selected by configure. (I don't think it can use the relative
> path tricks we use in pg_ctl and elsewhere, since there's no good reason
> to assume that it's running in a Postgres-supplied program.) I'm not
> particularly wedded to these names or the syntax, but I think this is the
> basic functionality we'd need.

Are there security issues with this? If a user can specify libpq
connection options, he can now execute any file he wants by passing it
as standalone_backend. Granted, you shouldn't allow an untrusted user to
specify libpq connection options, because allowing to open a TCP
connection to an arbitrary address can be a problem by itself, but it
seems like this might make the situation much worse. contrib/dblink
springs to mind..

> 3. The bulk of the changes have to do with the fact that we need to keep
> track of two file descriptors not one. This was a bit tedious, but fairly
> straightforward --- though I was surprised to find that send() and recv()
> don't work on pipes, at least not on Linux. You have to use read() and
> write() instead.

Would socketpair(2) be simpler?

> 7. I haven't tried to make pg_upgrade use this yet.

Hmm, pg_upgrade uses pg_dumpall rather than pg_dump, so it needs to
connect once per database. That means launching the standalone backend
multiple times. I guess that works, as long as pg_dumpall doesn't try to
hold multiple connections open at any one time.

- Heikki

Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> On 03.09.2012 03:23, Tom Lane wrote:
>> 1. As you can see above, the feature is triggered by specifying the new
>> connection option "standalone_datadir", whose value must be the location
>> of the data directory. I also invented an option "standalone_backend",
>> which can be set to specify which postgres executable to launch.

> Are there security issues with this? If a user can specify libpq
> connection options, he can now execute any file he wants by passing it
> as standalone_backend. Granted, you shouldn't allow an untrusted user to
> specify libpq connection options, because allowing to open a TCP
> connection to an arbitrary address can be a problem by itself, but it
> seems like this might make the situation much worse. contrib/dblink
> springs to mind..

Hmm, that's a good point. Maybe we should only allow the executable
name to come from an environment variable? Seems kinda klugy though.

>> 3. The bulk of the changes have to do with the fact that we need to keep
>> track of two file descriptors not one.

> Would socketpair(2) be simpler?

Hm, yes, but is it portable enough? It seems to be required by SUS v2,
so we're likely okay on the Unix side, but does Windows have this?

regards, tom lane

On Sun, Sep 02, 2012 at 11:34:42PM -0400, Tom Lane wrote:
> Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> > On 03.09.2012 03:23, Tom Lane wrote:
> >> 1. As you can see above, the feature is triggered by specifying the new
> >> connection option "standalone_datadir", whose value must be the location
> >> of the data directory. I also invented an option "standalone_backend",
> >> which can be set to specify which postgres executable to launch.
>
> > Are there security issues with this? If a user can specify libpq
> > connection options, he can now execute any file he wants by passing it
> > as standalone_backend. Granted, you shouldn't allow an untrusted user to
> > specify libpq connection options, because allowing to open a TCP
> > connection to an arbitrary address can be a problem by itself, but it
> > seems like this might make the situation much worse. contrib/dblink
> > springs to mind..
>
> Hmm, that's a good point. Maybe we should only allow the executable
> name to come from an environment variable? Seems kinda klugy though.

I don't think it's libpq's job to enforce security policy this way. In any
event, it has no reason to presume an environment variable is a safer source.

> >> 3. The bulk of the changes have to do with the fact that we need to keep
> >> track of two file descriptors not one.
>
> > Would socketpair(2) be simpler?
>
> Hm, yes, but is it portable enough? It seems to be required by SUS v2,
> so we're likely okay on the Unix side, but does Windows have this?

Windows does not have socketpair(), nor a strict pipe() equivalent. I expect
switching to socketpair() makes the Windows side trickier in some ways and
simpler in others. +1 for exploring that direction first.

Noah Misch <noah(at)leadboat(dot)com> writes:
> On Sun, Sep 02, 2012 at 11:34:42PM -0400, Tom Lane wrote:
>> Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>>> Are there security issues with this? If a user can specify libpq
>>> connection options, he can now execute any file he wants by passing it
>>> as standalone_backend.

>> Hmm, that's a good point. Maybe we should only allow the executable
>> name to come from an environment variable? Seems kinda klugy though.

> I don't think it's libpq's job to enforce security policy this way. In any
> event, it has no reason to presume an environment variable is a safer source.

One easy thing we could do that would at least narrow the risks is to
only allow the executable's *directory* to be specified, hardwiring the
executable file name to "postgres" (or "postgres.exe" I guess).

I'm inclined to think though that environment variables *are* more
secure in this context. In the contrib/dblink example, such a
restriction would certainly help a lot. In any case, we should at
least think of a way that an app using libpq can prevent this type
of attack short of parsing the conn string for itself.

regards, tom lane

Noah Misch <noah(at)leadboat(dot)com> writes:
> Windows does not have socketpair(), nor a strict pipe() equivalent. I expect
> switching to socketpair() makes the Windows side trickier in some ways and
> simpler in others. +1 for exploring that direction first.

A bit of googling suggests that emulating socketpair() on Windows is not
that hard: basically you create an accepting socket and connect to it.
Ugly I guess but likely to be nicer than emulating the two-pipes trick
exactly.

regards, tom lane

On Mon, Sep 03, 2012 at 12:11:20AM -0400, Tom Lane wrote:
> Noah Misch <noah(at)leadboat(dot)com> writes:
> > I don't think it's libpq's job to enforce security policy this way. In any
> > event, it has no reason to presume an environment variable is a safer source.
>
> One easy thing we could do that would at least narrow the risks is to
> only allow the executable's *directory* to be specified, hardwiring the
> executable file name to "postgres" (or "postgres.exe" I guess).

If the user has any interactive access to the machine, he could make a
/tmp/X/postgres symbolic link to the program of his choosing.

> I'm inclined to think though that environment variables *are* more
> secure in this context. In the contrib/dblink example, such a
> restriction would certainly help a lot.

dblink_connect() should only let superusers specify standalone_datadir at all;
normal users have no business manipulating other data directories visible to
the backend running dblink. For superusers, setting both standalone_datadir
and standalone_backend is fair game.

Trusting the environment over connection strings is a wrong policy for, say, a
setuid command-line program. Suppose such a program passes libpq a fixed
connection string to access its embedded database. The program will find
itself explicitly clearing this environment variable to regain safety.

> In any case, we should at
> least think of a way that an app using libpq can prevent this type
> of attack short of parsing the conn string for itself.

My recommendation to affected application authors is to pass the connection
string through PQconninfoParse(). Walk the array, validating or rejecting
arguments like "host" and "standalone_datadir". For maximum safety, the
application would need to reject any unanticipated parameters. We might
soften that by adding a "bool critical" field to PQconninfoOption that
documents whether the option must be understood by a program validating a
connection. Compare the idea of the PNG chunk header's "ancillary" bit.
Parameters like "host" and "standalone_datadir" would be critical, while
"application_name" and "connect_timeout" would be ancillary. But this is a
tough line to draw rigorously.

I was pondering the flexibility from letting the application fork/exec and
supply the client-side descriptor number(s) to libpq. Suppose it wants to
redirect the backend's stderr to a file. A single-threaded application would
temporarily redirect its own stderr while calling PQconnectdb(). In a
multithreaded application, that introduces a race condition when other threads
concurrently write to the normal stderr. By handling redirection in its own
fork/exec code, the application can achieve the outcome safely. Perhaps
offering this can wait until the feature constitutes a more general
embedded-database offering, though.

Thanks,
nm

On Mon, Sep 3, 2012 at 6:42 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Noah Misch <noah(at)leadboat(dot)com> writes:
>> Windows does not have socketpair(), nor a strict pipe() equivalent. I expect
>> switching to socketpair() makes the Windows side trickier in some ways and
>> simpler in others. +1 for exploring that direction first.
>
> A bit of googling suggests that emulating socketpair() on Windows is not
> that hard: basically you create an accepting socket and connect to it.
> Ugly I guess but likely to be nicer than emulating the two-pipes trick
> exactly.

That sounds a lot like what we were doing in pgpipe() before.. It was
removed in d2c1740dc275543a46721ed254ba3623f63d2204, but that's
because it was dead at the time. Do we need to bring it back?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

> 5. The fork/exec code is pretty primitive with respect to error handling.
> I didn't put much time into it since I'm afraid we may need to refactor it
entirely before a Windows equivalent can be > written. (And I need somebody
to write/test the Windows equivalent - any volunteers?)

I think part of the code for windows can be written by referring function
internal_forkexec(),
If you are okay, I can take up this. Please confirm.

> 8. PQcancel needs some work - it can't do what it does now, but it could
do kill(conn->postgres_pid, SIGINT) instead. > At least in Unix. I have no
idea what we'd do in Windows. This doesn't matter for pg_upgrade of course,
but it'd be
> important for manual use of this mode.

Can pgkill(int pid, int sig) API of PG be used to achieve the same on
Windows.

With Regards,
Amit Kapila.

Amit Kapila <amit(dot)kapila(at)huawei(dot)com> writes:
> I think part of the code for windows can be written by referring function
> internal_forkexec(),
> If you are okay, I can take up this. Please confirm.

Nobody else volunteered, so have at it. Note that I'm planning to redo
that code to use socketpair(), so possibly you want to wait to see that
before you do anything.

>> 8. PQcancel needs some work - it can't do what it does now, but it could
>> do kill(conn->postgres_pid, SIGINT) instead. At least in Unix. I have no
>> idea what we'd do in Windows. This doesn't matter for pg_upgrade of course,
>> but it'd be important for manual use of this mode.

> Can pgkill(int pid, int sig) API of PG be used to achieve the same on
> Windows.

Hmm, after looking at src/port/kill.c it doesn't seem like there's much
of a problem with doing that. I had had the idea that our kill
emulation only worked within the backend environment, but of course
pg_ctl wouldn't work if that were so. So this is easier than I thought.

regards, tom lane

On Mon, Sep 3, 2012 at 7:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Amit Kapila <amit(dot)kapila(at)huawei(dot)com> writes:
>>> 8. PQcancel needs some work - it can't do what it does now, but it could
>>> do kill(conn->postgres_pid, SIGINT) instead. At least in Unix. I have no
>>> idea what we'd do in Windows. This doesn't matter for pg_upgrade of course,
>>> but it'd be important for manual use of this mode.
>
>> Can pgkill(int pid, int sig) API of PG be used to achieve the same on
>> Windows.
>
> Hmm, after looking at src/port/kill.c it doesn't seem like there's much
> of a problem with doing that. I had had the idea that our kill
> emulation only worked within the backend environment, but of course
> pg_ctl wouldn't work if that were so. So this is easier than I thought.

Yeah, kill works fine from non-backend as long as the *receiver* has
our backend environment.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Mon, Sep 3, 2012 at 7:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Hmm, after looking at src/port/kill.c it doesn't seem like there's much
>> of a problem with doing that. I had had the idea that our kill
>> emulation only worked within the backend environment, but of course
>> pg_ctl wouldn't work if that were so. So this is easier than I thought.

> Yeah, kill works fine from non-backend as long as the *receiver* has
> our backend environment.

I have another question after thinking about that for awhile: is there
any security concern there? On Unix-oid systems, we expect the kernel
to restrict who can do a kill() on a postgres process. If there's any
similar restriction on who can send to that named pipe in the Windows
version, it's not obvious from the code. Do we have/need any
restriction there?

regards, tom lane

On Mon, Sep 3, 2012 at 8:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> On Mon, Sep 3, 2012 at 7:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Hmm, after looking at src/port/kill.c it doesn't seem like there's much
>>> of a problem with doing that. I had had the idea that our kill
>>> emulation only worked within the backend environment, but of course
>>> pg_ctl wouldn't work if that were so. So this is easier than I thought.
>
>> Yeah, kill works fine from non-backend as long as the *receiver* has
>> our backend environment.
>
> I have another question after thinking about that for awhile: is there
> any security concern there? On Unix-oid systems, we expect the kernel
> to restrict who can do a kill() on a postgres process. If there's any
> similar restriction on who can send to that named pipe in the Windows
> version, it's not obvious from the code. Do we have/need any
> restriction there?

We use the default for CreateNamedPipe() which is:
" The ACLs in the default security descriptor for a named pipe grant
full control to the LocalSystem account, administrators, and the
creator owner. They also grant read access to members of the Everyone
group and the anonymous account."
(ref: http://msdn.microsoft.com/en-us/library/windows/desktop/aa365150(v=vs.85).aspx)

Given that we only respond to writes (we don't "publish information"
over it), I think that's a reasonable default to use.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Mon, Sep 3, 2012 at 8:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I have another question after thinking about that for awhile: is there
>> any security concern there? On Unix-oid systems, we expect the kernel
>> to restrict who can do a kill() on a postgres process. If there's any
>> similar restriction on who can send to that named pipe in the Windows
>> version, it's not obvious from the code. Do we have/need any
>> restriction there?

> We use the default for CreateNamedPipe() which is:
> " The ACLs in the default security descriptor for a named pipe grant
> full control to the LocalSystem account, administrators, and the
> creator owner. They also grant read access to members of the Everyone
> group and the anonymous account."
> (ref: http://msdn.microsoft.com/en-us/library/windows/desktop/aa365150(v=vs.85).aspx)

Hm. The write protections sound fine ... but what's the semantics of
reading, is it like Unix pipes? If so, couldn't a random third party
drain the pipe by reading from it, and thereby cause signals to be lost?

regards, tom lane

Noah Misch <noah(at)leadboat(dot)com> writes:
> On Mon, Sep 03, 2012 at 12:11:20AM -0400, Tom Lane wrote:
>> One easy thing we could do that would at least narrow the risks is to
>> only allow the executable's *directory* to be specified, hardwiring the
>> executable file name to "postgres" (or "postgres.exe" I guess).

> If the user has any interactive access to the machine, he could make a
> /tmp/X/postgres symbolic link to the program of his choosing.

I said "narrow", not "eliminate" ;-)

>> I'm inclined to think though that environment variables *are* more
>> secure in this context. In the contrib/dblink example, such a
>> restriction would certainly help a lot.

> Trusting the environment over connection strings is a wrong policy for, say, a
> setuid command-line program. Suppose such a program passes libpq a fixed
> connection string to access its embedded database. The program will find
> itself explicitly clearing this environment variable to regain safety.

Well, a program that was concerned with this would *already* want to
clear every environment variable that affects libpq, else its database
connections could get redirected somewhere surprising. libpq already
provides enough infrastructure to get the list of such variables ... but
only ones that are associated with connection string parameters. So for
this purpose, making the standalone-control parameters not be accessible
through connection strings would actually be worse.

>> In any case, we should at
>> least think of a way that an app using libpq can prevent this type
>> of attack short of parsing the conn string for itself.

> My recommendation to affected application authors is to pass the connection
> string through PQconninfoParse(). Walk the array, validating or rejecting
> arguments like "host" and "standalone_datadir". For maximum safety, the
> application would need to reject any unanticipated parameters. We might
> soften that by adding a "bool critical" field to PQconninfoOption that
> documents whether the option must be understood by a program validating a
> connection. Compare the idea of the PNG chunk header's "ancillary" bit.
> Parameters like "host" and "standalone_datadir" would be critical, while
> "application_name" and "connect_timeout" would be ancillary. But this is a
> tough line to draw rigorously.

Even more to the point, we can't seriously expect application authors to
know what to do with connection parameters that didn't exist when they
were writing their code. Every new security-critical parameter would
represent a new set of bugs.

I'm reluctantly coming to the conclusion that we can't pass these
parameters through the regular libpq connection string mechanism, and
will have to invent something else. That's awfully nasty though;
it will pretty much cripple the idea that this would be a simple way to
invoke a quasi-embedded variant of Postgres.

regards, tom lane

On 09/03/2012 04:23 PM, Tom Lane wrote:
> I'm reluctantly coming to the conclusion that we can't pass these
> parameters through the regular libpq connection string mechanism, and
> will have to invent something else. That's awfully nasty though;
> it will pretty much cripple the idea that this would be a simple way to
> invoke a quasi-embedded variant of Postgres.
>
>

That would be a bit sad. BTW, how would this work for things like
auto_vacuum, logging_collector and so on?

cheers

andrew

Hi,

On Monday, September 03, 2012 10:23:52 PM Tom Lane wrote:
> I'm reluctantly coming to the conclusion that we can't pass these
> parameters through the regular libpq connection string mechanism, and
> will have to invent something else. That's awfully nasty though;
> it will pretty much cripple the idea that this would be a simple way to
> invoke a quasi-embedded variant of Postgres.
What I am asking myself is: why does that have to go through the normal
PQconnectdb* api? This is something completely new and very well might grow
more features that are not necessarily easy to press into PQconnectdb().

PQServer
PQstartServer(const char **keywords, const char **values);

or whatever seems to be more future proof especially considering the
possibility that this will grow into something more featureful.

Greetings,

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 09/03/2012 04:23 PM, Tom Lane wrote:
>> I'm reluctantly coming to the conclusion that we can't pass these
>> parameters through the regular libpq connection string mechanism, and
>> will have to invent something else. That's awfully nasty though;
>> it will pretty much cripple the idea that this would be a simple way to
>> invoke a quasi-embedded variant of Postgres.

> That would be a bit sad. BTW, how would this work for things like
> auto_vacuum, logging_collector and so on?

There's already an "options" connection-string option for passing random
GUC settings through to the connected backend. My original plan was to
just add any such settings to the standalone backend's command line.
In this context that would work for postmaster-level GUCs. However,
if we're going to redesign this then maybe something else is more
appropriate.

regards, tom lane

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On Monday, September 03, 2012 10:23:52 PM Tom Lane wrote:
>> I'm reluctantly coming to the conclusion that we can't pass these
>> parameters through the regular libpq connection string mechanism, and
>> will have to invent something else. That's awfully nasty though;
>> it will pretty much cripple the idea that this would be a simple way to
>> invoke a quasi-embedded variant of Postgres.

> What I am asking myself is: why does that have to go through the normal
> PQconnectdb* api? This is something completely new and very well might grow
> more features that are not necessarily easy to press into PQconnectdb().

Well, what that's mostly going to result in is a huge amount of
duplication :-(. psql, pg_dump, and anything else that wants to support
this will need some alternative command line switch and an alternative
code path to call PQstartServer. I'd hoped to avoid all that. Note
that the POC patch involved not one single line of change in those
application programs.

regards, tom lane

On Monday, September 03, 2012 10:54:23 PM Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On Monday, September 03, 2012 10:23:52 PM Tom Lane wrote:
> >> I'm reluctantly coming to the conclusion that we can't pass these
> >> parameters through the regular libpq connection string mechanism, and
> >> will have to invent something else. That's awfully nasty though;
> >> it will pretty much cripple the idea that this would be a simple way to
> >> invoke a quasi-embedded variant of Postgres.
> >
> > What I am asking myself is: why does that have to go through the normal
> > PQconnectdb* api? This is something completely new and very well might
> > grow more features that are not necessarily easy to press into
> > PQconnectdb().
>
> Well, what that's mostly going to result in is a huge amount of
> duplication :-(. psql, pg_dump, and anything else that wants to support
> this will need some alternative command line switch and an alternative
> code path to call PQstartServer. I'd hoped to avoid all that. Note
> that the POC patch involved not one single line of change in those
> application programs.
I can see why that would be nice, but is it really realistic? Don't we expect
some more diligence in applications using this against letting such a child
continue to run after ctrl-c/SIGTERMing e.g. pg_dump in comparison to closing
a normal database connection? Besides the already mentioned security issues I
would argue that its a good thing to make the applications authors think about
the special requirements of "embedding" PG.

Greetings,

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Monday, September 03, 2012 10:37 PM Tom Lane wrote:
Amit Kapila <amit(dot)kapila(at)huawei(dot)com> writes:
>> I think part of the code for windows can be written by referring function
>> internal_forkexec(),
>> If you are okay, I can take up this. Please confirm.

> Nobody else volunteered, so have at it. Note that I'm planning to redo
> that code to use socketpair(), so possibly you want to wait to see that
> before you do anything.

I shall wait till you post your initial version which you think is ready
for me to start
for write/test Windows part of feature.

With Regards,
Amit Kapila.

On Mon, Sep 3, 2012 at 4:38 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On Monday, September 03, 2012 10:23:52 PM Tom Lane wrote:
>> I'm reluctantly coming to the conclusion that we can't pass these
>> parameters through the regular libpq connection string mechanism, and
>> will have to invent something else. That's awfully nasty though;
>> it will pretty much cripple the idea that this would be a simple way to
>> invoke a quasi-embedded variant of Postgres.
> What I am asking myself is: why does that have to go through the normal
> PQconnectdb* api? This is something completely new and very well might grow
> more features that are not necessarily easy to press into PQconnectdb().
>
> PQServer
> PQstartServer(const char **keywords, const char **values);
>
> or whatever seems to be more future proof especially considering the
> possibility that this will grow into something more featureful.

I tend to agree. Another idea here might be to stick with Tom's
original idea of making it a connection parameter, but have it be
turned off by default. In other words, if an application wants to
allow those parameters to be used, it would need to do
PQenableStartServer() first. If it doesn't, those connection
parameters will be rejected.

Not 100% sure that's enough to fix the problem, but maybe...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I tend to agree. Another idea here might be to stick with Tom's
> original idea of making it a connection parameter, but have it be
> turned off by default. In other words, if an application wants to
> allow those parameters to be used, it would need to do
> PQenableStartServer() first. If it doesn't, those connection
> parameters will be rejected.

Hm, that might work.

regards, tom lane

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> I can see why that would be nice, but is it really realistic? Don't we
> expect some more diligence in applications using this against letting
> such a child continue to run after ctrl-c/SIGTERMing e.g. pg_dump in
> comparison to closing a normal database connection?

Er, what? If you kill the client, the child postgres will see
connection closure and will shut down. I already tested that with the
POC patch, it worked fine.

regards, tom lane

On Tue, Sep 04, 2012 at 12:01:17AM -0400, Robert Haas wrote:
> Another idea here might be to stick with Tom's
> original idea of making it a connection parameter, but have it be
> turned off by default. In other words, if an application wants to
> allow those parameters to be used, it would need to do
> PQenableStartServer() first. If it doesn't, those connection
> parameters will be rejected.

+1

On Tuesday, September 04, 2012 06:20:59 AM Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > I can see why that would be nice, but is it really realistic? Don't we
> > expect some more diligence in applications using this against letting
> > such a child continue to run after ctrl-c/SIGTERMing e.g. pg_dump in
> > comparison to closing a normal database connection?
>
> Er, what? If you kill the client, the child postgres will see
> connection closure and will shut down. I already tested that with the
> POC patch, it worked fine.
Well, but that will make scripting harder because you cannot start another
single backend pg_dump before the old backend noticed it, checkpointed and
shut down.

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tuesday, September 04, 2012 11:00 AM Andres Freund wrote:
On Tuesday, September 04, 2012 06:20:59 AM Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>> > I can see why that would be nice, but is it really realistic? Don't we
>> > expect some more diligence in applications using this against letting
>> > such a child continue to run after ctrl-c/SIGTERMing e.g. pg_dump in
>> > comparison to closing a normal database connection?
>
>> Er, what? If you kill the client, the child postgres will see
>> connection closure and will shut down. I already tested that with the
>> POC patch, it worked fine.

> Well, but that will make scripting harder because you cannot start another
> single backend pg_dump before the old backend noticed it, checkpointed and
> shut down.

But isn't that behavior will be similar when currently server is shutting down due to
CTRL-C, and at that time new clients will not be allowed to connect.
As this new interface is an approach similar to embedded database where first API (StartServer)
or at connect time it starts database and the other connection might not be allowed during
shutdown state.

With Regards,
Amit Kapila.

On Tuesday, September 04, 2012 12:40 AM Tom Lane wrote:
Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Mon, Sep 3, 2012 at 8:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I have another question after thinking about that for awhile: is there
>>> any security concern there? On Unix-oid systems, we expect the kernel
>>> to restrict who can do a kill() on a postgres process. If there's any
>>> similar restriction on who can send to that named pipe in the Windows
>>> version, it's not obvious from the code. Do we have/need any
>>> restriction there?

>> We use the default for CreateNamedPipe() which is:
>> " The ACLs in the default security descriptor for a named pipe grant
>> full control to the LocalSystem account, administrators, and the
>> creator owner. They also grant read access to members of the Everyone
>> group and the anonymous account."
>> (ref:
http://msdn.microsoft.com/en-us/library/windows/desktop/aa365150(v=vs.85).as
px)

> Hm. The write protections sound fine ... but what's the semantics of
> reading, is it like Unix pipes? If so, couldn't a random third party
> drain the pipe by reading from it, and thereby cause signals to be lost?

When a client connects to the server-end of a named pipe, the server-end
of the pipe is now dedicated to the client. No
more connections will be allowed to that server pipe instance until the
client has disconnected.
This is from paper: http://www.blakewatts.com/namedpipepaper.html, it
mentions about security issues in named pipes.

The function CallNamedPipe() used for sending signal in pgkill() has
following definition:
Connects to a message-type pipe (and waits if an instance of the pipe is
not available), writes to and reads from the
pipe, and then closes the pipe.
(http://msdn.microsoft.com/en-us/library/windows/desktop/aa365144(v=vs.85).a
spx)

So I think based on above 2 points it can be deduced that the signal sent
by pgkill() cannot be read by anyone else.

With Regards,
Amit Kapila.

On Wed, Sep 5, 2012 at 7:31 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> On Tuesday, September 04, 2012 12:40 AM Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> On Mon, Sep 3, 2012 at 8:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> I have another question after thinking about that for awhile: is there
>>>> any security concern there? On Unix-oid systems, we expect the kernel
>>>> to restrict who can do a kill() on a postgres process. If there's any
>>>> similar restriction on who can send to that named pipe in the Windows
>>>> version, it's not obvious from the code. Do we have/need any
>>>> restriction there?
>
>>> We use the default for CreateNamedPipe() which is:
>>> " The ACLs in the default security descriptor for a named pipe grant
>>> full control to the LocalSystem account, administrators, and the
>>> creator owner. They also grant read access to members of the Everyone
>>> group and the anonymous account."
>>> (ref:
> http://msdn.microsoft.com/en-us/library/windows/desktop/aa365150(v=vs.85).as
> px)
>
>> Hm. The write protections sound fine ... but what's the semantics of
>> reading, is it like Unix pipes? If so, couldn't a random third party
>> drain the pipe by reading from it, and thereby cause signals to be lost?
>
> When a client connects to the server-end of a named pipe, the server-end
> of the pipe is now dedicated to the client. No
> more connections will be allowed to that server pipe instance until the
> client has disconnected.

This is the main argument. yes. Each client gets it's own copy, so it
can't get drained.

> So I think based on above 2 points it can be deduced that the signal sent
> by pgkill() cannot be read by anyone else.

Agreed.

Well, what someone else could do is create a pipe with our name before
we do (since we use the actual name - it's \\.\pipe\pgsinal_<pid>), by
guessing what pid we will have. If that happens, we'll go into a loop
and try to recreate it while logging a warning message to
eventlog/stderr. (this happens for every backend). We can't throw an
error on this and kill the backend because the pipe is created in the
background thread not the main one.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Wednesday, September 05, 2012 3:58 PM Magnus Hagander wrote:
On Wed, Sep 5, 2012 at 7:31 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> On Tuesday, September 04, 2012 12:40 AM Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> On Mon, Sep 3, 2012 at 8:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>>> I have another question after thinking about that for awhile: is there
>>>>> any security concern there? On Unix-oid systems, we expect the kernel
>>>>> to restrict who can do a kill() on a postgres process. If there's any
>>>>> similar restriction on who can send to that named pipe in the Windows
>>>>> version, it's not obvious from the code. Do we have/need any
>>>>> restriction there?
>
>>>> We use the default for CreateNamedPipe() which is:
>>>> " The ACLs in the default security descriptor for a named pipe grant
>>>> full control to the LocalSystem account, administrators, and the
>>>> creator owner. They also grant read access to members of the Everyone
>>>> group and the anonymous account."
>>>> (ref:
>
http://msdn.microsoft.com/en-us/library/windows/desktop/aa365150(v=vs.85).as
> px)
>
>>> Hm. The write protections sound fine ... but what's the semantics of
>>> reading, is it like Unix pipes? If so, couldn't a random third party
>>> drain the pipe by reading from it, and thereby cause signals to be lost?
>
>> When a client connects to the server-end of a named pipe, the
server-end
>> of the pipe is now dedicated to the client. No
>> more connections will be allowed to that server pipe instance until the
>> client has disconnected.

> This is the main argument. yes. Each client gets it's own copy, so it
> can't get drained.

>> So I think based on above 2 points it can be deduced that the signal
sent
>> by pgkill() cannot be read by anyone else.

> Agreed.

> Well, what someone else could do is create a pipe with our name before
> we do (since we use the actual name - it's \\.\pipe\pgsinal_<pid>), by
> guessing what pid we will have. If that happens, we'll go into a loop
> and try to recreate it while logging a warning message to
> eventlog/stderr. (this happens for every backend). We can't throw an
> error on this and kill the backend because the pipe is created in the
> background thread not the main one.

Once it is detected that already a same Named Pipe already exists, there
can be following options:

a. try to create with some other name, but in that case how to communicate
the new name to client end of pipe.
Some solution can be thought if this approach seems to be reasonable,
though currently I don't have any in mind.
b. give error, as creation of pipe is generally at beginning of process
creation(backend), but you already mentioned it
is not good approach.
c. any other better solution?

With Regards,
Amit Kapila.

On Tuesday, September 04, 2012 12:11:28 PM Amit Kapila wrote:
> On Tuesday, September 04, 2012 11:00 AM Andres Freund wrote:
>
> On Tuesday, September 04, 2012 06:20:59 AM Tom Lane wrote:
> > Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> >> > I can see why that would be nice, but is it really realistic? Don't we
> >> > expect some more diligence in applications using this against letting
> >> > such a child continue to run after ctrl-c/SIGTERMing e.g. pg_dump in
> >> > comparison to closing a normal database connection?
> >>
> >> Er, what? If you kill the client, the child postgres will see
> >> connection closure and will shut down. I already tested that with the
> >> POC patch, it worked fine.
> >
> > Well, but that will make scripting harder because you cannot start
> > another single backend pg_dump before the old backend noticed it,
> > checkpointed and shut down.
>
> But isn't that behavior will be similar when currently server is shutting
> down due to CTRL-C, and at that time new clients will not be allowed to
> connect. As this new interface is an approach similar to embedded database
> where first API (StartServer) or at connect time it starts database and
> the other connection might not be allowed during shutdown state.
I don't find that a convincing comparison. Normally don't need to shutdown the
server between two pg_dump commands. Which very well might be scripted.

Especially as for now, without a background writer/checkpointer writing stuff
beforehand, the shutdown checkpoint won't be fast. IO isn't unlikely if youre
doing a pg_dump because of hint bits...

Greetings,

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> I don't find that a convincing comparison. Normally don't need to shutdown the
> server between two pg_dump commands. Which very well might be scripted.

> Especially as for now, without a background writer/checkpointer writing stuff
> beforehand, the shutdown checkpoint won't be fast. IO isn't unlikely if youre
> doing a pg_dump because of hint bits...

I still think this is a straw-man argument. There is no expectation
that a standalone PG implementation would provide performance for a
series of standalone sessions that is equivalent to what you'd get from
a persistent server. If that scenario is what's important to you, you'd
use a persistent server. The case where this sort of thing would be
interesting is where minimizing administration complexity (by not having
a server) is more important than performance. People currently use, eg,
SQLite for that type of application, and it's not because of
performance.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> schrieb:

>Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>> I don't find that a convincing comparison. Normally don't need to
>shutdown the
>> server between two pg_dump commands. Which very well might be
>scripted.
>
>> Especially as for now, without a background writer/checkpointer
>writing stuff
>> beforehand, the shutdown checkpoint won't be fast. IO isn't unlikely
>if youre
>> doing a pg_dump because of hint bits...
>
>I still think this is a straw-man argument. There is no expectation
>that a standalone PG implementation would provide performance for a
>series of standalone sessions that is equivalent to what you'd get from
>a persistent server. If that scenario is what's important to you,
>you'd
>use a persistent server. The case where this sort of thing would be
>interesting is where minimizing administration complexity (by not
>having
>a server) is more important than performance. People currently use,
>eg,
>SQLite for that type of application, and it's not because of
>performance.
I am not saying its bad that it is slower, that's absolutely OK. Just that it will take a variable amount of time till you can run pgdump again and its not easily detectable without looping and trying again.

Andres

---
Please excuse the brevity and formatting - I am writing this on my mobile phone.

"anarazel(at)anarazel(dot)de" <andres(at)anarazel(dot)de> writes:
> I am not saying its bad that it is slower, that's absolutely OK. Just that it will take a variable amount of time till you can run pgdump again and its not easily detectable without looping and trying again.

Well, that's why the proposed libpq code is written to wait for the
child postgres to exit when closing the connection.

Admittedly, if you forcibly kill pg_dump (or some other client) and then
immediately try to start a new one, it's not clear how long you'll have
to wait. But so what? Anything we might do in this space is going to
have pluses and minuses.

regards, tom lane

Tom,

> However, there are some additional things
> we'd need to think about before advertising it as a fit solution for that.
> 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.

Well, if you think about standalone mode as "developer" mode, it's not
quite so clear that we'd need those things. Generally when people are
testing code in development they don't care about vacuum or bgwriter
because the database is small and ephemeral. So even without background
processes, standalone mode would be useful for many users for
development and automated testing.

For that matter, applications which embed postgresql and have very small
databases could also live without autovacuum and bgwriter. Heck,
Postgres existed without them for many years.

You just doc that, if you're running postgres standalone, you need to
run a full VACUUM ANALYZE on the database cluster once per day. And you
live with the herky-jerky write performance. If the database is 5GB,
who's going to notice anyway?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> However, there are some additional things
>> we'd need to think about before advertising it as a fit solution for that.
>> 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.

> Well, if you think about standalone mode as "developer" mode, it's not
> quite so clear that we'd need those things. Generally when people are
> testing code in development they don't care about vacuum or bgwriter
> because the database is small and ephemeral. So even without background
> processes, standalone mode would be useful for many users for
> development and automated testing.

Only if startup and shutdown were near instantaneous, which as Andres
was pointing out would be far from the truth. I am envisioning the
use-case for this thing as stuff like desktop managers and mail
programs, which tend to be rather lumbering on startup anyway. (And
yes, a lot of those have got embedded databases in them these days.
More often than not it's mysql.) I don't see people wanting to use this
feature for unit tests.

> For that matter, applications which embed postgresql and have very small
> databases could also live without autovacuum and bgwriter. Heck,
> Postgres existed without them for many years.

Um ... true with respect to autovacuum, perhaps, but what about
checkpoints? A standalone backend will never perform a checkpoint
unless explicitly told to. (Before we invented the bgwriter, the
postmaster was in charge of launching checkpoints every so often.)
Again, this is probably just what you want for disaster recovery, but
it wouldn't be terribly friendly for an embedded-database application.

In general I think the selling point for such a feature would be "no
administrative hassles", and I believe that has to go not only for the
end-user experience but also for the application-developer experience.
If you have to manage checkpointing and vacuuming in the application,
you're probably soon going to look for another database.

regards, tom lane

On Wed, Sep 5, 2012 at 01:50:06PM -0700, Josh Berkus wrote:
> Tom,
>
> > However, there are some additional things
> > we'd need to think about before advertising it as a fit solution for that.
> > 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.
>
> Well, if you think about standalone mode as "developer" mode, it's not
> quite so clear that we'd need those things. Generally when people are
> testing code in development they don't care about vacuum or bgwriter
> because the database is small and ephemeral. So even without background
> processes, standalone mode would be useful for many users for
> development and automated testing.
>
> For that matter, applications which embed postgresql and have very small
> databases could also live without autovacuum and bgwriter. Heck,
> Postgres existed without them for many years.
>
> You just doc that, if you're running postgres standalone, you need to
> run a full VACUUM ANALYZE on the database cluster once per day. And you
> live with the herky-jerky write performance. If the database is 5GB,
> who's going to notice anyway?

If this mode slows down pg_upgrade, that is going to be a problem.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 9/5/12 5:03 PM, Tom Lane wrote:
> I don't see people wanting to use this feature for unit tests.

If this is going to become an official feature (as opposed to an
internal interface only for use by pg_upgrade), then I think that's
exactly what people will want to use it for. In fact, it might even
make it more likely that people will write unit tests suits to begin with.

On Wed, Sep 5, 2012 at 2:50 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On 9/5/12 5:03 PM, Tom Lane wrote:
>> I don't see people wanting to use this feature for unit tests.
>
> If this is going to become an official feature (as opposed to an
> internal interface only for use by pg_upgrade), then I think that's
> exactly what people will want to use it for. In fact, it might even
> make it more likely that people will write unit tests suits to begin with.

I agree with this, even though in theory (but not in practice)
creative use of unix sockets (sorry windows, perhaps some
port-allocating and URL mangling can be done instead) and conventions
for those would allow even better almost-like-embedded results,
methinks. That may still be able to happen.

The biggest improvement to that situation is the recent drastic
reduction in use of shared memory, and that only became a thing recently.

--
fdr

On 9/5/12 5:59 PM, Daniel Farina wrote:
> I agree with this, even though in theory (but not in practice)
> creative use of unix sockets (sorry windows, perhaps some
> port-allocating and URL mangling can be done instead) and conventions
> for those would allow even better almost-like-embedded results,
> methinks. That may still be able to happen.

Sure, everyone who cares can already do this, but some people probably
don't care enough. Also, making this portable and robust for everyone
to use, not just your local environment, is pretty tricky. See
pg_upgrade test script, for a prominent example.

On 9/5/12 2:50 PM, Peter Eisentraut wrote:
> On 9/5/12 5:03 PM, Tom Lane wrote:
>> I don't see people wanting to use this feature for unit tests.
>
> If this is going to become an official feature (as opposed to an
> internal interface only for use by pg_upgrade), then I think that's
> exactly what people will want to use it for. In fact, it might even
> make it more likely that people will write unit tests suits to begin with.

Heck, *I'll* use it for unit tests.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

> Um ... true with respect to autovacuum, perhaps, but what about
> checkpoints? A standalone backend will never perform a checkpoint
> unless explicitly told to.

Hmmm, that's definitely an issue.

> (Before we invented the bgwriter, the
> postmaster was in charge of launching checkpoints every so often.)
> Again, this is probably just what you want for disaster recovery, but
> it wouldn't be terribly friendly for an embedded-database application.

Yeah, we'd have to put in a clock-based thing which did checkpoints
every 5 minutes and VACUUM ANALYZE every hour or something. That seems
like a chunk of extra code.

> In general I think the selling point for such a feature would be "no
> administrative hassles", and I believe that has to go not only for the
> end-user experience but also for the application-developer experience.
> If you have to manage checkpointing and vacuuming in the application,
> you're probably soon going to look for another database.

Well, don't discount the development/testing case. If you do agile or
TDD (a lot of people do), you often have a workload which looks like:

1) Start framework
2) Start database
3) Load database with test data
4) Run tests
5) Print results
6) Shut down database

In a case like that, you can live without checkpointing, even; the
database is ephemeral.

In other words, let's make this a feature and document it for use in
testing, and that it's not really usable for production embedded apps yet.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Wed, Sep 5, 2012 at 3:17 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On 9/5/12 5:59 PM, Daniel Farina wrote:
>> I agree with this, even though in theory (but not in practice)
>> creative use of unix sockets (sorry windows, perhaps some
>> port-allocating and URL mangling can be done instead) and conventions
>> for those would allow even better almost-like-embedded results,
>> methinks. That may still be able to happen.
>
> Sure, everyone who cares can already do this, but some people probably
> don't care enough. Also, making this portable and robust for everyone
> to use, not just your local environment, is pretty tricky. See
> pg_upgrade test script, for a prominent example.

To my knowledge, no one has even really seriously tried to package it
yet and then told the tale of woe, and it was an especially
un-gratifying exercise for quite a while on account of multiple
postgreses not getting along on the same machine because of SysV
shmem.

The bar for testing is a lot different than pg_upgrade (where a
negative consequence is confusing and stressful downtime), and many
programs use fork/threads and multiple connections even in testing,
making its requirements different.

So consider me still skeptical given the current reasoning that unix
sockets can't be a good-or-better substitute, and especially
accounting for programs that need multiple backends.

--
fdr

So, in the spirit of not painting ourselves into a tiny corner here on
the whole "single backend" and "embedded database" problem with pg
options, can we generalize this a bit?

Any way we could make psql connect to a "given fd", as an option? In
theory, that could be something opened by some out-side-of-postgresql
tunnel with 3rd party auth in the same app that uses libpq directly,
or it could be a fd prepared by something that specifically launched
a single-backend postgres, like in the case of pg_upgrade, pg_uprade
itself, and passed to psql, etc, which would be passed in as options.

In theory, that might even allow the possibility of starting the
single-backend only once and passing it to multiple clients in
succession, instead of having to stop/start the backend between each
client. And it would allow the possiblity of "something" (pg_upgrade,
or some other application) to control the start/stop of the backend
outside the libpq connection.

Now, I'm familiar with the abilities related to passing fd's around in
Linux, but have no idea if we'd have comparable methods to use on
Windows.

a.

On Wed, Sep 5, 2012 at 8:11 PM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
> On Wed, Sep 5, 2012 at 3:17 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> On 9/5/12 5:59 PM, Daniel Farina wrote:
>>> I agree with this, even though in theory (but not in practice)
>>> creative use of unix sockets (sorry windows, perhaps some
>>> port-allocating and URL mangling can be done instead) and conventions
>>> for those would allow even better almost-like-embedded results,
>>> methinks. That may still be able to happen.
>>
>> Sure, everyone who cares can already do this, but some people probably
>> don't care enough. Also, making this portable and robust for everyone
>> to use, not just your local environment, is pretty tricky. See
>> pg_upgrade test script, for a prominent example.
>
> To my knowledge, no one has even really seriously tried to package it
> yet and then told the tale of woe, and it was an especially
> un-gratifying exercise for quite a while on account of multiple
> postgreses not getting along on the same machine because of SysV
> shmem.
>
> The bar for testing is a lot different than pg_upgrade (where a
> negative consequence is confusing and stressful downtime), and many
> programs use fork/threads and multiple connections even in testing,
> making its requirements different.
>
> So consider me still skeptical given the current reasoning that unix
> sockets can't be a good-or-better substitute, and especially
> accounting for programs that need multiple backends.
>
> --
> fdr
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Aidan Van Dyk <aidan(at)highrise(dot)ca> writes:
> So, in the spirit of not painting ourselves into a tiny corner here on
> the whole "single backend" and "embedded database" problem with pg
> options, can we generalize this a bit?

> Any way we could make psql connect to a "given fd", as an option? In
> theory, that could be something opened by some out-side-of-postgresql
> tunnel with 3rd party auth in the same app that uses libpq directly,
> or it could be a fd prepared by something that specifically launched
> a single-backend postgres, like in the case of pg_upgrade, pg_uprade
> itself, and passed to psql, etc, which would be passed in as options.

This seems to me to be going in exactly the wrong direction. What
I visualize this feature as responding to is demand for a *simple*,
minimal configuration, minimal administration, quasi-embedded database.
What you propose above is not that, but is if anything even more
complicated for an application to deal with than a regular persistent
server. More complication is *the wrong thing* for this use case.

The people who would be interested in this are currently using something
like SQLite within a single application program. It hasn't got any of
the features you're suggesting either, and I don't think anybody wishes
it did.

regards, tom lane

On 09/05/2012 10:14 PM, Tom Lane wrote:
> Aidan Van Dyk <aidan(at)highrise(dot)ca> writes:
>> So, in the spirit of not painting ourselves into a tiny corner here on
>> the whole "single backend" and "embedded database" problem with pg
>> options, can we generalize this a bit?
>> Any way we could make psql connect to a "given fd", as an option? In
>> theory, that could be something opened by some out-side-of-postgresql
>> tunnel with 3rd party auth in the same app that uses libpq directly,
>> or it could be a fd prepared by something that specifically launched
>> a single-backend postgres, like in the case of pg_upgrade, pg_uprade
>> itself, and passed to psql, etc, which would be passed in as options.
> This seems to me to be going in exactly the wrong direction. What
> I visualize this feature as responding to is demand for a *simple*,
> minimal configuration, minimal administration, quasi-embedded database.
> What you propose above is not that, but is if anything even more
> complicated for an application to deal with than a regular persistent
> server. More complication is *the wrong thing* for this use case.
>
> The people who would be interested in this are currently using something
> like SQLite within a single application program. It hasn't got any of
> the features you're suggesting either, and I don't think anybody wishes
> it did.
>
>

Exactly. I think it's worth stating that this has a HUGE potential
audience, and if we can get to this the deployment of Postgres could
mushroom enormously. I'm really quite excited about it.

cheers

andrew

On Wed, Sep 5, 2012 at 7:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> This seems to me to be going in exactly the wrong direction. What
> I visualize this feature as responding to is demand for a *simple*,
> minimal configuration, minimal administration, quasi-embedded database.
> What you propose above is not that, but is if anything even more
> complicated for an application to deal with than a regular persistent
> server. More complication is *the wrong thing* for this use case.
>
> The people who would be interested in this are currently using something
> like SQLite within a single application program. It hasn't got any of
> the features you're suggesting either, and I don't think anybody wishes
> it did.

I am failing to understand how one could easily replicate the SQLite
feature of (even fairly poorly) using multiple processes addressing
one database, and supporting multiple executors per-database (which
correspond to every open 'connection' in SQLite, as far as I can
understand). My best thoughts are in the direction of EXEC_BACKEND
and hooking up to a cluster post-facto, but I wasn't really looking
for solutions so much as to raise this (to me) important use-case.

I'm just thinking about all the enormously popular prefork based web
servers out there like unicorn (Ruby), gunicorn (Python), and even
without forking language-specific database abstractions like that seen
in Go ("database/sql") that have decided to make pooling the default
interaction.

It is easiest to use these prefork embedded servers in both in
development and production, so people (rather sensibly) do that --
better parity, and no fuss.

I really would rather not see that regress by appropriating special
mechanics for test/development scenarios with regards to managing
database connections (e.g. exactly one of them), so how do we not make
that a restriction, unless I misunderstood and was a non-restriction
already?

--
fdr

On Wed, 2012-09-05 at 17:03 -0400, Tom Lane wrote:
> In general I think the selling point for such a feature would be "no
> administrative hassles", and I believe that has to go not only for the
> end-user experience but also for the application-developer experience.
> If you have to manage checkpointing and vacuuming in the application,
> you're probably soon going to look for another database.

Maybe there could be some hooks (e.g., right after completing a
statement) that see whether a vacuum or checkpoint is required? VACUUM
can't be run in a transaction block[1], so there are some details to
work out, but it might be a workable approach.

Regards,
Jeff Davis

[1]: It seems like the only reason for that is so a multi-table vacuum
doesn't hold locks for longer than it needs to, but that's not much of a
concern in this case.

Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> Would socketpair(2) be simpler?

Attached is a revised version of the patch that uses socketpair(2).
This is definitely a lot less invasive --- the backend side of the
patch, in particular, is far shorter, and there are fewer portability
hazards since we're not trying to replace sockets with pipes.

I've not done anything yet about the potential security issues
associated with untrusted libpq connection strings. I think this
is still at the proof-of-concept stage; in particular, it's probably
time to see if we can make it work on Windows before we worry more
about that.

I'm a bit tempted though to pull out and apply the portions of the
patch that replace libpq's assorted ad-hoc closesocket() calls with
a centralized pqDropConnection routine. I think that's probably a good
idea independently of this feature.

regards, tom lane

On 07.09.2012 10:49, Tom Lane wrote:
> Heikki Linnakangas<hlinnaka(at)iki(dot)fi> writes:
>> Would socketpair(2) be simpler?
>
> Attached is a revised version of the patch that uses socketpair(2).
> This is definitely a lot less invasive --- the backend side of the
> patch, in particular, is far shorter, and there are fewer portability
> hazards since we're not trying to replace sockets with pipes.
>
> I've not done anything yet about the potential security issues
> associated with untrusted libpq connection strings. I think this
> is still at the proof-of-concept stage; in particular, it's probably
> time to see if we can make it work on Windows before we worry more
> about that.
>
> I'm a bit tempted though to pull out and apply the portions of the
> patch that replace libpq's assorted ad-hoc closesocket() calls with
> a centralized pqDropConnection routine. I think that's probably a good
> idea independently of this feature.

Sounds good.

It's worth noting that now that libpq constructs the command line to
execute "postgres --child= -D <datadir>", we'll be stuck with that set
of arguments forever, because libpq needs to be able to talk to
different versions. Or at least we'd need to teach libpq to check the
version of binary and act accordingly, if we change that syntax. That's
probably OK, I don't feel any pressure to change those command line
arguments anyway.

- Heikki

Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> It's worth noting that now that libpq constructs the command line to
> execute "postgres --child= -D <datadir>", we'll be stuck with that set
> of arguments forever, because libpq needs to be able to talk to
> different versions. Or at least we'd need to teach libpq to check the
> version of binary and act accordingly, if we change that syntax. That's
> probably OK, I don't feel any pressure to change those command line
> arguments anyway.

Yeah. The -D syntax seems safe enough from here. One thing that's on
my to-do list for this patch is to add a -v switch to set the protocol
version, so that we don't need to assume that libpq and backend have the
same default protocol version.

regards, tom lane

Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> On 07.09.2012 10:49, Tom Lane wrote:
>> I'm a bit tempted though to pull out and apply the portions of the
>> patch that replace libpq's assorted ad-hoc closesocket() calls with
>> a centralized pqDropConnection routine. I think that's probably a good
>> idea independently of this feature.

> Sounds good.

Done; here's the rebased patch.

regards, tom lane

On Thu, Sep 6, 2012 at 12:56 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Wed, 2012-09-05 at 17:03 -0400, Tom Lane wrote:
>> In general I think the selling point for such a feature would be "no
>> administrative hassles", and I believe that has to go not only for the
>> end-user experience but also for the application-developer experience.
>> If you have to manage checkpointing and vacuuming in the application,
>> you're probably soon going to look for another database.
>
> Maybe there could be some hooks (e.g., right after completing a
> statement) that see whether a vacuum or checkpoint is required? VACUUM
> can't be run in a transaction block[1], so there are some details to
> work out, but it might be a workable approach.

If it was me, I'd want finer grained control of if/when automatic
background optimization work happened. Something like
DoBackgroundWork(int forThisManySeconds). Of course, for that to
work, we'd need to have resumable vacuum.

I like the idea of keeping everything single threaded.

merlin

On Friday, September 07, 2012 11:21:00 PM Merlin Moncure wrote:
> On Thu, Sep 6, 2012 at 12:56 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > On Wed, 2012-09-05 at 17:03 -0400, Tom Lane wrote:
> >> In general I think the selling point for such a feature would be "no
> >> administrative hassles", and I believe that has to go not only for the
> >> end-user experience but also for the application-developer experience.
> >> If you have to manage checkpointing and vacuuming in the application,
> >> you're probably soon going to look for another database.
> >
> > Maybe there could be some hooks (e.g., right after completing a
> > statement) that see whether a vacuum or checkpoint is required? VACUUM
> > can't be run in a transaction block[1], so there are some details to
> > work out, but it might be a workable approach.
>
> If it was me, I'd want finer grained control of if/when automatic
> background optimization work happened. Something like
> DoBackgroundWork(int forThisManySeconds). Of course, for that to
> work, we'd need to have resumable vacuum.
>
> I like the idea of keeping everything single threaded.
To me this path seems to be the best way to never get the feature at all...

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 9/2/12 7:23 PM, Tom Lane wrote:
> 4. As coded, the backend assumes the incoming pipe is on its FD 0 and the
> outgoing pipe is on its FD 1. This made the command line simple but I'm
> having second thoughts about it: if anything inside the backend tries to
> read stdin or write stdout, unpleasant things will happen. It might be
> better to not reassign the pipe FD numbers. In that case we'd have to
> pass them on the command line, so that the syntax would be something
> like "postgres --child 4,5 -D pgdata ...".

Would it be sufficient to just hard-code the FD's to use? I'm not sure why someone would need to change them, so long as we steer clear of STD(IN|OUT|ERR)...
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Wed, Sep 5, 2012 at 10:29 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> On 09/05/2012 10:14 PM, Tom Lane wrote:
>
>>
>> The people who would be interested in this are currently using something
>> like SQLite within a single application program.
>>
>

> Exactly. I think it's worth stating that this has a HUGE potential
> audience, and if we can get to this the deployment of Postgres could
> mushroom enormously. I'm really quite excited about it.
>

/me shares the feeling :)

--
Gurjeet Singh

A Dijous, 6 de setembre de 2012 00:30:53, Josh Berkus va escriure:
> > In general I think the selling point for such a feature would be "no
> > administrative hassles", and I believe that has to go not only for the
> > end-user experience but also for the application-developer experience.
> > If you have to manage checkpointing and vacuuming in the application,
> > you're probably soon going to look for another database.
>
> Well, don't discount the development/testing case. If you do agile or
> TDD (a lot of people do), you often have a workload which looks like:
>
> 1) Start framework
> 2) Start database
> 3) Load database with test data
> 4) Run tests
> 5) Print results
> 6) Shut down database
>
> In a case like that, you can live without checkpointing, even; the
> database is ephemeral.
>
> In other words, let's make this a feature and document it for use in
> testing, and that it's not really usable for production embedded apps yet.

+1.

Some projects such as tryton would benefit from this feature.

--
Albert Cervera i Areny
http://www.NaN-tic.com
Tel: +34 93 553 18 03

http://twitter.com/albertnan
http://www.nan-tic.com/blog

On Friday, September 07, 2012 11:19 PM Tom Lane wrote:
Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>> Would socketpair(2) be simpler?

>I've not done anything yet about the potential security issues
>associated with untrusted libpq connection strings. I think this
>is still at the proof-of-concept stage; in particular, it's probably
> time to see if we can make it work on Windows before we worry more
>about that.

I have started working on this patch to make it work on Windows. The 3 main things to make it work are:

1. Windows equivalent for socketpair - This as suggested previously in this thread earlier code of pgpipe can suffice the need. Infact I have checked on net as well, most implementations are similar to pgpipe implementation. So I prefered to use the existing code which was removed.

2. Windows equivalent for fork-execv - This part can be done by CreateProcess,it can be similar to internal_forkexec except for path where it uses shared memory to pass parameters, I am trying to directly pass parameters to CreateProcess.

3. Windows equivalent for waitpid - Actually there can be 2 ways to accomplish this
a. use waitforsingleobject with process handle, but in some places it is mentioned it might not work for all windows versions. Can someone pls confirm about. I shall try on my
PC to test the same.
b. use existing infrastructure of waitpid, however it is not for single process and it might need some changes to make it work for single process or may be we can use it
directly. However currently it is in postmaster.c, so it need to be moved so that we can access it from fe-connect.c in libpq as well.
c. suggest if you know of other ways to handle it or which from above 2 would be better?

Some other doubts:

1. does this follow the behavior that admin users will not be allowed to invoke postgres child process?
2. to find standalone_backend incase user didn't input, do we need mechanism similar to getInstallationPaths()?

Any other comments/suggestions?

With Regards,
Amit Kapila.

Amit kapila <amit(dot)kapila(at)huawei(dot)com> writes:
> 1. does this follow the behavior that admin users will not be allowed to invoke postgres child process?

That's an interesting question. I'm not sure if we'd want to disable
the no-root check on the Unix side, but it might make sense to. But
this has no bearing on what libpq does, does it?

> 2. to find standalone_backend incase user didn't input, do we need mechanism similar to getInstallationPaths()?

No. There is no reason at all for libpq to think it is part of a
postgres-supplied program, so it can't use any relative-path tricks,
even if it had the program's argv[0] which it does not. Either the
compiled-in path works, or the user has to give one.

(But having said that, if Windows provides a way for a DLL to find
out its own filesystem location, maybe relative path from there would
work.)

regards, tom lane

Hi,

On Wednesday, September 05, 2012 06:00:18 PM Tom Lane wrote:
> "anarazel(at)anarazel(dot)de" <andres(at)anarazel(dot)de> writes:
> > I am not saying its bad that it is slower, that's absolutely OK. Just
> > that it will take a variable amount of time till you can run pgdump
> > again and its not easily detectable without looping and trying again.
>
> Well, that's why the proposed libpq code is written to wait for the
> child postgres to exit when closing the connection.
>
> Admittedly, if you forcibly kill pg_dump (or some other client) and then
> immediately try to start a new one, it's not clear how long you'll have
> to wait.
Yep, thats the case I was talking about upthread ;)

On Monday, September 03, 2012 11:04:15 PM Andres Freund wrote:
> Don't we expect some more diligence in applications using this against
> letting such a child continue to run after ctrl-c/SIGTERMing e.g. pg_dump
> in comparison to closing a normal database connection?
I would really expect any postgres supplied core tool to try to handle that
case. Thats not exactly trivial to do right and requires cooperation of the
application. Because that requires knowledge about the operation mode in those
anyway I don't really see an extra libpq call as problematic...

> But so what? Anything we might do in this space is going to
> have pluses and minuses.
True.

Greetings,

Andres

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Sunday, September 09, 2012 8:46 PM Tom Lane wrote:
Amit kapila <amit(dot)kapila(at)huawei(dot)com> writes:
>> 1. does this follow the behavior that admin users will not be allowed to
invoke postgres child process?

> That's an interesting question. I'm not sure if we'd want to disable
> the no-root check on the Unix side, but it might make sense to. But
> this has no bearing on what libpq does, does it?

No, it has no bearing with what libpq does. Its only related to postgres
invocation, because as soon as postgres get invoked, it first checks about
root. If we want the root check can be avoided easily by checking argv.

>> 2. to find standalone_backend incase user didn't input, do we need
mechanism similar to getInstallationPaths()?

> No. There is no reason at all for libpq to think it is part of a
> postgres-supplied program, so it can't use any relative-path tricks,
> even if it had the program's argv[0] which it does not. Either the
> compiled-in path works, or the user has to give one.

> (But having said that, if Windows provides a way for a DLL to find
> out its own filesystem location, maybe relative path from there would
> work.)

At first, I shall go ahead with the current way (Either the
compiled-in path works, or the user has to give one.).

On Sunday, September 09, 2012 1:37 PM Amit Kapila wrote:
On Friday, September 07, 2012 11:19 PM Tom Lane wrote:
Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>>> Would socketpair(2) be simpler?

>>I've not done anything yet about the potential security issues
>>associated with untrusted libpq connection strings. I think this
>>is still at the proof-of-concept stage; in particular, it's probably
>> time to see if we can make it work on Windows before we worry more
>>about that.

> I have started working on this patch to make it work on Windows. The 3
main things to make it work are:

> 1. Windows equivalent for socketpair - This as suggested previously in
this thread earlier code of pgpipe can suffice
> the need. Infact I have checked on net as well, most implementations are
similar to pgpipe implementation. So I
> prefered to use the existing code which was removed.

> 2. Windows equivalent for fork-execv - This part can be done by
CreateProcess,it can be similar to internal_forkexec
> except for path where it uses shared memory to pass parameters, I am
trying to directly pass parameters to
> CreateProcess.

Directly passing parameters doesn't suffice for all parameters, as for
socket we need to duplicate the socket using WSADuplicateSocket() which
returns little big structure which is better to be passed via shared memory.

> 3. Windows equivalent for waitpid - Actually there can be 2 ways to
accomplish this
> a. use waitforsingleobject with process handle, but in some places it
is mentioned it might not work for all
> windows versions. Can someone pls confirm about. I shall try on
my PC to test the same.
> b. use existing infrastructure of waitpid, however it is not for
single process and it might need some changes to
> make it work for single process or may be we can use it directly.
However currently it is in postmaster.c, so
> it need to be moved so that we can access it from fe-connect.c in
libpq as well.
> c. suggest if you know of other ways to handle it or which from above
2 would be better?

I have used method - a (waitforsingleobject) and it worked fine.

With the above implementation, it is working on Windows. Now the work left
is as follows:
1. Refactoring of code
2. Error handling in paths
3. Check if anything is missing and implement for same.
4. Test the patch for Windows.

Any comments/suggestions?

With Regards,
Amit Kapila

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/

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.

On 10.09.2012 18:12, Gurjeet Singh 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.

[scratches head] How's that different from the normal postmaster mode?

- Heikki

On Mon, Sep 10, 2012 at 11:43 AM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>wrote:

> On 10.09.2012 18:12, Gurjeet Singh 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.
>>
>
> [scratches head] How's that different from the normal postmaster mode?

As I described in later paragraphs, it'd behave like an embedded database,
like SQLite etc., so the database will startup and shutdown with the
application, and provide other advantages we're currently trying to
provide, like zero-maintenance. But it will not mandate that only one
application talk to it at a time, and allow as many applications as it
would in postmaster mode. So the database would be online as long as any
application is connected to it, and it will shutdown when the last
application disconnects.

As being implemented right now, there's very little difference between
--single and --child modes. I guess I am asking for a --child mode
implementation that is closer to a postmaster than --single.

Best regards,
--
Gurjeet Singh

http://gurjeet.singh.im/

Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> writes:
> On Mon, Sep 10, 2012 at 11:43 AM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>wrote:
>> [scratches head] How's that different from the normal postmaster mode?

> As I described in later paragraphs, it'd behave like an embedded database,
> like SQLite etc., so the database will startup and shutdown with the
> application, and provide other advantages we're currently trying to
> provide, like zero-maintenance. But it will not mandate that only one
> application talk to it at a time, and allow as many applications as it
> would in postmaster mode. So the database would be online as long as any
> application is connected to it, and it will shutdown when the last
> application disconnects.

I am having a hard time getting excited about that. To me it sounds
like it's a regular postmaster, except with a response-time problem for
connections that occur when there had been no active client before.

The point of the proposal that I am making is to have a simple,
low-maintenance solution for people who need a single-application
database. A compromise somewhere in the middle isn't likely to be an
improvement for anybody. For instance, if you want to have additional
connections, you open up a whole collection of communication and
authentication issues, which potential users of a single-application
database don't want to cope with.

> As being implemented right now, there's very little difference between
> --single and --child modes. I guess I am asking for a --child mode
> implementation that is closer to a postmaster than --single.

There are good reasons for wanting something that is closer to --single:
pg_upgrade being one, and having a friendlier user interface for
disaster recovery in --single mode being another. In these cases, you
not only don't need the capability for additional applications to
connect, it is actually important that it's impossible for them to do
so.

regards, tom lane

> The point of the proposal that I am making is to have a simple,
> low-maintenance solution for people who need a single-application
> database. A compromise somewhere in the middle isn't likely to be an
> improvement for anybody. For instance, if you want to have additional
> connections, you open up a whole collection of communication and
> authentication issues, which potential users of a single-application
> database don't want to cope with.

Yes, exactly.

In fact, most of the folks who would want an embedded PostgreSQL either
want no authentication at all, or only a single password. So supporting
authentication options other than trust or md5 is not required, or
desired AFAIK.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> In fact, most of the folks who would want an embedded PostgreSQL
> either want no authentication at all, or only a single password.
> So supporting authentication options other than trust or md5 is
> not required, or desired AFAIK.

I don't know whether it's worth the trouble of doing so, but
serializable transactions could skip all the SSI predicate locking
and conflict checking when in single-connection mode. With only one
connection the transactions could never overlap, so there would be
no chance of serialization anomalies when running snapshot
isolation.

The reason I wonder whether it is worth the trouble is that it would
only really matter if someone had code they wanted to run under both
normal and single-connection modes. For single-connection only,
they could just choose REPEATABLE READ to get exactly the same
semantics.

-Kevin

On Mon, Sep 10, 2012 at 9:59 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> The point of the proposal that I am making is to have a simple,
>> low-maintenance solution for people who need a single-application
>> database. A compromise somewhere in the middle isn't likely to be an
>> improvement for anybody. For instance, if you want to have additional
>> connections, you open up a whole collection of communication and
>> authentication issues, which potential users of a single-application
>> database don't want to cope with.
>
> Yes, exactly.
>
> In fact, most of the folks who would want an embedded PostgreSQL either
> want no authentication at all, or only a single password. So supporting
> authentication options other than trust or md5 is not required, or
> desired AFAIK.

I agree people who want embedded postgres probably want no authentication.

For the sake of test/local development use cases, I still question the
usefulness of a quasi-embeddable mode that does not support multiple
executors as SQLite does -- it's pretty murderous for the testing use
case for a number of people if they intend to have parity with their
production environment.

I could see this being useful for, say, iOS (although there is a
definite chance that one will want to use multiple
threads/simultaneous xacts in applications), or a network router,
except that pg_xlog and the catalog are rather enormous for those use
cases.

So while the embedded use case is really appealing in general, I'm
still intuitively quite skeptical of the omission of multi-executor
support, especially considering that people have gotten used to being
able to that with the incredibly popular SQLite. Could EXEC_BACKEND
be repurposed -- even on *nix-- to make this work someday?

--
fdr

On Monday, September 10, 2012 8:20 PM Amit Kapila wrote:
On Sunday, September 09, 2012 1:37 PM Amit Kapila wrote:
On Friday, September 07, 2012 11:19 PM Tom Lane wrote:
Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>>> Would socketpair(2) be simpler?

>>>I've not done anything yet about the potential security issues
>>>associated with untrusted libpq connection strings. I think this
>>is still at the proof-of-concept stage; in particular, it's probably
>> time to see if we can make it work on Windows before we worry more
>>about that.

> I have started working on this patch to make it work on Windows. The 3
main things to make it work are:

The patch which contains Windows implementation as well is attached with this mail. It contains changes related to following
1. waitpid
2. socketpair
3. fork-exec

The following is still left:
1. Error handling in all paths
2. During test, I found if i try to run with admin user, it throws error but psql doesn't comes out.
I will look into this issue. However as in previous mail discussion there is a decision pending whether in standalone mode
we need admin user behavior.
3. Will do some more test in Windows.

Currently I have prepared a patch on top of your changes, please let me know if that is okay.
Also, it will be better for me if you can tell me how I can further contribute.

With Regards,
Amit Kapila

On Tuesday, September 11, 2012 7:07 PM Amit Kapila wrote:
On Monday, September 10, 2012 8:20 PM Amit Kapila wrote:
On Sunday, September 09, 2012 1:37 PM Amit Kapila wrote:
On Friday, September 07, 2012 11:19 PM Tom Lane wrote:
Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>>>> Would socketpair(2) be simpler?

>>>>I've not done anything yet about the potential security issues
>>>>associated with untrusted libpq connection strings. I think this
>>>is still at the proof-of-concept stage; in particular, it's probably
>>> time to see if we can make it work on Windows before we worry more
>>>about that.

>> I have started working on this patch to make it work on Windows. The 3
main things to make it work are:

>The patch which contains Windows implementation as well is attached with this mail. It contains changes related to following
>1. waitpid
>2. socketpair
>3. fork-exec

>The following is still left:
> 1. Error handling in all paths

The modified version 2 contains error handling in all paths.

With Regards,
Amit Kapila

On Fri, Sep 14, 2012 at 6:42 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> On Tuesday, September 11, 2012 7:07 PM Amit Kapila wrote:
> On Monday, September 10, 2012 8:20 PM Amit Kapila wrote:
> On Sunday, September 09, 2012 1:37 PM Amit Kapila wrote:
> On Friday, September 07, 2012 11:19 PM Tom Lane wrote:
> Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>>>>> Would socketpair(2) be simpler?
>
>
>
>>>>>I've not done anything yet about the potential security issues
>>>>>associated with untrusted libpq connection strings. I think this
>>>>is still at the proof-of-concept stage; in particular, it's probably
>>>> time to see if we can make it work on Windows before we worry more
>>>>about that.
>
>>> I have started working on this patch to make it work on Windows. The 3
> main things to make it work are:
>
>>The patch which contains Windows implementation as well is attached with this mail. It contains changes related to following
>>1. waitpid
>>2. socketpair
>>3. fork-exec
>
>>The following is still left:
>> 1. Error handling in all paths
>
> The modified version 2 contains error handling in all paths.

I didn't see that this patch was added to a commitfest -- should it
have been? I very much like Tom's proposed starting point for this
feature as a replacement for --single. Hate to see this die on the
vine. Would some testing on windows be what's needed to get the ball
rolling?

merlin

On 10 September 2012 17:50, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> The point of the proposal that I am making is to have a simple,
> low-maintenance solution for people who need a single-application
> database. A compromise somewhere in the middle isn't likely to be an
> improvement for anybody. For instance, if you want to have additional
> connections, you open up a whole collection of communication and
> authentication issues, which potential users of a single-application
> database don't want to cope with.

So the proposal is to implement a database that can't ever have 2 or
more connections. And so any data it stores cannot ever be accessed by
another connection, so all forms of replication are excluded and all
maintenance actions force the database to be unavailable for a period
of time. Those two things are barriers of the most major kind to
anybody working in an enterprise with connected data and devices. The
only people that want this are people with a very short term view of
the purpose of their applications, and disregard for the value and
permanence of the data stored. They may not want to cope with those
issues *now* but they will later and won't thank us for implementing
it in a way that means it can never be achieved.

To be honest, I can't believe I'm reading this.

And worse, it's on our Don't Want list, and nobody has said stop.

It's almost impossible to purchase a CPU these days that doesn't have
multiple cores, so the whole single-process architecture is just dead.
Yes, we want Postgres installed everywhere, but this isn't the way to
achieve that.

I agree we should allow a PostgreSQL installation to work for a single
user, but I don't see that requires other changes. This idea will
cause endless bugs, thinkos and severely waste our time. So without a
much better justification, I don't think we should do this.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Nov 12, 2012 at 1:21 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 10 September 2012 17:50, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> The point of the proposal that I am making is to have a simple,
>> low-maintenance solution for people who need a single-application
>> database. A compromise somewhere in the middle isn't likely to be an
>> improvement for anybody. For instance, if you want to have additional
>> connections, you open up a whole collection of communication and
>> authentication issues, which potential users of a single-application
>> database don't want to cope with.
>
> So the proposal is to implement a database that can't ever have 2 or
> more connections. And so any data it stores cannot ever be accessed by
> another connection, so all forms of replication are excluded and all
> maintenance actions force the database to be unavailable for a period
> of time. Those two things are barriers of the most major kind to
> anybody working in an enterprise with connected data and devices. The
> only people that want this are people with a very short term view of
> the purpose of their applications, and disregard for the value and
> permanence of the data stored. They may not want to cope with those
> issues *now* but they will later and won't thank us for implementing
> it in a way that means it can never be achieved.
>
> To be honest, I can't believe I'm reading this.
>
> And worse, it's on our Don't Want list, and nobody has said stop.
>
> It's almost impossible to purchase a CPU these days that doesn't have
> multiple cores, so the whole single-process architecture is just dead.
> Yes, we want Postgres installed everywhere, but this isn't the way to
> achieve that.
>
> I agree we should allow a PostgreSQL installation to work for a single
> user, but I don't see that requires other changes. This idea will
> cause endless bugs, thinkos and severely waste our time. So without a
> much better justification, I don't think we should do this.

I couldn't disagree more. The patch is small, logical, and fixes an
awful problem, namely that --single mode is basically unusable. As to
your wider point (namely, that you can't connect to it, therefore it's
bad), it has got to be refuted by numerous competing solutions in the
market such as http://www.firebirdsql.org/manual/fbmetasecur-embedded.html,
and many others.

While it's not as common as it used to be, now and then a piece of
software needs to distribute an application as part of a boxed
product. Postgres is horrible at this and doesn't have to be; imagine
how much easier the lives of poker tracker would be (for *some* of its
users) with an integrated standalone mode: google 'poker tracker
postgresql' and take a good long look at problems people face in this
scenario.

merlin

On 12 November 2012 21:26, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> I couldn't disagree more. The patch is small, logical, and fixes an
> awful problem, namely that --single mode is basically unusable. As to
> your wider point (namely, that you can't connect to it, therefore it's
> bad), it has got to be refuted by numerous competing solutions in the
> market such as http://www.firebirdsql.org/manual/fbmetasecur-embedded.html,
> and many others.

Small is not an argument in favour, just a statement of ease, like
jumping off a cliff. i.e. lemmings.

> While it's not as common as it used to be, now and then a piece of
> software needs to distribute an application as part of a boxed
> product. Postgres is horrible at this and doesn't have to be; imagine
> how much easier the lives of poker tracker would be (for *some* of its
> users) with an integrated standalone mode: google 'poker tracker
> postgresql' and take a good long look at problems people face in this
> scenario.

I get the installability thang, very very much, I just don't see the
single process thing as the only solution. At very least an open
minded analysis of the actual problem and ways of solving it is called
for, not just reach for a close to hand solution.

I don't ever want to hear someone reject a patch cos it would mess up
poker tracker. The point is it complicates the code, introduces
restrictions into what is possible and is just more inertia onto
development.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Monday, November 12, 2012 8:31 PM Merlin Moncure wrote:
On Fri, Sep 14, 2012 at 6:42 AM, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> On Tuesday, September 11, 2012 7:07 PM Amit Kapila wrote:
> On Monday, September 10, 2012 8:20 PM Amit Kapila wrote:
> On Sunday, September 09, 2012 1:37 PM Amit Kapila wrote:
> On Friday, September 07, 2012 11:19 PM Tom Lane wrote:
> Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> >>>>> Would socketpair(2) be simpler?
>
>>>> I have started working on this patch to make it work on Windows. The 3
> main things to make it work are:
>
>>>The patch which contains Windows implementation as well is attached with this mail. It contains changes related to following
>>>1. waitpid
>>>2. socketpair
>>3. fork-exec
>
>>>The following is still left:
>>> 1. Error handling in all paths
>
>> The modified version 2 contains error handling in all paths.

> I didn't see that this patch was added to a commitfest -- should it
> have been? I very much like Tom's proposed starting point for this
> feature as a replacement for --single. Hate to see this die on the
> vine. Would some testing on windows be what's needed to get the ball
> rolling?

After Windows implementation, I have done first level tests also to make sure it works.
I think Tom is the right person to comment on how to see this patch move forward.
I am not sure what's in his mind that he didn't provide the feedback or proceeded to complete it.
Is it due to time or he might have forseen some design or usecase problem, if it's due to time then I think it can be persuaded.

With Regards,
Amit Kapila.

On Tuesday, November 13, 2012 3:11 AM Simon Riggs wrote:
On 12 November 2012 21:26, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

>> I couldn't disagree more. The patch is small, logical, and fixes an
>> awful problem, namely that --single mode is basically unusable. As to
>> your wider point (namely, that you can't connect to it, therefore it's
>> bad), it has got to be refuted by numerous competing solutions in the
>> market such as http://www.firebirdsql.org/manual/fbmetasecur-embedded.html,
>> and many others.

As far as I remember even MySQL provides such a mode.

> Small is not an argument in favour, just a statement of ease, like
>jumping off a cliff. i.e. lemmings.

>> While it's not as common as it used to be, now and then a piece of
>> software needs to distribute an application as part of a boxed
>> product. Postgres is horrible at this and doesn't have to be; imagine
>> how much easier the lives of poker tracker would be (for *some* of its
>> users) with an integrated standalone mode: google 'poker tracker
>> postgresql' and take a good long look at problems people face in this
>> scenario.

>I get the installability thang, very very much, I just don't see the
>single process thing as the only solution. At very least an open
>minded analysis of the actual problem and ways of solving it is called
>for, not just reach for a close to hand solution.

Some other usecase where I have seen it required is in telecom billing apps.
In telecom application where this solution works, needs other maintainence connections as well.
Some of the reasons for its use are performance and less maintainence overhead and also their data requirements are
also not so high.
So even if this solution doesn't meet all requirements of single process solution (and neither I think it is written to address all) but can't we think of it as first version and then based on requirements extend it to have other capabilities:
a. to have a mechnism for other background processes (autovacuum, checkpoint, ..).
b. more needs to be thought of..

With Regards,
Amit Kapila.

On 2012-11-12 19:21:28 +0000, Simon Riggs wrote:
> On 10 September 2012 17:50, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > The point of the proposal that I am making is to have a simple,
> > low-maintenance solution for people who need a single-application
> > database. A compromise somewhere in the middle isn't likely to be an
> > improvement for anybody. For instance, if you want to have additional
> > connections, you open up a whole collection of communication and
> > authentication issues, which potential users of a single-application
> > database don't want to cope with.
>
> So the proposal is to implement a database that can't ever have 2 or
> more connections.
> ...
> It's almost impossible to purchase a CPU these days that doesn't have
> multiple cores, so the whole single-process architecture is just dead.
> Yes, we want Postgres installed everywhere, but this isn't the way to
> achieve that.
>
> I agree we should allow a PostgreSQL installation to work for a single
> user, but I don't see that requires other changes. This idea will
> cause endless bugs, thinkos and severely waste our time. So without a
> much better justification, I don't think we should do this.

I personally think that a usable & scriptable --single mode is
justification enough, even if you don't aggree with the other
goals. Having to wait for hours just enter one more command because
--single doesn't support any scripts sucks. Especially in recovery
situations.

I also don't think a single-backend without further child processes is
all that helpful - but I think this might be a very useful stepping
stone.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 13 November 2012 06:14, Amit kapila <amit(dot)kapila(at)huawei(dot)com> wrote:

>>I get the installability thang, very very much, I just don't see the
>>single process thing as the only solution. At very least an open
>>minded analysis of the actual problem and ways of solving it is called
>>for, not just reach for a close to hand solution.
>
> Some other usecase where I have seen it required is in telecom billing apps.
> In telecom application where this solution works, needs other maintainence connections as well.
> Some of the reasons for its use are performance and less maintainence overhead and also their data requirements are
> also not so high.
> So even if this solution doesn't meet all requirements of single process solution (and neither I think it is written to address all) but can't we think of it as first version and then based on requirements extend it to have other capabilities:
> a. to have a mechnism for other background processes (autovacuum, checkpoint, ..).
> b. more needs to be thought of..

Why would we spend time trying to put back something that is already
there? Why not simply avoid removing it in the first place?

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs escribió:

> > So even if this solution doesn't meet all requirements of single
> > process solution (and neither I think it is written to address all)
> > but can't we think of it as first version and then based on
> > requirements extend it to have other capabilities:
> > a. to have a mechnism for other background processes (autovacuum, checkpoint, ..).
> > b. more needs to be thought of..
>
> Why would we spend time trying to put back something that is already
> there? Why not simply avoid removing it in the first place?

Actually, the whole point of this solution originally was just to serve
pg_upgrade needs, so that it doesn't have to start a complete postmaster
environment just to have to turn off most of what postmaster does, and
with enough protections to disallow everyone else from connecting.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 13 November 2012 13:05, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> Simon Riggs escribió:
>
>> > So even if this solution doesn't meet all requirements of single
>> > process solution (and neither I think it is written to address all)
>> > but can't we think of it as first version and then based on
>> > requirements extend it to have other capabilities:
>> > a. to have a mechnism for other background processes (autovacuum, checkpoint, ..).
>> > b. more needs to be thought of..
>>
>> Why would we spend time trying to put back something that is already
>> there? Why not simply avoid removing it in the first place?
>
> Actually, the whole point of this solution originally was just to serve
> pg_upgrade needs, so that it doesn't have to start a complete postmaster
> environment just to have to turn off most of what postmaster does, and
> with enough protections to disallow everyone else from connecting.

I don't see anything that pg_upgrade is doing that causes the need to
support a special mode.

From other people's comments it's clear that "single user mode" is
desirable to many and *will* be widely deployed if we allow it. I
support the wish to allow a database server to be limited by
configuration to a single user. However, supporting a specifically
targeted mode that presents single user as an architectural
design/limitation is a regressive step that I am strongly opposed to.

The most popular relational database in the world is Microsoft Access,
not MySQL. Access appears desirable because it allows a single user to
create and use a database (which is very good). But all business
databases have a requirement for at least one of: high availability,
multi-user access or downstream processing in other parts of the
business. Businesses worldwide curse the difficulties caused by having
critical business data in desktop databases. And worldwide, there are
also many that don't understand the problems that disconnected data
causes because they can't see past the initial benefit.

The lessons from that are that its OK to start with a database used by
a single person, but that database soon needs to allow access from
multiple users or automated agents. Many database systems support
embedded or single user mode as an architectural option. All of those
systems cause headaches in all of the businesses where they are used.
They also cause problems on small detached devices such as phones,
because even on very small systems there is a requirement for multiple
concurrently active processes each of which may need database access.

PostgreSQL was designed from the ground up as a multi-user database.
This is the very fact that puts us in a good position to become
pervasive. A single database system that works the same on all
devices, with useful replication to connect data together.

The embedded or single mode concept has long been on the "do not want"
list. I believe that is a completely rational and strongly desirable
thing. Supporting multiple architectures is extra work, and the
restrictive architecture bites people in the long term. The fact that
its an "easy patch" is not a great argument for changing that
position, and in fact, its not easy, since it comes with a request to
make it work on Windows (= extra work). The "easy" bit is not proven
since people are already starting to ask about bgwriter and
autovacuum.

In this release there is much work happening around providing
additional autonomous agents (bgworker) and other work around flexible
replication (BDR), all of which would be nullified by the introduction
and eventual wide usage of a restrictive new architecture.

Single user configuration option, yes. Architecturally limited special
version of PostgreSQL, no.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> The most popular relational database in the world is Microsoft Access,
> not MySQL. Access appears desirable because it allows a single user to
> create and use a database (which is very good). But all business
> databases have a requirement for at least one of: high availability,
> multi-user access or downstream processing in other parts of the
> business.

That's a mighty sweeping claim, which you haven't offered adequate
evidence for. The fact of the matter is that there is *lots* of demand
for simple single-user databases, and what I'm proposing is at least a
first step towards getting there.

The main disadvantage of approaching this via the existing single-user
mode is that you won't have any autovacuum, bgwriter, etc, support.
But the flip side is that that lack of infrastructure is a positive
advantage for certain admittedly narrow use-cases, such as disaster
recovery and pg_upgrade. So while I agree that this isn't the only
form of single-user mode that we'd like to support, I think it is *a*
form we'd like to support, and I don't see why you appear to be against
having it at all.

A more reasonable objection would be that we need to make sure that this
isn't foreclosing the option of having a multi-process environment with
a single user connection. I don't see that it is, but it might be wise
to sketch exactly how that case would work before accepting this.

regards, tom lane

On Tue, Nov 13, 2012 at 12:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
>> The most popular relational database in the world is Microsoft Access,
>> not MySQL. Access appears desirable because it allows a single user to
>> create and use a database (which is very good). But all business
>> databases have a requirement for at least one of: high availability,
>> multi-user access or downstream processing in other parts of the
>> business.
>
> That's a mighty sweeping claim, which you haven't offered adequate
> evidence for. The fact of the matter is that there is *lots* of demand
> for simple single-user databases, and what I'm proposing is at least a
> first step towards getting there.
>
> The main disadvantage of approaching this via the existing single-user
> mode is that you won't have any autovacuum, bgwriter, etc, support.
> But the flip side is that that lack of infrastructure is a positive
> advantage for certain admittedly narrow use-cases, such as disaster
> recovery and pg_upgrade. So while I agree that this isn't the only
> form of single-user mode that we'd like to support, I think it is *a*
> form we'd like to support, and I don't see why you appear to be against
> having it at all.
>
> A more reasonable objection would be that we need to make sure that this
> isn't foreclosing the option of having a multi-process environment with
> a single user connection. I don't see that it is, but it might be wise
> to sketch exactly how that case would work before accepting this.

I'm not particularly excited about providing more single-user mode
options, but I think it's worth having this particular thing because
it makes pg_upgrade more robust. Whether we do anything else is
something we can litigate when the time comes.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 13 November 2012 17:38, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
>> The most popular relational database in the world is Microsoft Access,
>> not MySQL. Access appears desirable because it allows a single user to
>> create and use a database (which is very good). But all business
>> databases have a requirement for at least one of: high availability,
>> multi-user access or downstream processing in other parts of the
>> business.
>
> That's a mighty sweeping claim, which you haven't offered adequate
> evidence for. The fact of the matter is that there is *lots* of demand
> for simple single-user databases, and what I'm proposing is at least a
> first step towards getting there.

I agree there is lots of demand for simple single-user databases and I
wish that too. What I don't agree with is something that casts that
requirement in stone by architecturally/permanently disallowing
secondary connections.

Evidence for claims:
* The whole Business Intelligence industry relies on being able to
re-purpose existing data, forming integrated webs of interconnecting
databases. All of that happens after the initial developers write the
first version of the database application.
* Everybody wants a remote backup, whether its for your mobile phone
contact list or your enterprise datastore.

People are migrating away from embedded databases in droves for these
very reasons.

> The main disadvantage of approaching this via the existing single-user
> mode is that you won't have any autovacuum, bgwriter, etc, support.
> But the flip side is that that lack of infrastructure is a positive
> advantage for certain admittedly narrow use-cases, such as disaster
> recovery and pg_upgrade. So while I agree that this isn't the only
> form of single-user mode that we'd like to support, I think it is *a*
> form we'd like to support, and I don't see why you appear to be against
> having it at all.

I have no problem with people turning things off, I reject the idea
that we should encourage people to never be able to turn them back on.

> A more reasonable objection would be that we need to make sure that this
> isn't foreclosing the option of having a multi-process environment with
> a single user connection. I don't see that it is, but it might be wise
> to sketch exactly how that case would work before accepting this.

Whatever we provide will become the norm. I don't have a problem with
you providing BOTH the proposed single user mode AND the multi-process
single user connection mode in this release. But if you provide just
one of them and its the wrong one, we will be severely hampered in the
future.

Yes, I am very much against this project producing a new DBMS
architecture that works on top of PostgreSQL data files, yet prevents
maintenance, backup, replication and multi-user modes.

I see this decision as a critical point for this project, so please
consider this objection and where it comes from.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On 13 November 2012 17:38, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> ... The fact of the matter is that there is *lots* of demand
>> for simple single-user databases, and what I'm proposing is at least a
>> first step towards getting there.

> I agree there is lots of demand for simple single-user databases and I
> wish that too. What I don't agree with is something that casts that
> requirement in stone by architecturally/permanently disallowing
> secondary connections.

If you want secondary connections, then I think you want a postmaster.
We already have umpteen ways to limit who can connect (for example,
putting the socket in a directory with limited access rights), and in
that sort of situation I don't see why you'd really want a database
that is only accessible when the "main" client is running.

The case that this patch is meant to address is one where there is only
one client application, period, and you'd rather that the database
starts and stops automatically with that application instead of needing
any management complexity. Now we can debate whether we want only one
process or multiple processes underneath the client application, but
I think the restriction to one client connection is a key *feature*
not a bug, precisely because it removes a whole bunch of user-visible
complexity that we cannot escape otherwise.

> People are migrating away from embedded databases in droves for these
> very reasons.

[ shrug... ] If they don't want an embedded database, they won't want
this either, but there are still plenty of people left who do want an
embedded database. We've never had an adequate offering for those
people before. If we ratchet up the management complexity of "single
user" mode then it still won't be an adequate offering for them.

> I see this decision as a critical point for this project, so please
> consider this objection and where it comes from.

I think this is nonsense. It's not critical; it's a very small patch
that provides a feature of interest to a limited audience. And I don't
believe it's foreclosing providing other operating modes later, unless
maybe people feel this is "almost good enough" and lose motivation to
work on those other operating modes. But if that happens, then I'd say
the demand for the other modes isn't as high as you think.

regards, tom lane

Preface: I think there's some great commentary here, and find myself
agreeing
pretty whole-heartedly.

On Tue, Nov 13, 2012 at 2:45 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On 13 November 2012 17:38, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> >> The most popular relational database in the world is Microsoft Access,
> >> not MySQL. Access appears desirable because it allows a single user to
> >> create and use a database (which is very good). But all business
> >> databases have a requirement for at least one of: high availability,
> >> multi-user access or downstream processing in other parts of the
> >> business.
> >
> > That's a mighty sweeping claim, which you haven't offered adequate
> > evidence for. The fact of the matter is that there is *lots* of demand
> > for simple single-user databases, and what I'm proposing is at least a
> > first step towards getting there.
>
> I agree there is lots of demand for simple single-user databases and I
> wish that too. What I don't agree with is something that casts that
> requirement in stone by architecturally/permanently disallowing
> secondary connections.
>
> Evidence for claims:
> * The whole Business Intelligence industry relies on being able to
> re-purpose existing data, forming integrated webs of interconnecting
> databases. All of that happens after the initial developers write the
> first version of the database application.
> * Everybody wants a remote backup, whether its for your mobile phone
> contact list or your enterprise datastore.
>
> People are migrating away from embedded databases in droves for these
> very reasons.
>

There seems to be a continuum of different sorts of scenarios of
more-to-less
concurrency that are desirable for some different reasons. From
most-to-least,
I can see:

1 - Obviously, there's the case that Postgres is eminently good at, of
supporting
many users concurrently using a database. We love that, let's not break
it :-).

2 - We have found it useful to have some extra work processes that do some
useful internal things, such as vacuuming, forcing background writes,
collecting statistics. And an online backup requires having a second
process.

3 - People doing embedded systems find it attractive to attach all the data
to the singular user running the system. Witness the *heavy* deployment
of SQLite on Android and iOS. People make an assumption that this is
a single-process thing, but I am inclined to be a bit skeptical. What
they
*do* know is that it's convenient to not spawn extra processes and do
IPC. That's not quite the same thing as it being a certainty that they
definitely want not to have more than one process.

4 - There are times when there *is* certainty about not wanting there to be
more
than one process. When running pg_upgrade, or, at certain times, when
doing streaming replication node status switches, one might have that
certainty. Or when reindexing system tables, which needs single user
mode.

For us to conflate the 3rd and 4th items seems like a mistake to me.

> > The main disadvantage of approaching this via the existing single-user
> > mode is that you won't have any autovacuum, bgwriter, etc, support.
> > But the flip side is that that lack of infrastructure is a positive
> > advantage for certain admittedly narrow use-cases, such as disaster
> > recovery and pg_upgrade. So while I agree that this isn't the only
> > form of single-user mode that we'd like to support, I think it is *a*
> > form we'd like to support, and I don't see why you appear to be against
> > having it at all.
>
> I have no problem with people turning things off, I reject the idea
> that we should encourage people to never be able to turn them back on.
>

Yep. That seems like conflating #2 with #4.

It's mighty attractive to have a forcible "single process mode" to add
safety to
certain activities.

I think we need a sharper knife, though, so we don't ablate off stuff like
#2, just
because someone imagined that "Must Have Single Process!!!" was the right
doctrine.

> > A more reasonable objection would be that we need to make sure that this
> > isn't foreclosing the option of having a multi-process environment with
> > a single user connection. I don't see that it is, but it might be wise
> > to sketch exactly how that case would work before accepting this.
>
> Whatever we provide will become the norm. I don't have a problem with
> you providing BOTH the proposed single user mode AND the multi-process
> single user connection mode in this release. But if you provide just
> one of them and its the wrong one, we will be severely hampered in the
> future.
>
> Yes, I am very much against this project producing a new DBMS
> architecture that works on top of PostgreSQL data files, yet prevents
> maintenance, backup, replication and multi-user modes.
>
> I see this decision as a critical point for this project, so please
> consider this objection and where it comes from.
>

I don't think we're necessarily *hugely* hampered by doing one of these;
I don't think it precludes doing the other.

Pointedly, I think it's a fine thing to have a way to force the system into
a single process to support #4 (e.g. - Single Process Mode to support
recovery from failover, pg_upgrade, and other pretty forcible SUM stuff).
That's useful to lots of people doing non-embedded cases.

That doesn't preclude allowing a somewhat more in-between case later.

Perhaps in-between is supported by slightly richer configuration:
- pg_hba.conf knows about replication connections; perhaps we could
augment it to have rules for the "internal daemons" like autovac, stats
collector, and background writer.
- pg_hba.conf might be augmented to forbid >1 connection per user,
for regular connections?

Possibly I'm not solving the true problem, of course.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> I agree there is lots of demand for simple single-user databases and I
>> wish that too. What I don't agree with is something that casts that
>> requirement in stone by architecturally/permanently disallowing
>> secondary connections.
>
> If you want secondary connections, then I think you want a postmaster.

I would agree. I think you're both talking above each other, and that
what Simon is worried about (but I haven't asked him about that before
sending that email) is how to change the application setup to switch
from single user mode to multi user mode.

IIRC the way to implement single user mode in your application is quite
low-level with this patch, so switching to multi-user mode is not about
just changing the connection string, or is it?

> The case that this patch is meant to address is one where there is only
> one client application, period, and you'd rather that the database
> starts and stops automatically with that application instead of needing
> any management complexity. Now we can debate whether we want only one
> process or multiple processes underneath the client application, but
> I think the restriction to one client connection is a key *feature*
> not a bug, precisely because it removes a whole bunch of user-visible
> complexity that we cannot escape otherwise.

Well I think your patch would be easier to accept as is if it was
documented only as a psql friendly single-user mode. I would really
welcome that.

> embedded database. We've never had an adequate offering for those
> people before. If we ratchet up the management complexity of "single
> user" mode then it still won't be an adequate offering for them.

Now, if we're talking about single user mode as in embedded database, I
really do think this patch should include a solution to run online
maintainance, logical and physical backups, replication, archiving and
all the production grade features you expect from PostgreSQL.

And then I understand Simon's POV about code complexity and bgworkers
for examples, which will *need* to be taken care of in that solution.

> I think this is nonsense. It's not critical; it's a very small patch
> that provides a feature of interest to a limited audience. And I don't

Yes, it's providing full psql capabilities where we only had that bizare
postgres --single interface. Maybe it will make initdb and debugging it
easier too.

> believe it's foreclosing providing other operating modes later, unless
> maybe people feel this is "almost good enough" and lose motivation to
> work on those other operating modes. But if that happens, then I'd say
> the demand for the other modes isn't as high as you think.

Again, my concern on that point after reading Simon's comments is only
about the production procedure you have to follow to switch your
application from single user mode to multi user mode.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support