473,836 Members | 2,172 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 7229
Tom Lane <tg*@sss.pgh.pa .us> said:
---------------------------(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


Re: tip 9 :-). In 7.3.5 is it possible that the planner is finding a type
mismatch when there isn't one, resulting in a seq scan being selected? I've
had a problem with joining tables on an int8 column (primary key column in one
of the tables). Tested left outer and where clause syntax.

TIA,

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

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

Nov 22 '05 #11
"Jim Wilson" <ji**@kelcomain e.com> writes:
Re: tip 9 :-). In 7.3.5 is it possible that the planner is finding a type
mismatch when there isn't one, resulting in a seq scan being selected?


When you gave no details, how is anyone to know? For questions like
this, you need to post the table definitions, the exact query, and the
results of EXPLAIN ANALYZE on that query. BTW, pgsql-performance is a
more appropriate list than -general for such questions.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #12
> 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.


I remember reading about how some the original commerical code from
Interbase came with some bugs that corrupted databases and that those bugs
were eventually fixed in Firebird. Don't know if that's related to this but
it could be.

Anyway since postgres uses WAL files to verify the integrity of the database
couldn't it more or less make the same guarantee's in an embedded version?
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 of
db errors resulting in database corruption?

In addition there are times when you want the ease of a relational database
but you don't need say the same level of reliability that you would on your
server. In these cases it would be nice to just have something that is easy
to drop into your app and have it just work. That being said I think that
in these cases the developer isn't usually going to care if it runs in
process or not. Maybe a good solution would be to make a postgres library
that could be included in process that would interface with the application.
All it would do is manager spawning the real postgres process. Then you
would have the reliability of a real server but the ease of use of an
embedded database. Couldn't that end up being pretty transparent to the
developer? If it was made to be easy postgres could become very popular as
an "embedded" db solution but still not have it's reputation diluted by
rampant database corruption from the application.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #13
"Rick Gigger" <ri**@alpinenet working.com> writes:
Anyway since postgres uses WAL files to verify the integrity of the database
couldn't it more or less make the same guarantee's in an embedded version?
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 of
db errors resulting in database corruption?


If an app's internal data structures get corrupted and it goes nuts,
pretty much anything can happen to its address space.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #14
> Anyway since postgres uses WAL files to verify the integrity of the database
couldn't it more or less make the same guarantee's in an embedded version?
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 of
db errors resulting in database corruption?


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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #15
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" <jo*****@eskimo .com>
To: "Rick Gigger" <ri**@alpinenet working.com>
Cc: "Jeff Bowden" <jl*@houseofdis traction.com>; "Tom Lane"
<tg*@sss.pgh.pa .us>; <pg***********@ postgresql.org>
Sent: Wednesday, January 14, 2004 2:07 PM
Subject: Re: [GENERAL] serverless postgresql

Anyway since postgres uses WAL files to verify the integrity of the database couldn't it more or less make the same guarantee's in an embedded version? 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 of db errors resulting in database corruption?


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

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

Nov 22 '05 #16
Jeff Bowden wrote:
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?


[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

Nov 22 '05 #17
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?

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

http://archives.postgresql.org

Nov 22 '05 #18
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?

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

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

Nov 22 '05 #19
> 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?


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

Nov 22 '05 #20

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
9657
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
10822
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...
0
9359
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...
1
7774
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6975
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
5642
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4443
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3103
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.