Thread: Deep integration of PostgreSQL with Apache
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? Best regards, Robin Boerdijk __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
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 On 5/3/05, Robin Boerdijk <robin_boerdijk@yahoo.com> wrote: > --- 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
> > 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...
> "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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
>> 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
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.