FAQ on Embedding Postgres

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/&quot;&gt;SQLite&lt;/a&gt; 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. +

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/&quot;&gt;SQLite&lt;/a&gt;

or <a href="http://http://www.firebirdsql.org/&quot;&gt;Firebird&lt;/a&gt;

for the purpose.

Just to be fair.

--
Richard Huxton
Archonet Ltd

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/&quot;&gt;SQLite&lt;/a&gt;

or <a href="http://http://www.firebirdsql.org/&quot;&gt;Firebird&lt;/a&gt;

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

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/&quot;&gt;SQLite&lt;/a&gt;

or <a href="http://http://www.firebirdsql.org/&quot;&gt;Firebird&lt;/a&gt;

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

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/&quot;&gt;SQLite&lt;/a&gt;

or <a href="http://http://www.firebirdsql.org/&quot;&gt;Firebird&lt;/a&gt;

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

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/&quot;&gt;SQLite&lt;/a&gt;

or <a href="http://http://www.firebirdsql.org/&quot;&gt;Firebird&lt;/a&gt;

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

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/&quot;&gt;SQLite&lt;/a&gt;

or <a href="http://http://www.firebirdsql.org/&quot;&gt;Firebird&lt;/a&gt;

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

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

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

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

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

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

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

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

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

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

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

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

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