serverless postgresql

[Rick Gigger]

Re: serverless postgresql

This is just what I recommended (I think) in my earlier post. This would be
FANTASTIC. Then I could scrap sqlite and any sqlite custom code that I end
up having to write (there is a little). Plus I could have the reliability
of a multi-process postgres "embedded".

Now if they just finish the Win32 port sometime soon...

----- Original Message -----
From: "Jonathan Bartlett" <johnnyb@eskimo .com>
To: "Rick Gigger" <rick@alpinenet working.com>
Cc: "Jeff Bowden" <jlb@houseofdis traction.com>; "Tom Lane"
<tgl@sss.pgh.pa .us>; <pgsql-general@postgre sql.org>
Sent: Wednesday, January 14, 2004 2:07 PM
Subject: Re: [GENERAL] serverless postgresql

[color=blue][color=green]
> > Anyway since postgres uses WAL files to verify the integrity of the[/color][/color]
database[color=blue][color=green]
> > couldn't it more or less make the same guarantee's in an embedded[/color][/color]
version?[color=blue][color=green]
> > As long as the app uses the db libs unmodified and doesn't mess with the
> > files it creates how does simply making it embedded increase the change[/color][/color]
of[color=blue][color=green]
> > db errors resulting in database corruption?[/color]
>
> I have a different idea. I've been thinking about coding it, but haven't
> had the time. This could be done with no changes to Postgres itself.
>
> Basically, you would have a library which exported functions such as
>
> pg_instance *pg_start(char *directory);
> pg_get_connecti on(pg_instance *);
> pg_stop(pg_inst ance *);
> pg_initdb(char *directory)
>
> pg_start would do the following:
> 1) check "directory" for an instance of the UNIX socket.
> if it is there, make a Postgres connection
> if it is not there, start the Postmaster server with "-k directory
> -D directory/data" and then make a Postgres connection
> 2) Create a struct to contain the directory and any other data item we
> need to connect to the database
> 3) Return this structure
>
> pg_get_connecti on would just be a wrapper for pq_connect()
>
> pg_stop would kill the database.
>
> pg_initdb would simply run initdb
>
> Does anyone see a reason why this wouldn't work?
>
> Jon
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[David Garamond]

embedded/&quot;serverles s&quot; (Re: serverless postgresql)

Jeff Bowden wrote:[color=blue]
> For ease of configuration and other reasons, I would like for my
> single-user GUI app to be able to use postgresql in-process as a library
> accessing a database created in the users home directory. I think I
> could possibly get what I want by launching a captive copy of postmaster
> with appropriate args but it seems conceptually cleaner to not have a
> seperate process at all. Has anyone tried to do anything like this?[/color]

[Sorry for not actually answering this question]

I believe the demands for embedded/"serverless " version of PostgreSQL to
increase significantly once PostgreSQL is natively available on Windows.
So I would expect that official embedded support to follow quite shortly
after win32 port has stabilized. :-)

--
dave


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[David Garamond]

Re: serverless postgresql

Rick Gigger wrote:[color=blue]
> I have just about the same sort of needs now and concluded that postgres
> just is not suited for embedding into apps like that.[/color]

Why not? It's not that the PostgreSQL backend is a mammoth like Oracle.
The Firebird embedded version is pretty much the same as their server,
but without network and client authentication layer.

However, embedded usually demands that the backend be threaded.
Otherwise it will be pretty useless/very inconvenient to use in many
apps. Perhaps this is the major change that's hard to do?

--
dave


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[David Garamond]

Re: serverless postgresql

Tom Lane wrote:[color=blue]
> Jeff Bowden <jlb@houseofdis traction.com> writes:
>[color=green]
>>That makes sense to me. I wonder if sqlite suffers for this problem
>>(e.g. app crashing and corrupting the database).[/color]
>
> Likely. I can tell you that Ann Harrison once told me she made a decent
> amount of money as a consultant fixing broken Interbase/Firebird
> database files. It would be hard to make a living in the same game for
> Postgres. Now I don't think that Firebird is any buggier than Postgres.
> But it comes in an embedded-library form; I'll bet lunch that most of
> those data corruption problems were actually induced by crashes of
> surrounding applications.[/color]

Do the developers generally oppose the idea of a threaded (but
non-embedded) backend as well? If the backend is thread-safe, then users
can still choose to run multiprocess or multithreaded right?

--
dave


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Jonathan Bartlett]

Re: serverless postgresql

> Do the developers generally oppose the idea of a threaded (but[color=blue]
> non-embedded) backend as well? If the backend is thread-safe, then users
> can still choose to run multiprocess or multithreaded right?[/color]

I've been under the impression that the developers were opposed to a
threaded server because of the complete lack of consistency in threading
behavior across platforms. However, I don't see how doing it as a
multiprocess server as apposed to a multithreaded server affects embedded
use - as long as it can simply be called from a library, why would a user
or developer care?

Jon


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Paul Ganainm]

Re: serverless postgresql



tgl@sss.pgh.pa. us says...

[color=blue][color=green]
> > That makes sense to me. I wonder if sqlite suffers for this problem
> > (e.g. app crashing and corrupting the database).[/color][/color]

[color=blue]
> Likely. I can tell you that Ann Harrison once told me she made a decent
> amount of money as a consultant fixing broken Interbase/Firebird
> database files. It would be hard to make a living in the same game for
> Postgres. Now I don't think that Firebird is any buggier than Postgres.
> But it comes in an embedded-library form; I'll bet lunch that most of
> those data corruption problems were actually induced by crashes of
> surrounding applications.[/color]


The reason IMHO for "server" crashes in IB/FB is quite simply due to the
fact that newbie types who are running IB/FB will tend to run it on the
crappiest PC in the office under Wintendo 95 on the machine used by the
secretary who's a bit of a ditz and keeps switching it off by mistake,
or spilling her coffee on the exposed hard drive.


No forced writes on, no UPS, no RAID, no server room - nada, zip,
diddly. Basically, it boils down to the fact that just because IB/FB
*_lets_* you do something, it's not necessarily a good idea. IB/FB will
run reasonably on the crappiest of hardware, and on the crappiest of
OS's, which is what leads to the problem.


It's unlikely that PostgreSQL faces this problem, since it will only run
under far more robust OS's, and you're not likely to have the office
eejit running the Unix/Linux/BSD box with the db server on it.


Paul...


--
plinehan y_a_h_o_o and d_o_t com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.

"XML avoids the fundamental question of what we should do,
by focusing entirely on how we should do it."

quote from http://www.metatorial.com


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Rick Gigger]

Re: serverless postgresql

> Rick Gigger wrote:[color=blue][color=green]
> > I have just about the same sort of needs now and concluded that postgres
> > just is not suited for embedding into apps like that.[/color]
>
> Why not? It's not that the PostgreSQL backend is a mammoth like Oracle.
> The Firebird embedded version is pretty much the same as their server,
> but without network and client authentication layer.
>
> However, embedded usually demands that the backend be threaded.
> Otherwise it will be pretty useless/very inconvenient to use in many
> apps. Perhaps this is the major change that's hard to do?[/color]

I should have said that postgres isn't suited for running in-proccess with
the rest of your app. Or at least the developers don't think (and I guess I
agree now) that it's a good idea. They could however make a library that
would start up the server in multiprocess mode and run it in the background.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Rick Gigger]

Re: serverless postgresql

> Tom Lane wrote:[color=blue][color=green]
> > Jeff Bowden <jlb@houseofdis traction.com> writes:
> >[color=darkred]
> >>That makes sense to me. I wonder if sqlite suffers for this problem
> >>(e.g. app crashing and corrupting the database).[/color]
> >
> > Likely. I can tell you that Ann Harrison once told me she made a decent
> > amount of money as a consultant fixing broken Interbase/Firebird
> > database files. It would be hard to make a living in the same game for
> > Postgres. Now I don't think that Firebird is any buggier than Postgres.
> > But it comes in an embedded-library form; I'll bet lunch that most of
> > those data corruption problems were actually induced by crashes of
> > surrounding applications.[/color]
>
> Do the developers generally oppose the idea of a threaded (but
> non-embedded) backend as well? If the backend is thread-safe, then users
> can still choose to run multiprocess or multithreaded right?[/color]

My impression is that they don't want to go multithreaded. There was some
talk of this for the windows port but I belive that Bruce is simply
modifying the multi-proccess code so that it will work on windows.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Tom Lane]

Re: serverless postgresql

David Garamond <lists@zara.6.i sreserved.com> writes:[color=blue]
> Do the developers generally oppose the idea of a threaded (but
> non-embedded) backend as well? If the backend is thread-safe, then users
> can still choose to run multiprocess or multithreaded right?[/color]

The backend isn't thread-safe. There have been repeated discussions
about using threading in the backend (see the pgsql-hackers archives),
but so far no one has made a convincing case for it.

BTW, this whole discussion is getting pretty off-topic for -general;
I'd suggest pursuing it on -hackers.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Chris Travers]

Re: embedded/&quot;serverles s&quot; (Re: serverless postgresql)

From: "David Garamond" <lists@zara.6.i sreserved.com>[color=blue]
> I believe the demands for embedded/"serverless " version of PostgreSQL to
> increase significantly once PostgreSQL is natively available on Windows.
> So I would expect that official embedded support to follow quite shortly
> after win32 port has stabilized. :-)[/color]

People are always asking for embedded dbms's without really considering the
consequences. For example, if you need to share data, you end up with all
the MS Access sorts of issues. I know because I used to work at Microsoft
in the department that provided support both for Access and the developer
products. I suspect the support nightmares may be part of the reason for
pushing MSDE, and hence *get away* from the embedded dbms model ;-)

For example, do you realize that the marketing info said that Access
supported 256 *concurrent* users to a database? At the same time
troubleshooting corruption was nearly unsupported for a while.

I agree with the approach of a wrapper library which would wrap the
startup/shutdown of a postgresql server so that the programmer doesn't have
to worry about the details, but I would add another idea-- namely that the
library should be able to determine whether the server is running remotely,
and simply pass the connection to libpq. This would also create a
conceptually cleaner framework for configuration of software which may need
to access a local or remote data store.

Best Wishes,
Chris Travers


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Re: embedded/&quot;serverles s&quot; (Re: serverless postgresql)

"Chris Travers" <chris@travelam ericas.com> writes:[color=blue]
> I agree with the approach of a wrapper library which would wrap the
> startup/shutdown of a postgresql server so that the programmer doesn't have
> to worry about the details,[/color]

The reason that the client programmer doesn't have to worry about
starting/stopping the database is that it's not his responsibility.
I don't think that having the client control this is a good idea at all.
David conveniently ignored the points I made before, but they are
real issues --- if the client is in charge of starting or stopping the
DB, it just adds potential for mucking things up. I can see the bug
reports now: "I decided I'd make the shutdown routine 'kill -9' the
postmaster because I didn't like the multi-second delay for a normal
shutdown. Now my database is corrupt."

Another set of objections to this center around the fact that with this
sort of arrangement, the database files would necessarily belong to the
client user, since there's no way to launch the postmaster as a
different userid. (Unless the client is running as root, which I
sincerely hope he is not.) That means there's no filesystem protection
between the client and the database, which is another recipe for
trouble. Not much point in keeping an address-space firewall between
client and server when the client can scribble on the database anyway.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Rick Gigger]

Re: embedded/&quot;serverles s&quot; (Re: serverless postgresql)

> "Chris Travers" <chris@travelam ericas.com> writes:[color=blue][color=green]
> > I agree with the approach of a wrapper library which would wrap the
> > startup/shutdown of a postgresql server so that the programmer doesn't[/color][/color]
have[color=blue][color=green]
> > to worry about the details,[/color]
>
> The reason that the client programmer doesn't have to worry about
> starting/stopping the database is that it's not his responsibility.
> I don't think that having the client control this is a good idea at all.
> David conveniently ignored the points I made before, but they are
> real issues --- if the client is in charge of starting or stopping the
> DB, it just adds potential for mucking things up. I can see the bug
> reports now: "I decided I'd make the shutdown routine 'kill -9' the
> postmaster because I didn't like the multi-second delay for a normal
> shutdown. Now my database is corrupt."[/color]

I recall a discussion a while back where people were complaining that some
os (I think it was mac os x) would just kill all proccesses after something
like 30 seconds on system shutdown if they didn't quit fast enough on their
own. The response was that because of how postgres uses wal files to
prevent db corruption this was not an issue. Why does this not apply in
this situation?
[color=blue]
> Another set of objections to this center around the fact that with this
> sort of arrangement, the database files would necessarily belong to the
> client user, since there's no way to launch the postmaster as a
> different userid. (Unless the client is running as root, which I
> sincerely hope he is not.) That means there's no filesystem protection
> between the client and the database, which is another recipe for
> trouble. Not much point in keeping an address-space firewall between
> client and server when the client can scribble on the database anyway.[/color]

By saying "the client can scribble on the database anyway" do you do you
mean the client app actually writing over the db files on disk? It seems
like this would only be a problem with an exceptionally stupid programmer.
How could that happen on accident?


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Rick Gigger]

Re: embedded/&quot;serverles s&quot; (Re: serverless postgresql)

> From: "David Garamond" <lists@zara.6.i sreserved.com>[color=blue][color=green]
> > I believe the demands for embedded/"serverless " version of PostgreSQL to
> > increase significantly once PostgreSQL is natively available on Windows.
> > So I would expect that official embedded support to follow quite shortly
> > after win32 port has stabilized. :-)[/color]
>
> People are always asking for embedded dbms's without really considering[/color]
the[color=blue]
> consequences. For example, if you need to share data, you end up with all
> the MS Access sorts of issues. I know because I used to work at Microsoft
> in the department that provided support both for Access and the developer
> products. I suspect the support nightmares may be part of the reason for
> pushing MSDE, and hence *get away* from the embedded dbms model ;-)[/color]

My argument of having an embedded or pseudo-embedded postgres is this:

I am never, ever going to trust any really, really important data to an
embedded database. If the user has it on his system the moron might delete
the database files in which case it really doesn't matter how your
protecting it. The user can potentially screw everything up. The reason I
need an embedded database is that sometime my clients need to take a copy of
the app off line and look the info, make reports etc. They also may
eventually be filling out small amounts of data that can then be synced with
the live database once they are back on line. If a clients thinkpad lights
on fire while he's on the plane and he loses 6 hours of data entry well
that's not my problem. But I've ALWAYS got most of the data in a nice
consistent postgres database on my server which is backed up constantly to
multiple locations.

So there are cases where reliability requirements just go down and what I
want is something that acts exactly like postgres so that I don't have to
write custom code for 2 databases. Since I end up just running sqlite
anyway I would prefer to just have an embedded (or pseudo-embedded)
postgres. I'm not going to get all up in arms if something bad happens
because I understand the risks of running it embedded. But those risks will
be the same with any embedded app. I just don't care.

Does this apply to the vast majority of embedded users or is it just me?


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Tom Lane]

Re: embedded/&quot;serverles s&quot; (Re: serverless postgresql)

"Rick Gigger" <rick@alpinenet working.com> writes:[color=blue][color=green]
>> ... just adds potential for mucking things up. I can see the bug
>> reports now: "I decided I'd make the shutdown routine 'kill -9' the
>> postmaster because I didn't like the multi-second delay for a normal
>> shutdown. Now my database is corrupt."[/color][/color]
[color=blue]
> I recall a discussion a while back where people were complaining that some
> os (I think it was mac os x) would just kill all proccesses after something
> like 30 seconds on system shutdown if they didn't quit fast enough on their
> own. The response was that because of how postgres uses wal files to
> prevent db corruption this was not an issue. Why does this not apply in
> this situation?[/color]

Because a system shutdown doesn't leave anything else running. kill -9
on the postmaster leaves orphaned backends still running, and orphaned
shared memory segments still in existence. The latter could prevent you
from starting a fresh postmaster (because two shmem segments could
exceed the kernel's SHMMAX) until you do manual cleanup, which most
people don't know how to do. If you do succeed in starting another
postmaster with a fresh shared memory segment, you will have two
independent sets of backends modifying the database files with no
interlocking, which is a sure recipe for data corruption.

There is an interlock that is supposed to prevent this catastrophic
scenario, but I do not trust it unreservedly (and not at all on
non-Unix-derived platforms, because it depends on some rather arcane
features of the SysV shared memory API, which might not be implemented
fully on other platforms).

This is why the "don't kill -9 the postmaster" TIP is still around, BTW.
It has nothing to do with crash safety.
[color=blue]
> By saying "the client can scribble on the database anyway" do you do you
> mean the client app actually writing over the db files on disk? It seems
> like this would only be a problem with an exceptionally stupid programmer.
> How could that happen on accident?[/color]

Scribbling on memory that doesn't belong to you isn't something one does
intentionally, either --- but it happens. However, my real concern here
is not so much with program bugs as clueless users. We see enough cases
already of people removing lock files or "unnecessar y" log files; I fear
it'd be a lot worse if those files were sitting in the user's home
directory.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Jeff Bowden]

Re: embedded/&quot;serverles s&quot; (Re: serverless postgresql)

Tom Lane wrote:
[color=blue]
>"Chris Travers" <chris@travelam ericas.com> writes:
>
>[color=green]
>>I agree with the approach of a wrapper library which would wrap the
>>startup/shutdown of a postgresql server so that the programmer doesn't have
>>to worry about the details,
>>
>>[/color]
>
>The reason that the client programmer doesn't have to worry about
>starting/stopping the database is that it's not his responsibility.
>I don't think that having the client control this is a good idea at all.
>David conveniently ignored the points I made before, but they are
>real issues --- if the client is in charge of starting or stopping the
>DB, it just adds potential for mucking things up. I can see the bug
>reports now: "I decided I'd make the shutdown routine 'kill -9' the
>postmaster because I didn't like the multi-second delay for a normal
>shutdown. Now my database is corrupt."
>
>Another set of objections to this center around the fact that with this
>sort of arrangement, the database files would necessarily belong to the
>client user, since there's no way to launch the postmaster as a
>different userid. (Unless the client is running as root, which I
>sincerely hope he is not.) That means there's no filesystem protection
>between the client and the database, which is another recipe for
>trouble. Not much point in keeping an address-space firewall between
>client and server when the client can scribble on the database anyway.
>
>
>[/color]

Still, the main problem I, and I suspect others, would like to solve is
installation/configuration. For my app I don't want the user to have to
understand anything about how keeping data in a shared
system-administered database is different from keeping data in local
files. Everything should "just work". There is no requirement for
concurrent access.

So kill -9 on postmaster can lead to database corruption? What happens
in a power failure?





---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org