Thread: FAQ on Embedding Postgres

FAQ on Embedding Postgres

From
David Fetter
Date:
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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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

Re: FAQ on Embedding Postgres

From
Bruce Momjian
Date:
David Fetter wrote:
> 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?

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

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

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

Re: FAQ on Embedding Postgres

From
Richard Huxton
Date:
Bruce Momjian wrote:
> David Fetter wrote:
>> If you really need to embed an
>> SQL engine in a binary, consider the excellent
>> <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

Re: FAQ on Embedding Postgres

From
Bruce Momjian
Date:
Richard Huxton wrote:
> Bruce Momjian wrote:
> > David Fetter wrote:
> >> If you really need to embed an
> >> SQL engine in a binary, consider the excellent
> >> <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.

sqlite seems much more appropriate for embedding.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

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

Re: FAQ on Embedding Postgres

From
Richard Huxton
Date:
Bruce Momjian wrote:
> Richard Huxton wrote:
>> Bruce Momjian wrote:
>>> David Fetter wrote:
>>>> If you really need to embed an
>>>> SQL engine in a binary, consider the excellent
>>>> <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.
>
> 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

Re: FAQ on Embedding Postgres

From
Bruce Momjian
Date:
Richard Huxton wrote:
> Bruce Momjian wrote:
> > Richard Huxton wrote:
> >> Bruce Momjian wrote:
> >>> David Fetter wrote:
> >>>> If you really need to embed an
> >>>> SQL engine in a binary, consider the excellent
> >>>> <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.
> >
> > 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 :-)

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.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

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

Re: FAQ on Embedding Postgres

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> Richard Huxton wrote:
>> Bruce Momjian wrote:
> David Fetter wrote:
>>> If you really need to embed an
>>> SQL engine in a binary, consider the excellent
>>> <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.

> 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

Re: FAQ on Embedding Postgres

From
David Fetter
Date:
On Wed, Mar 05, 2008 at 12:35:53PM -0500, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Richard Huxton wrote:
> >> Bruce Momjian wrote:
> > David Fetter wrote:
> >>> If you really need to embed an
> >>> SQL engine in a binary, consider the excellent
> >>> <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.
>
> > 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.

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.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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

Re: FAQ on Embedding Postgres

From
"Joshua D. Drake"
Date:
On Wed, 5 Mar 2008 09:41:26 -0800
David Fetter <david@fetter.org> wrote:

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

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


Attachment

Re: FAQ on Embedding Postgres

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> On Wed, Mar 05, 2008 at 12:35:53PM -0500, Tom Lane wrote:
>> 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.

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

Re: FAQ on Embedding Postgres

From
David Fetter
Date:
On Wed, Mar 05, 2008 at 12:56:01PM -0500, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Wed, Mar 05, 2008 at 12:35:53PM -0500, Tom Lane wrote:
> >> 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.
>
> 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.

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.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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

Re: FAQ on Embedding Postgres

From
"Joshua D. Drake"
Date:
On Wed, 5 Mar 2008 10:25:00 -0800
David Fetter <david@fetter.org> wrote:

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

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

Sincerely,

Joshua D. Drake

>
> Cheers,
> David.


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


Attachment

Re: FAQ on Embedding Postgres

From
"Joshua D. Drake"
Date:
On Wed, 5 Mar 2008 10:30:43 -0800
"Joshua D. Drake" <jd@commandprompt.com> 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 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


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


Attachment

Re: FAQ on Embedding Postgres

From
Alvaro Herrera
Date:
Joshua D. Drake wrote:
> On Wed, 5 Mar 2008 10:30:43 -0800
> "Joshua D. Drake" <jd@commandprompt.com> 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 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.

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

Re: FAQ on Embedding Postgres

From
"Joshua D. Drake"
Date:
On Wed, 5 Mar 2008 16:17:17 -0300
Alvaro Herrera <alvherre@commandprompt.com> 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.
>
> I would suggest something less unfriendly, like "there are other open
> source databases that that are better suited for embedded usage."
>

No argument.

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


Attachment

Re: FAQ on Embedding Postgres

From
David Fetter
Date:
On Wed, Mar 05, 2008 at 10:53:07AM -0800, Joshua D. Drake wrote:
> On Wed, 5 Mar 2008 10:30:43 -0800
> "Joshua D. Drake" <jd@commandprompt.com> 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.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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

Re: FAQ on Embedding Postgres

From
"Joshua D. Drake"
Date:
On Wed, 5 Mar 2008 11:40:24 -0800
David Fetter <david@fetter.org> wrote:

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

Well let's start the +/-

+1 on wording
-1 on mentioning other products.

:)

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


Attachment

Re: FAQ on Embedding Postgres

From
Greg Smith
Date:
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 gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: FAQ on Embedding Postgres

From
Tom Lane
Date:
Greg Smith <gsmith@gregsmith.com> writes:
> 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

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

Re: FAQ on Embedding Postgres

From
"Joshua D. Drake"
Date:
On Thu, 06 Mar 2008 01:25:35 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

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

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


Attachment

Re: FAQ on Embedding Postgres

From
Theo Kramer
Date:
On Thu, 2008-03-06 at 01:25 -0500, Tom Lane wrote:
> Greg Smith <gsmith@gregsmith.com> writes:
> > 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
>
> 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.

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


Re: FAQ on Embedding Postgres

From
"Gurjeet Singh"
Date:
On Thu, Mar 6, 2008 at 11:55 AM, Tom Lane <tgl@sss.pgh.pa.us> 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

Re: FAQ on Embedding Postgres

From
"Dave Page"
Date:
On Thu, Mar 6, 2008 at 7:14 AM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> On Thu, Mar 6, 2008 at 11:55 AM, Tom Lane <tgl@sss.pgh.pa.us> 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

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

Re: FAQ on Embedding Postgres

From
Bruce Momjian
Date:
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:
> 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 gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
> --
> Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
> To make changes to your subscription:
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-docs

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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