Deep integration of PostgreSQL with Apache

On Tue, 2005-05-03 at 07:53, Robin Boerdijk wrote:

Hi,

Apologies if this has been discussed before, but I was wondering if
there have been any efforts in the past to provide a deep integration
of PostgreSQL with Apache. What I mean by deep integration is that the
PostgreSQL server logic runs inside the Apache server processes, rather
than separate processes. In particular, the postmaster server logic
would run inside the Apache master process and the postgres server
logic would run inside Apache child processes.

The main advantage of this approach would be that it avoids the
Apache/PostgreSQL context switch when executing SQL requests from the
web server. It looks like the Apache server and PostgreSQL server
architectures are quite similar to make this feasible. Any thoughts?

And a single crashed or corrupted apache thread or process can trash
your database.

Connection pooling is generally a better answer to this type of
problem. PostgreSQL just isn't designed to be embedded into other
people's code. SQLLite, on the other hand, might be a good option for
such a project.

Robin Boerdijk wrote:

Hi,

Apologies if this has been discussed before, but I was wondering if
there have been any efforts in the past to provide a deep integration
of PostgreSQL with Apache. What I mean by deep integration is that the
PostgreSQL server logic runs inside the Apache server processes, rather
than separate processes. In particular, the postmaster server logic
would run inside the Apache master process and the postgres server
logic would run inside Apache child processes.

The main advantage of this approach would be that it avoids the
Apache/PostgreSQL context switch when executing SQL requests from the
web server. It looks like the Apache server and PostgreSQL server
architectures are quite similar to make this feasible. Any thoughts?

The PostgreSQL backend is inherently single-threaded and a new process
is forked each time you establish a new connection (session) so the
integration you ask for is not in anyway possible unless you are content
with one single database connection.

Regards,
Thomas Hallgren

Thomas Hallgren wrote:

Robin Boerdijk wrote:

Hi,

Apologies if this has been discussed before, but I was wondering if
there have been any efforts in the past to provide a deep integration
of PostgreSQL with Apache. What I mean by deep integration is that the
PostgreSQL server logic runs inside the Apache server processes, rather
than separate processes. In particular, the postmaster server logic
would run inside the Apache master process and the postgres server
logic would run inside Apache child processes.

I think the closest you are going to get is Apache::DBI however sense
PostgreSQL is processed based you are going to get a new connection
for every connection to Apache.

On any reasonably busy site that can spell doom.

Sincerely,

Joshua D. Drake

--- Thomas Hallgren <thhal@mailblocks.com> wrote:

Robin Boerdijk wrote:

Hi,

Apologies if this has been discussed before, but I was wondering if
there have been any efforts in the past to provide a deep

integration

of PostgreSQL with Apache. What I mean by deep integration is that

the

PostgreSQL server logic runs inside the Apache server processes,

rather

than separate processes. In particular, the postmaster server logic
would run inside the Apache master process and the postgres server
logic would run inside Apache child processes.

The main advantage of this approach would be that it avoids the
Apache/PostgreSQL context switch when executing SQL requests from

the

web server. It looks like the Apache server and PostgreSQL server
architectures are quite similar to make this feasible. Any

thoughts?

The PostgreSQL backend is inherently single-threaded and a new
process
is forked each time you establish a new connection (session) so the
integration you ask for is not in anyway possible unless you are
content
with one single database connection.

I agree that it is not trivial, but is it feasible? Specifically, I'm
thinking about the following approach:

1. Strip all networking logic and the logic that manages the postgres
child servers from the postmaster server. The logic that remains is
code that manages the auxiliary processes such as the bgwriter and
statistics collector. Integrate this remaining logic in the the Apache
master server.

2. Strip all networking logic from the postgres server. The logic that
remains is logic for executing queries against the database. Integrate
this remaining logic in the Apache child server.

The result of this is an integrated web/database server where all
networking is handled by Apache instead of postmaster/postgres. Other
than that, I see no difference with the way PostgreSQL works out of the
box. The Apache master server functions as the postmaster (i.e.
managing the child server processes) and the Apache child servers
function as the postgres servers (i.e. access the database). Why would
this web/database server be limited to using only one connection?

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Apologies if this has been discussed before, but I was wondering

if

there have been any efforts in the past to provide a deep

integration

of PostgreSQL with Apache. What I mean by deep integration is that

the

PostgreSQL server logic runs inside the Apache server processes,

rather

than separate processes. In particular, the postmaster server

logic

would run inside the Apache master process and the postgres server
logic would run inside Apache child processes.

I think the closest you are going to get is Apache::DBI however sense
PostgreSQL is processed based you are going to get a new connection
for every connection to Apache.

Yes, I didn't think about that one. Those Apache child processes serve
only a single connection at a time. I guess a single postgres server
process must be quite heavywait then compared to an Apache child
process.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Wouldn't it just be cheaper to buy a second machine than do all of that?

Alex Turner
netEconomist

I agree that it is not trivial, but is it feasible? Specifically, I'm
thinking about the following approach:

Everything is feasible but that doesn't mean it is sane to do so :). If
you were going to do that you would probably be better served looking at
something like sqllite.

Sincerely,

Joshua D. Drake
Command Prompt, Inc.

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Robin Boerdijk wrote:

Why would
this web/database server be limited to using only one connection?

No it wouldn't. I misunderstood your question. It's one process/one
connection. If you don't use Apache as a multi-threaded server, then
your question makes more sense. Still, it's a lot of work to make it
happen and I'm not sure you'd gain anything. My advice would be to
consider a multi-threaded server that uses a connection pool and perhaps
include some shared caching of data that is more static in nature in the
middle tier. That could really save some context switches.

Regards,
Thomas Hallgren

I asked this question several weeks ago, but nobody proposed a solution, so
I am repeating the same question again...
I have an MS Access front-end for a database on PostgreSQL.
I could use pass-through queries as record sources for reports and it works
fine...
Unfortunately, MS Access doesn't allow pass-through queries to be records
sources for subforms.
Therefore I tried to base subforms on regular JET queries on linked tables.
It was too slow...
Then I tried to base subforms on DAO recordset code generated from
pass-through QueryDef objects. Although it worked, it was very unstable...

Now it seems to me that POstgreSQL views are the best solution, but Access
considers views as tables (!) and needs column with unique values.
All those views are complicated queries on several tables, so I can't use
any table's column as primary key. I need a calculated column in the view
that Access will consider as primary key column.
In regular tables, I use bigserial field, but how can I create calculated
bigserial column in a view ?

Thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I think the closest you are going to get is Apache::DBI however sense
PostgreSQL is processed based you are going to get a new connection
for every connection to Apache.

On any reasonably busy site that can spell doom.

Actually, this is a classic argument for using mod_perl. Each Apache child
can connect once to the database, and stay connected as it serves requests
to different clients. There is very little overhead, and if you take advantage
of server-side prepares, things can run even faster.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200505020927
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCdiqzvJuQZxSWSsgRAvmcAKC8HX5E/QwzhHalM0PNg/rMmufFhgCfZFlA
tt64Bin9lq7TFSSk/hCsYFg=
=Nmnq
-----END PGP SIGNATURE-----

Zlatko Matic wrote:

I asked this question several weeks ago, but nobody proposed a solution,
so I am repeating the same question again...
I have an MS Access front-end for a database on PostgreSQL.
I could use pass-through queries as record sources for reports and it
works fine...
Unfortunately, MS Access doesn't allow pass-through queries to be
records sources for subforms.
Therefore I tried to base subforms on regular JET queries on linked
tables. It was too slow...
Then I tried to base subforms on DAO recordset code generated from
pass-through QueryDef objects. Although it worked, it was very unstable...

Now it seems to me that POstgreSQL views are the best solution, but
Access considers views as tables (!) and needs column with unique values.
All those views are complicated queries on several tables, so I can't
use any table's column as primary key. I need a calculated column in the
view that Access will consider as primary key column.
In regular tables, I use bigserial field, but how can I create
calculated bigserial column in a view ?

Thanks.

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

Another option is to toss the MS Access altogether and program the front
end entirely in VB. That is what we did.

--
Kind Regards,
Keith

"Zlatko Matic" <zlatko.matic1@sb.t-com.hr> writes:

In regular tables, I use bigserial field, but how can I create calculated
bigserial column in a view ?

You would have to create a sequence and reference it with
nextval('sequencename') in your view. But I doubt very much that it will do
anything useful.

It sounds like Access wants to be able to update records by looking them up by
primary key. In that case assigning a new value in your view will make
Postgres make up a brand new number that is utterly useless for finding the
record again later.

--
greg

I will try...if it will be useless, I will quite. Then the only solution
will be make-table query based on nested pass-through query, so I will be
working on local JET tables that will be refreshed from server on each
session. But I would like to avoid local tables, if possible...

How do I create sequence ?

----- Original Message -----
From: "Greg Stark" <gsstark@mit.edu>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>
Cc: <pgsql-general@postgresql.org>; <pgsql-interfaces@postgresql.org>
Sent: Wednesday, May 04, 2005 7:11 AM
Subject: Re: [GENERAL] [INTERFACES] calculated identity field in views,
again...

You mean VB.NET ?

----- Original Message -----
From: "Keith Worthington" <KeithW@NarrowPathInc.com>
To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr>
Cc: <pgsql-general@postgresql.org>; <pgsql-interfaces@postgresql.org>
Sent: Wednesday, May 04, 2005 5:58 AM
Subject: Re: [INTERFACES] calculated identity field in views, again...

Zlatko Matic wrote:

I asked this question several weeks ago, but nobody proposed a
solution, so I am repeating the same question again...
I have an MS Access front-end for a database on PostgreSQL.
I could use pass-through queries as record sources for reports and it
works fine...
Unfortunately, MS Access doesn't allow pass-through queries to be
records sources for subforms.
Therefore I tried to base subforms on regular JET queries on linked
tables. It was too slow...
Then I tried to base subforms on DAO recordset code generated from
pass-through QueryDef objects. Although it worked, it was very
unstable...

Now it seems to me that POstgreSQL views are the best solution, but
Access considers views as tables (!) and needs column with unique
values.
All those views are complicated queries on several tables, so I can't
use any table's column as primary key. I need a calculated column in
the view that Access will consider as primary key column.
In regular tables, I use bigserial field, but how can I create
calculated bigserial column in a view ?

Thanks.

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

Another option is to toss the MS Access altogether and program the
front end entirely in VB. That is what we did.

--
Kind Regards,
Keith

Zlatko Matic wrote:
You mean VB.NET ?

Actually we wrote our interface using VB6.

--
Kind Regards,
Keith

On Wed, May 04, 2005 at 10:56:25 +0200,
Zlatko Matic <zlatko.matic1@sb.t-com.hr> wrote:

I will try...if it will be useless, I will quite. Then the only solution
will be make-table query based on nested pass-through query, so I will be
working on local JET tables that will be refreshed from server on each
session. But I would like to avoid local tables, if possible...

Didn'y you ask this same question a week or two ago? I am pretty sure
someone told you how to make access work with a view.

How do I create sequence ?

You can't create a sequence for a view. You would need to have it in a
table that is joined as part of the view.

Bruno Wolff III <bruno@wolff.to> writes:

How do I create sequence ?

You can't create a sequence for a view. You would need to have it in a
table that is joined as part of the view.

Sure you can. You can have any expression you want, including nextval('seq')
in your view. The problem is that every time you query it it will provide
different numbers. I can't see that being useful.

--
greg

--- Zlatko Matic <zlatko.matic1@sb.t-com.hr> wrote:

I asked this question several weeks ago, but nobody
proposed a solution, so
I am repeating the same question again...
I have an MS Access front-end for a database on
PostgreSQL.
I could use pass-through queries as record sources
for reports and it works
fine...
Unfortunately, MS Access doesn't allow pass-through
queries to be records
sources for subforms.

Unless you use unbound form/controls. Which means
handling everything in code, which might work out best
for you, depending on what you want (this is
effectively equivalent to the VB-only option which
someone else mentioned).

Therefore I tried to base subforms on regular JET
queries on linked tables.
It was too slow...
Then I tried to base subforms on DAO recordset code
generated from
pass-through QueryDef objects. Although it worked,
it was very unstable...

Now it seems to me that POstgreSQL views are the
best solution, but Access
considers views as tables (!) and needs column with
unique values.

AFAIK a composite key (combination of several columns)
should work ok for a primary key for Access. When
linking to the view, just select the columns you want
to use. Or are you saying that you tried this, and it
didn't work?

Alternatively, you could try including in your view
definition the oid column for each of the constituent
tables. If I understand right, oids are globally
unique within your database. This assumes that you
have created your tables with oids, which may not be
the case.

Basing a subform on a mult-table join sounds like odd
database design. Perhaps if you can explain more
about what you are trying to do, people can offer more
suggestions.

All those views are complicated queries on several
tables, so I can't use
any table's column as primary key. I need a
calculated column in the view
that Access will consider as primary key column.
In regular tables, I use bigserial field, but how
can I create calculated
bigserial column in a view ?

Thanks.

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

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Wed, May 04, 2005 at 11:47:12 -0400,
Greg Stark <gsstark@mit.edu> wrote:

Bruno Wolff III <bruno@wolff.to> writes:

How do I create sequence ?

You can't create a sequence for a view. You would need to have it in a
table that is joined as part of the view.

Sure you can. You can have any expression you want, including nextval('seq')
in your view. The problem is that every time you query it it will provide
different numbers. I can't see that being useful.

That is more or less what I meant. You can't usefully tie a sequence directly
to a view, the way you can tie one to a table.