Using Postgresql as application server

On Aug 13, 2011, at 11:57, c k <shreeseva.learning@gmail.com> wrote:

Dear Postgres users,
from last few months I am reading and searching for can postgresql used as application server? As postgresql supports many languages like pl/perl, pl/python etc, supports dblink like functions to connect to other postgresql servers and now features are in development to use external data. Postgresql works well on many operating systems and has a stable and good quality code. As many users are using plpython or plperl to work on many types of data and to implement logic that can be useful in web application management.
So i am thinking if I can use postgresql as web application server.
Few points that supports this opinion:
1. multiple languages support for stored procs and triggers.
2. can connect to other postgresql servers or databases
3. it is easy to manage stored procs or triggers than managing web application in other servers.
4. data and logic/processing can be separated. One postgresql can be used as application server and another as database.
5. stable, mature and open codebase.

I request to users and developers give your suggestions and opinions.
Waiting for your replies.

Thanks and regards,

Chaitanya Kulkarni

Code yourself a nice "hello world" application that can accessed by a web browser and outputs in HTML using only PostgreSQL. IF you can actually do that simple task you will then be in a better position to decide if such an architecture is worth expanding upon.

The better question to ask is why wouldn't you want to use something like Tomcat or Apache+Programming Language?

David J.

c k wrote:

from last few months I am reading and searching for can postgresql
used as application server?
So i am thinking if I can use postgresql as web application server.

I request to users and developers give your suggestions and opinions.
Waiting for your replies.

Yes! Yes And Yes, my friend!
You should!

i am using postgresql exactly as application server.
(stored procedures in PLpgsql)
at http://platzcart.com i managed to minimize server side scripting:
Stored procedures run entire "business logic" + PHP envelop results into
HTML code.

and in the next project (yet under construction)
i managed to ELIMINATE server side scripting at all
(except captcha server, which is standalone (written in perl))
PLpgsql "business logic" again + client side JS is building presentation.

most important point to support you is
*This is the originally intended usage of a DBMS*

All the troubles, all the NoSQL whining caused by unnecessary middleware.

P.S.
i have held a report to NLUUG Spring Conference 2011
about platzcart.com implementation
it is located at http://platzcart.com/lib/platzcart.pdf
it clearly describes advantages of using postgresql as an application
server.
and i am sure it explains the good practice (maybe the best one)

P.P.S.
now i am using nginx_http_postgres_module

Yes, I know that I can not create a simple web application using only
postgresql because we need a web server to server the html content. I not
going to add web server functionalities to postgresql, but just want to use
existing features. I would like to generate content dynamically. I want
minimum developers to be required, simple and powerful security and
administration, and most importantly ability to respond to changes.
As sad@bestmx.ru replied above, I want to go through this way at least to
test the application. What I am going to do is
use python for a simple test web server.
Use xml to define static content for each page.
Use posgtresql to generate dynamic content using stored procs.
static content of the page and dynamic content both will make final web
page.
Use javascript for client UI.

For my application the most important part is generating dynamic content.
Scaling, concurrency etc are not the issues at this time. We have to adopt
govt. rules and change the business logic accordingly and it must be fast
enough to save penalties.

Regards,

Chaitanya Kulkarni

On Sat, Aug 13, 2011 at 10:54 PM, sad@bestmx.ru <sad@bestmx.ru> wrote:

c k wrote:

Yes, I know that I can not create a simple web application using only
postgresql because we need a web server to server the html content.

u r wrong.
u CAN!

there is nginx_htttp_postgresql_module
exactly to connect webserver directly to postgresql
and to OUTPUT query result to a browser.

can u please give me it's link.
I found ngx_postgres module.

Chaitanya Kulkarni

On Sun, Aug 14, 2011 at 12:14 AM, sad@bestmx.ru <sad@bestmx.ru> wrote:

can u please give me it's link.
I found ngx_postgres module.

there is nginx_htttp_postgresql_module
exactly to connect webserver directly to postgresql
and to OUTPUT query result to a browser.

http://wiki.nginx.org/3rdPartyModules
http://labs.frickle.com/nginx_ngx_postgres/

On Sun, 14 Aug 2011, c k wrote:

I would like to generate content dynamically. I want minimum developers to
be required, simple and powerful security and administration, and most
importantly ability to respond to changes.

For my application the most important part is generating dynamic content.

Chaitanya,

There are three major components of a database application: the dbms back
end (postgres in this case), middleware (in the language of your choice),
and the UI. The UI will use apache to display pages and accept user input.
The middleware contains business logic and is the interface between the user
and the back end.

Each (scripting) language has application development frameworks. My
partner (a highly experienced software engineer) is using Ruby on Rails for
one of our projects. It is powerful but has a long learning curve and you
need to do everything the Rails way. In python (my scripting language of
choice) there's django, turbogears, pylon, and probably many more.

Depending on the approach you select you may be able to meet all your
desired attributes as above. What you might consider doing is list those
requirements in order of importance and focus on meeting the most important
ones. This may mean droping other preferences (e.g., minumum number of
developers unless you have a long lead time for this project).

Rich

On 08/13/2011 05:57 PM, c k wrote:

Dear Postgres users,
from last few months I am reading and searching for can postgresql used

as application server? As postgresql supports many languages like
pl/perl, pl/python etc, supports dblink like functions to connect to
other postgresql servers and now features are in development to use
external data. Postgresql works well on many operating systems and has a
stable and good quality code. As many users are using plpython or plperl
to work on many types of data and to implement logic that can be useful
in web application management.

So i am thinking if I can use postgresql as web application server.
Few points that supports this opinion:
1. multiple languages support for stored procs and triggers.
2. can connect to other postgresql servers or databases
3. it is easy to manage stored procs or triggers than managing web

application in other servers.

4. data and logic/processing can be separated. One postgresql can be

used as application server and another as database.

5. stable, mature and open codebase.

I request to users and developers give your suggestions and opinions.
Waiting for your replies.

Thanks and regards,

Chaitanya Kulkarni

No, PG has never, and will never, act as an application-server. Are you
really sure you need a full-blown application-server?

If you're familiar with Scala (a language which runs on the JVM) I
really recommend Lift (www.liftweb.net) with PostgreSQL. Definitely the
best. Although Lift has a history of having a steep learning-curve I
always favour maintainability, type-safety and robustness over RAD and
time-to-market. Having said that I honestly think a skilled Lift team is
at least 2x more productive than with any other web-based framework for
building modern UI.

I'm about to post a Lift+JPA+Spring+PostgreSQL example soon which
demonstrates how to use Lift with Spring+JPA+XA(2 phase commit, aka.
distributed transactions). Follow the mailing-list and you'll see it
there soon (I hope): http://groups.google.com/group/liftweb

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Troll�sen | somebody else doing it wrong, without |
NORWAY | comment. |
Org.nr: NO 981 479 076 | |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

Hi CK:

First, it depends on what you mean by an application server. There
are people who in fact do very similar things with PostgreSQL,
essentially having it take on roles traditionally served by
middleware.

On Sat, Aug 13, 2011 at 8:57 AM, c k <shreeseva.learning@gmail.com> wrote:

Dear Postgres users,
from last few months I am reading and searching for can postgresql used as
application server?

Kinda.

As postgresql supports many languages like pl/perl,
pl/python etc, supports dblink like functions to connect to other postgresql
servers and now features are in development to use external data. Postgresql
works well on many operating systems and has a stable and good quality code.
As many users are using plpython or plperl to work on many types of data and
to implement logic that can be useful in web application management.
So i am thinking if I can use postgresql as web application server.
Few points that supports this opinion:
1. multiple languages support for stored procs and triggers.

2. can connect to other postgresql servers or databases

Best used sparingly.

3. it is easy to manage stored procs or triggers than managing web
application in other servers.

I agree, but....

4. data and logic/processing can be separated. One postgresql can be used as
application server and another as database.

Bad idea. You don't really gain anything except complexity and
headache by trying to separate like this.

5. stable, mature and open codebase.

Probably better than give some general feedback I will share how we do
this with LedgerSMB. Some of our deployments using this approach are
decent-sized.

1: Thus far all our stored procs are all in SQL and PLPGSQL. We do
not currently use PL/Perl or PL/Python, or any other stored procedure
language.

2: We pay close attention to semantics in order to ensure, to the
extent possible, that catalog data allows for discovery of stored
procedure interfaces. We then do a lot of query building in the
"client" app (a web app) to discover these interfaces and call them
properly. We do not include SQL code in most perl modules. Instead
virtually all calls go through a generic discovery interface.

3: All logic required to store, retrieve, or present (to the
application) the data goes through these stored procedures.

4: The web app is a fairly thin Perl glue that binds HTML templates
written in Template Toolkit to these stored procedures. It's rare to
find Perl functions more than about 5-10 lines long and when that
happens most of the logic is usually taking data and putting it into a
tabular structure for a report template.

This could be done with a desktop app as well. The primary business
logic and security is thus reusable between applications, making
PostgreSQL essentially a middleware server.

Observations from my experience: Design is critical and difficult.
There aren't a lot of people doing this sort of thing and so there is
a LOT of trial and error. Also, it is quite possible to do a heck of
a lot in this area in SQL and PLPGSQL. Focus on writing unified,
maintainable queries and semantically meaningful interfaces (argument
names, function names, etc).

Best Wishes,
Chris Travers

On 14/08/11 05:12, David Johnston wrote:

On Aug 13, 2011, at 11:57, c k<shreeseva.learning@gmail.com> wrote:

Dear Postgres users,
from last few months I am reading and searching for can postgresql used as application server? As postgresql supports many languages like pl/perl, pl/python etc, supports dblink like functions to connect to other postgresql servers and now features are in development to use external data. Postgresql works well on many operating systems and has a stable and good quality code. As many users are using plpython or plperl to work on many types of data and to implement logic that can be useful in web application management.
So i am thinking if I can use postgresql as web application server.
Few points that supports this opinion:
1. multiple languages support for stored procs and triggers.
2. can connect to other postgresql servers or databases
3. it is easy to manage stored procs or triggers than managing web application in other servers.
4. data and logic/processing can be separated. One postgresql can be used as application server and another as database.
5. stable, mature and open codebase.

I request to users and developers give your suggestions and opinions.
Waiting for your replies.

Thanks and regards,

Chaitanya Kulkarni

Code yourself a nice "hello world" application that can accessed by a web browser and outputs in HTML using only PostgreSQL. IF you can actually do that simple task you will then be in a better position to decide if such an architecture is worth expanding upon.

The better question to ask is why wouldn't you want to use something like Tomcat or Apache+Programming Language?

David J.

Hi,

I see from other posts that you are using Python (A lovely language, but
one that does not scale well for my own purposes -- unfortunately) so
this is most likely not relevant to your current situation.

However, for those people who would consider Java, then consider using
JBoss 7, it is very fast and will support very sophisticated web front
ends while allowing very powerful logic written in Java, and can easily
be configured to use PostgreSQL.

The combination of JBoss http://www.jboss.org/overview.html and JEE
(Java Enterprise Edition), is well suited to large projects that can
benefit from multi-core processors and massive amounts of RAM. My own
humble development machine has a mere 4 cores and 8GB, but production
machines can easily use a terrabyte of memory and many more cores.
Eclipse is an IDE that is well suited for developing applications in
Java, and can be configured to develop applications for JBoss.

Cheers,
Gavin

I have written a script to install JBoss 7 (a lot easier than for JBoss
4 and earlier!) and convert it to use PostgreSQL 9.1 beta3. This I am
using to revise my JEE knowledge and get to grips with version 6 of JEE.

Dear Postgres users,

<snip>

How about sending these to just one mailing list -- when you cross post everybody gets two copies of each response.

Hi,

I've recently looked into the problem of my INSERTs throwing an ROW
error, when a new row hits an already present one, by unique constraint.
It triggers an expensive rollback, and I'd like to have it sort of
"optimised". In my case, duplicates can be discarded on an attempt
INSERT, but an UPDATE instead would also do.

When I was looking for a solution, I found this:
http://wiki.postgresql.org/wiki/SQL_MERGE

Which would do nicely, but I understand postgresql does not have it,
yet.

On the other hand, I think that providing the OLD.* table for RULES and
TRIGGERS on INSERT, for an application level programmer (like myself),
could provide a simple way to overcome the missing feature, until it's
fully implemented as MERGE statement according to SQL:2003.

Such OLD.* table on INSERTS should contain a row from current table
content, that matches unique constraints of a currently inserted row or
nothing, if there is no collision. This way I could make a conditional
RULE with "... WHERE exists(OLD.someting)..." instead of doing an
explicit SELECT in that WHERE clausure, which I think is more expensive
then referring a column already fetched by the engine.

Possibly, this may pave the way to MERGE implementation??

As of today, no application level code can possibly expect a valid OLD.*
table within ROLE/TRIGGER on INSERT - so no current code will be broken
by this.

Would it be possible to add this to a whishlist for 9.2 or something?

Regards,

-R

can u please give me it's link.
I found ngx_postgres module.

there is nginx_htttp_postgresql_module
exactly to connect webserver directly to postgresql
and to OUTPUT query result to a browser.

http://wiki.nginx.org/3rdPartyModules
http://labs.frickle.com/nginx_ngx_postgres/

Which is still a webserver on top of postgresql. Sure, you're coding more in
postgresql, but by the end of the day, a database is a database and a
webserver is a webserver. Sure, you can use a kitchen knife to chop firewood,
but wouldn't a tool made for the job be better at it?
IMHO you trade flexibility and security with "simplification" Personally I'd
never ever hook my database directly to the internet - for me that's like
writing your PIN on your banking card and leave it next to the ATM :-)

On Aug 14 2011, Uwe Schroeder wrote:

can u please give me it's link.
I found ngx_postgres module.

there is nginx_htttp_postgresql_module
exactly to connect webserver directly to postgresql
and to OUTPUT query result to a browser.

http://wiki.nginx.org/3rdPartyModules
http://labs.frickle.com/nginx_ngx_postgres/

Personally I'd never
ever hook my database directly to the internet - for me that's like
writing your PIN on your banking card and leave it next to the ATM :-)

DSTABASE IS THE BEST TOOL TO AGGANGE AN ACCESS CONTROL SCHEME

Thanks for everyone.
I will give reply after some experiments.
Regards,

Chaitanya Kulkarni

On Sun, Aug 14, 2011 at 6:04 PM, <sad@bestmx.ru> wrote:

On Sat, Aug 13, 2011 at 2:30 PM, Andreas Joseph Krogh
<andreak@officenet.no> wrote:

No, PG has never, and will never, act as an application-server.

Why in the world not? Now, it may or may not be a good idea but there
is no technical constraint that prevents postgresql from being used in
this fashion. I think it's a fine idea.

Postgresql has certain features, in particular being able to implement
functions in any language, that make it uniquely well suited among its
peers to act as a application server platform. Having a
quasi-functional front end to your middeware procedural code is very
powerful as is having direct access to a local data store for caching
and spooling purposes. So powerful that I would argue that if
properly supported by tools it would be superior to many of the more
classic stacks in use today. I could go down the list of reasons why
that's the case -- tight coupling with data, performance, emphasis on
transactional coding, etc. There are downsides too, but those could
be mitigated with some thought and work.

Postgres is not just a database -- it's a language hosting platform if
you want to use it as such. Now, you can continue to do things as
you've always done (database 'here', code 'here', web server 'here'),
but why discourage people from trying out different things?

merlin

På mandag 15. august 2011 kl 16:36:23 skrev du:

On Sat, Aug 13, 2011 at 2:30 PM, Andreas Joseph Krogh
<andreak@officenet.no> wrote:

No, PG has never, and will never, act as an application-server.

Why in the world not? Now, it may or may not be a good idea but there
is no technical constraint that prevents postgresql from being used in
this fashion. I think it's a fine idea.

Postgresql has certain features, in particular being able to implement
functions in any language, that make it uniquely well suited among its
peers to act as a application server platform. Having a
quasi-functional front end to your middeware procedural code is very
powerful as is having direct access to a local data store for caching
and spooling purposes. So powerful that I would argue that if
properly supported by tools it would be superior to many of the more
classic stacks in use today. I could go down the list of reasons why
that's the case -- tight coupling with data, performance, emphasis on
transactional coding, etc. There are downsides too, but those could
be mitigated with some thought and work.

Postgres is not just a database -- it's a language hosting platform if
you want to use it as such. Now, you can continue to do things as
you've always done (database 'here', code 'here', web server 'here'),
but why discourage people from trying out different things?

merlin

Sorry if I stepped on any toes here. But seriously - by my definition of app-server PG is not suited at all. I strongly would discourage anyone from using any RDBMS as an app-server. *IMO* it makes development, testing, separation-of-consern, debugging and re-deployment a nightmare.

PG has a lot of nice DB-features and I use it every day, but as an app-server - no way.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
Org.nr: NO 981 479 076 | |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

On Sat, Aug 13, 2011 at 9:57 AM, c k <shreeseva.learning@gmail.com> wrote:

Dear Postgres users,
from last few months I am reading and searching for can postgresql used as
application server? As postgresql supports many languages like pl/perl,

Besides the previously mentioned nginx module there's apache's mod
libpq http://asmith.id.au/mod_libpq.html

But I'd stick to a language to wrap stuff in like php etc.

On Mon, Aug 15, 2011 at 7:50 AM, Andreas Joseph Krogh
<andreak@officenet.no> wrote:

Sorry if I stepped on any toes here. But seriously - by my definition of app-server PG is not suited at all. I strongly would discourage anyone from using any RDBMS as an app-server. *IMO* it makes development, testing, separation-of-consern, debugging and re-deployment a nightmare.

PG has a lot of nice DB-features and I use it every day, but as an app-server - no way.

The key issue here IMO is what you really want in an app server, which
is why my answer is "kinda." PostgreSQL can do, and do well, a strong
subset of what an app-server does. It is not a reasonable replacement
for a standard middleware solution with all the bells and whistles,
however.

This being said, you could build an app server around PostgreSQL where
Pg is the entry point of the app server without a whole lot of
additional problems. Want to send an email? Write the data to a
queue table and NOTIFY...... Let another process pick it up and
handle it. And the approach you have to take is different from a
traditional app server approach, largely because of the separation of
concerns issues you flag.

In general, as with any multi-tiered application (and several tiers
can exist within PostgreSQL quite well), separation of concerns is a
critical aspect of design. I'd suggest that PostgreSQL can do at
least half of what folks usually expect from middleware by itself and
do it well, without other processes listening for notifications. For
example, it can accept business logic calls, take the data presented,
manipulate (and store/retrieve), and send the updated data back to the
client, all while enforcing security and data integrity constraints.
Practical experience suggests that this is a solid majority of what a
middleware program is actually doing. And it can do things better
because you don't really have to deal with all the ORM stuff that
usually ends up in that environment.

If you want to do stuff beyond that, it is really best to either do it
before hitting the database (through the client or some lightweight
middleware wrapper), or after (via helper programs and NOTIFY/LISTEN
commands).

I think using PostgreSQL as a business logic entry point is not
necessarily a bad idea, provided the application is well designed.
The idea of using it to connect to other databases however, is
generally best avoided (though there are some cases where it is
impossible to do otherwise).

Best Wishes,
Chris Travers