Subject: FAQ on Embedding Postgres

From: David Fetter <[email-masked]>
Date: Wed, 5 Mar 2008 06:37:12 -0800

Folks,

I just got yet another question about embedding Postgres in a binary.
What should be in the FAQ on this subject?  Here's what I have so far:

Q:  How can I embed PostgreSQL in a binary?

A:  You can't.  PostgreSQL is designed from the ground up to run as a
separate set of processes on a server.  If you really need to embed an
SQL engine in a binary, consider the excellent
<a href="http://www.sqlite.org/">SQLite</a> for the purpose.

Comments?  Criticisms?  Rotten tomatoes?

Cheers,
David.
-- 
David Fetter <[email-masked]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: [email-masked]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 5 Mar 2008 09:41:21 -0500 (EST)

> Comments?  Criticisms?  Rotten tomatoes?

Yea, good idea.  Let me add it.  Thanks.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---

From: Richard Huxton <[email-masked]>
Date: Wed, 05 Mar 2008 14:58:30 +0000

Bruce Momjian wrote:
> David Fetter wrote:
>> If you really need to embed an
>> <a href="http://www.sqlite.org/">SQLite</a>

or <a href="http://http://www.firebirdsql.org/">Firebird</a>

>> for the purpose.

Just to be fair.

-- 
   Richard Huxton
   Archonet Ltd

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 5 Mar 2008 10:02:41 -0500 (EST)

> or <a href="http://http://www.firebirdsql.org/">Firebird</a>

sqlite seems much more appropriate for embedding.

---

From: Richard Huxton <[email-masked]>
Date: Wed, 05 Mar 2008 15:05:30 +0000

> sqlite seems much more appropriate for embedding.

It's certainly the lightest DB I'm aware of, but Firebird does have an 
embedded mode (apparentl - I tend to use a different DB :-)

-- 
   Richard Huxton
   Archonet Ltd

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 5 Mar 2008 10:09:38 -0500 (EST)

> embedded mode (apparentl - I tend to use a different DB :-)

Right.  The issue with Firebird is it does both embedded and
traditional, while sqlite is only embedded, so is probably a better
choice for that purpose.

---

From: Tom Lane <[email-masked]>
Date: Wed, 05 Mar 2008 12:35:53 -0500

> sqlite seems much more appropriate for embedding.

Is it really a good idea to be recommending particular other products?
And don't forget that the next thing on that list would be mysql.

If we need a FAQ entry on this at all, I'd stop after David's first two
sentences.

			regards, tom lane

---

From: David Fetter <[email-masked]>
Date: Wed, 5 Mar 2008 09:41:26 -0800

On Wed, Mar 05, 2008 at 12:35:53PM -0500, Tom Lane wrote:
> Is it really a good idea to be recommending particular other
> products?  And don't forget that the next thing on that list would
> be mysql.
> 
> If we need a FAQ entry on this at all, I'd stop after David's first
> two sentences.

Stopping there seems like a very bad idea from a public relations
point of view.  Making the suggestion generic and naming several FLOSS
DBs as an alternative might work better.

Cheers,
David.

Remember to vote!

---

From: "Joshua D. Drake" <[email-masked]>
Date: Wed, 5 Mar 2008 09:48:30 -0800

On Wed, 5 Mar 2008 09:41:26 -0800

> DBs as an alternative might work better.

IMO, entries last forever, for years and years even after we fix them.
I believe we should make reference that PostgreSQL is not generally
considered an embeddable platform and call it good.

Sincerely,

Joshua D. Drake

-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director |  PostgreSQL political pundit

---

From: Tom Lane <[email-masked]>
Date: Wed, 05 Mar 2008 12:56:01 -0500

> point of view.

Pointing to an alternative product doesn't make that any better.
What would make it better is to explain *why* we design PG this way.

			regards, tom lane

---

From: David Fetter <[email-masked]>
Date: Wed, 5 Mar 2008 10:25:00 -0800

On Wed, Mar 05, 2008 at 12:56:01PM -0500, Tom Lane wrote:
> >> If we need a FAQ entry on this at all, I'd stop after David's
> >> first two sentences.
> 

It's good to explain why PG does what it does how it does it, and that
should be in that section of the FAQ, but continuing, even by silence,
with an answer equivalent to, "We don't do that.  You shouldn't
either.  Tough $#!+" only makes us look bad, where suggesting
alternatives for the use cases we don't cover makes us look good.

Cheers,
David.

Remember to vote!

---

From: "Joshua D. Drake" <[email-masked]>
Date: Wed, 5 Mar 2008 10:30:43 -0800

On Wed, 5 Mar 2008 10:25:00 -0800

> alternatives for the use cases we don't cover makes us look good.

I believe there is wording that could be used that would not convey
that sentiment.

Sincerely,

Joshua D. Drake

---

From: "Joshua D. Drake" <[email-masked]>
Date: Wed, 5 Mar 2008 10:53:07 -0800

On Wed, 5 Mar 2008 10:30:43 -0800
 
> I believe there is wording that could be used that would not convey

Such as:

PostgreSQL is designed as a client / server architecture and does not
normally embed in an optimal way. Discussion of other product solutions
to the embedded problem is outside the scope of this document.

Sincerely,

Joshua D. Drake

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 5 Mar 2008 16:17:17 -0300

> to the embedded problem is outside the scope of this document.

I would suggest something less unfriendly, like "there are other open
source databases that that are better suited for embedded usage."

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---

From: "Joshua D. Drake" <[email-masked]>
Date: Wed, 5 Mar 2008 11:32:08 -0800

On Wed, 5 Mar 2008 16:17:17 -0300
Alvaro Herrera <[email-masked]> wrote:

> > PostgreSQL is designed as a client / server architecture and does
> > not normally embed in an optimal way. Discussion of other product
> > solutions to the embedded problem is outside the scope of this
> > document.
> 

No argument.

Joshua D. Drake

---

From: David Fetter <[email-masked]>
Date: Wed, 5 Mar 2008 11:40:24 -0800

On Wed, Mar 05, 2008 at 10:53:07AM -0800, Joshua D. Drake wrote:
> > I believe there is wording that could be used that would not
> > convey that sentiment.
> 
> Such as:
> 
> PostgreSQL is designed as a client / server architecture

Good so far.

> and does not normally embed in an optimal way.

While could be twisted around to be factually true--it's possible to
ship a VM as part of a software package, for example--it's at best
misleading.  Let's just cut the weasel words out.

> Discussion of other product solutions to the embedded problem is
> outside the scope of this document.

As Alvaro said, it should be possible to put this in a positive light.

I still think it is good to name a few places to start the research on
embedded SQL DBMSs rather than leave people with the feeling of, "It's
all up to you from here.  Go away."

Cheers,
David.

Remember to vote!

---

From: "Joshua D. Drake" <[email-masked]>
Date: Wed, 5 Mar 2008 11:44:47 -0800

On Wed, 5 Mar 2008 11:40:24 -0800
 
> all up to you from here.  Go away."

Well let's start the +/-

+1 on wording
-1 on mentioning other products.

:)

Joshua D. Drake

---

From: Greg Smith <[email-masked]>
Date: Wed, 5 Mar 2008 19:16:26 -0500 (EST)

I think it's funny to consider a specific recommendation for SQLite as 
being out of line when you look at the history here.  The whole reason 
that software even exists is because of the difficulty of using PostgreSQL 
in this context.  See http://www.linuxjournal.com/article/6650

With that in mind, after gobbling up a good mix of earlier suggestions 
here and editing a bit I think it's completely reasonable to write this:

"Q:  How can I embed PostgreSQL inside another program?

A:  PostgreSQL is designed with a client/server architecture that makes it 
difficult to embed this way.  There are other open source databases you 
might consider that are better suited for embedded usage, such as SQLite 
which was designed for that purpose."

Now there's no obligation to mention Firebird/MySQL/etc. because that's 
not in fact what they were originally designed for (there's not enough 
angry Derby users to worry about them), and this satisfies the idea I 
support that you give people some idea where they might look instead.

--
* Greg Smith [email-masked] http://www.gregsmith.com Baltimore, MD

---

From: Tom Lane <[email-masked]>
Date: Thu, 06 Mar 2008 01:25:35 -0500

> in this context.  See http://www.linuxjournal.com/article/6650

I've got nothing against SQLite.  But I am unhappy with the idea of us
recommending *any* particular bit of software that is not under our
control, especially in a document that is as widespread and hard to
update as our FAQ.  There are any number of scenarios where we might
want to take back such an endorsement, but once made it'll be out there
somewhere on the Web until cockroaches rule the earth.  There is also
the whole class of arguments about "why'd you recommend X and not Y?"
that we'd surely get sucked into.  Better not to go there in the first
place.

			regards, tom lane

---

From: "Joshua D. Drake" <[email-masked]>
Date: Wed, 5 Mar 2008 22:53:34 -0800

On Thu, 06 Mar 2008 01:25:35 -0500
Tom Lane <[email-masked]> wrote:

> want to take back such an endorsement, but once made it'll be out
> there somewhere on the Web until cockroaches rule the earth.  There
> is also the whole class of arguments about "why'd you recommend X and
> not Y?" that we'd surely get sucked into.  Better not to go there in
> the first place.

I have to agree. As much as I actually like SQLite the reality is the
only thing we can remotely guarantee is the quality of our own software
and as Tom says, "cockroaches". I still find articles that I wrote 5
years ago that are not even remotely relevant any longer but I get
emails about them.

We need to focus the language on and about postgresql and postgresql
only.

Sincerely,

Joshua D. Drake

---

From: Theo Kramer <[email-masked]>
Date: Thu, 06 Mar 2008 08:54:23 +0200

>From a users point of view - both PostgreSQL and other embedable
databases aka file handlers

I have to agree - suggesting an alternate product might belong in a book
but not in a FAQ which is relevant to PostgreSQL and an integral part of
the PostgreSQL documentation.
-- 
Regards
Theo

---

From: "Gurjeet Singh" <[email-masked]>
Date: Thu, 6 Mar 2008 12:44:34 +0530

On Thu, Mar 6, 2008 at 11:55 AM, Tom Lane <[email-masked]> wrote:

> but once made it'll be out there
> somewhere on the Web until cockroaches rule the earth.

Hate to deviate away from the topic, but couldn't resist.... sorry.

I had watched this episode of Mythbusters, and they have busted the myth
that cockroaches will be the last/only survivors of a nuclear holocaust.
Here's a link to a portion of that episode. I love the show BTW.

http://www.youtube.com/watch?v=S-6cIy_s8pQ&feature=related

Best regards,
-- 
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

17° 29' 34.37"N,  78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N,  73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device

---

From: "Dave Page" <[email-masked]>
Date: Thu, 6 Mar 2008 08:54:23 +0000

> http://www.youtube.com/watch?v=S-6cIy_s8pQ&feature=related

Now that just sounds plain weird without the voiceover guy that does
the Discovery UK edits!

-- 
Dave Page
EnterpriseDB UK Ltd: http://www.enterprisedb.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 24 Apr 2008 15:58:14 -0400 (EDT)

Added to FAQ as part of item 1.13:

    <H3 id="item1.13">1.13) How does PostgreSQL compare to other
    <SMALL>DBMS</SMALL>s?  Can PostgreSQL be embedded?</H3>

    ...

    <P>PostgreSQL is designed as a client/server architecture, which
    requires separate processes for each client and server, and various
    helper processes.  Many embedded architectures can support such
    requirements.  However, if your embedded architecture requires the
    database server to run  inside the application process, Postgres cannot
    be used and a lighter-weight database solution should be selected.</P>

---------------------------------------------------------------------------

Greg Smith wrote:
> Sent via pgsql-docs mailing list ([email-masked])
> To make changes to your subscription:
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-docs

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com