473,836 Members | 2,340 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

serverless postgresql

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?

I've looked at sqlite and it might be workable for my project but I
would prefer the more standard and featureful SQL of postgres. In
particular sqlite lacks date arithmetic and has some funny type issues
(it claims to be typeless, except when it isn't, or something).

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

Nov 22 '05
59 7227


tg*@sss.pgh.pa. us says...

That makes sense to me. I wonder if sqlite suffers for this problem
(e.g. app crashing and corrupting the database).

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.

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

Nov 22 '05 #21
> Rick Gigger wrote:
I have just about the same sort of needs now and concluded that postgres
just is not suited for embedding into apps like that.


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?


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 ma*******@postg resql.org)

Nov 22 '05 #22
> Tom Lane wrote:
Jeff Bowden <jl*@houseofdis traction.com> writes:
That makes sense to me. I wonder if sqlite suffers for this problem
(e.g. app crashing and corrupting the database).


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.


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?


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 ma*******@postg resql.org)

Nov 22 '05 #23
David Garamond <li***@zara.6.i sreserved.com> writes:
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?


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

Nov 22 '05 #24
From: "David Garamond" <li***@zara.6.i sreserved.com>
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. :-)


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

Nov 22 '05 #25
"Chris Travers" <ch***@travelam ericas.com> writes:
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,


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

Nov 22 '05 #26
> "Chris Travers" <ch***@travelam ericas.com> writes:
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,
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."


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?
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.


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

Nov 22 '05 #27
> From: "David Garamond" <li***@zara.6.i sreserved.com>
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. :-)
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 ;-)


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 ma*******@postg resql.org

Nov 22 '05 #28
"Rick Gigger" <ri**@alpinenet working.com> writes:
... 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."
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?
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.
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?


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 ma*******@postg resql.org

Nov 22 '05 #29
Tom Lane wrote:
"Chris Travers" <ch***@travelam ericas.com> writes:

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,


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.


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 ma*******@postg resql.org

Nov 22 '05 #30

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
12726
by: codepirate | last post by:
Hi All, I am trying to connct to a serverless/embedded PostgreSQL database from a C# application using VS 2005. First of all, is this possible with PostgreSQL? I know this can be done with some other database providers (sqllite?). If anyone has connected to such a database using some other method/language I'd be greatful for the information. Thank you in advance for any information.
0
9810
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9656
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10821
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10573
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10241
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9358
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6973
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4001
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.