Обсуждение: 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
manylanguages like pl/perl, pl/python etc, supports dblink like functions to connect to other postgresql servers and
nowfeatures are in development to use external data. Postgresql works well on many operating systems and has a stable
andgood quality code. As many users are using plpython or plperl to work on many types of data and to implement logic
thatcan 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
architectureis 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

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.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

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

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.

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.

Further to Scott's comment, we are running our application platform on
nginx/php (using php-fpm).
It scales very well and it is extremely fast.
When running under Apache, we had to constantly restart the apache
service because it could not handle the load (at 150 concurrent users
hitting the app it would pretty much grind to a standstill).  We moved
to Nginx, have quadrupled the number of devices which are accessing our
platform, and the number of php processes which are actually running is
< 10.  I had allocated a dynamic pool whereby we have 35 processes ready
to accept requests at all times and which can dynamically scale up, but
it does not come close to needing to use all of the existing processes.
Everything is running under FreeBSD 8.1 amd64.

As Scott mentioned, wrapping it into a real language is much better -
the extent of what you will be able to do within the module will be
severely limited vs a real language.


> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
> owner@postgresql.org] On Behalf Of Scott Marlowe
> Sent: Monday, August 15, 2011 9:54 AM
> To: c k
> Cc: pgsql-general@postgresql.org; pgsql-admin
> Subject: Re: [ADMIN] Using Postgresql as application server
>
> 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.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Scott Marlowe пишет:
> 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.

BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Mon, Aug 15, 2011 at 11:33 AM, sad@bestmx.ru <sad@bestmx.ru> wrote:
> Scott Marlowe пишет:
>>
>> 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.
>
> BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP

But I can throw 1,000 cores at a large load with php.  Much harder to
do with plpgsql.

Scott Marlowe пишет:
> On Mon, Aug 15, 2011 at 11:33 AM, sad@bestmx.ru<sad@bestmx.ru>  wrote:
>> Scott Marlowe пишет:
>>> 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.
>> BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP
> But I can throw 1,000 cores at a large load with php.  Much harder to
> do with plpgsql.
and?
all of them would inevitably connect the same postgresql

the solution is outside the scope.
...a web-server page cache is one element of a solution

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Aug 13, 2011, at 2:44 PM, 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
serverthe 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.
>


You could also use something like node.js which allows you to connect Javascript directly to Postgres using the front
end/backend protocol.  

With something like this, you could talk directly to Postgres from the browser:

http://ajaxian.com/archives/tcpsocket-sockets-in-the-browser

http://www.postgresql.org/docs/9.0/interactive/protocol.html

I'm not saying this is the right approach, but if that is what you are really after...



John DeSoi, Ph.D.

2011/8/16 sad@bestmx.ru <sad@bestmx.ru>:
> Scott Marlowe пишет:
>>
>> On Mon, Aug 15, 2011 at 11:33 AM, sad@bestmx.ru<sad@bestmx.ru>  wrote:
>>>
>>> Scott Marlowe пишет:
>>>>
>>>> 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.
>>>
>>> BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP
>>
>> But I can throw 1,000 cores at a large load with php.  Much harder to
>> do with plpgsql.
>
> and?
> all of them would inevitably connect the same postgresql

And they'd each need postgresql to do a concat?  I'd hope nobody was
dumb enough to program the app layer to do something like that.  PG
might make a decent app server, but there's no way you could scale it
to millions of users like you could a farm of app servers.

Technically it can be done, but just because we can do something does not
mean we should do something. Having said that...

We have been using a middleware product that shall remain nameless,
that goes against a large commercial database that shall also remain nameless.
The middleware has been migrating to a more and more database based code
set, and as an administrator of such a system I can state that this is
awful.

Getting appropriate logging out of the application logic for both auditing purposes
and trouble shooting is near impossible. Performance is nearly impossible to tune as
everything runs inside the database. One giant process chewing up cores of CPU power.


Security is near impossible to manage as well. Again, almost everything needs to run as
the same user. The database is now making calls to generate pdf objects and make
printing calls.

None of the traditional tools can be used to integrate the application into the enterprise.
The load balancer needs to add x-forwarded headers to http requests, but the
custom http code can't handle that, so all web access appears to come from the load
balancer. This violates regulatory requirements. Log file formats are not standard
since none of the code is standard, this means that none of the event correlation
tools can be used for intrusion detection etc.

It is just a nightmare. The previous version that had real middleware and real database
servers was much better. The workloads were different so each server could be tuned for
what it was doing. We were able to purchase hardware appropriate to the task. Big RAM
for database, big CPU for middleware. Overall it was cheaper.

Just my $0.02

Evan

Scott Marlowe wrote:
> 2011/8/16 sad@bestmx.ru <sad@bestmx.ru>:
>> Scott Marlowe ÐÉÛÅÔ:
>>> On Mon, Aug 15, 2011 at 11:33 AM, sad@bestmx.ru<sad@bestmx.ru> šwrote:
>>>> Scott Marlowe ÐÉÛÅÔ:
>>>>> 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.
>>>> BTW, string concatenation in postgresql (plpgsql) is FASTER than in PHP
>>> But I can throw 1,000 cores at a large load with php. šMuch harder to
>>> do with plpgsql.
>> and?
>> all of them would inevitably connect the same postgresql
>
> And they'd each need postgresql to do a concat?  I'd hope nobody was
> dumb enough to program the app layer to do something like that.  PG
> might make a decent app server, but there's no way you could scale it
> to millions of users like you could a farm of app servers.
>

Evan Rempel пишет:
> Security is near impossible to manage as well. Again, almost
> everything needs to run as
> the same user.

throw your nameless DMS away, or fire the architect.

>>> Scott Marlowe ÐÉÛÅÔ:
>>>> On Mon, Aug 15, 2011 at 11:33 AM, sad@bestmx.ru<sad@bestmx.ru> šwrote:
>>>>> Scott Marlowe ÐÉÛÅÔ:
>>>>>> 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.
>>>>> BTW, string concatenation in postgresql (plpgsql) is FASTER than
>>>>> in PHP
>>>> But I can throw 1,000 cores at a large load with php. šMuch harder to
>>>> do with plpgsql.
>>> and?
>>> all of them would inevitably connect the same postgresql
>>
>> And they'd each need postgresql to do a concat? I'd hope nobody was
>> dumb enough to program the app layer to do something like that. PG
>> might make a decent app server, but there's no way you could scale it
>> to millions of users like you could a farm of app servers.
>>
> 2011/8/16 sad@bestmx.ru <sad@bestmx.ru>:
>
>


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

I can't let this slide :-D

On Tue, Aug 16, 2011 at 9:27 AM, Evan Rempel <erempel@uvic.ca> wrote:
> Technically it can be done, but just because we can do something does not
> mean we should do something. Having said that...
>
> We have been using a middleware product that shall remain nameless,
> that goes against a large commercial database that shall also remain
> nameless.
> The middleware has been migrating to a more and more database based code
> set, and as an administrator of such a system I can state that this is
> awful.

From your description below, it truly sounds awful.  However, this
strikes me as being an issue of *which* logic is moved into the
database instead of *that* logic is being moved into the database.
>
> Getting appropriate logging out of the application logic for both auditing
> purposes
> and trouble shooting is near impossible. Performance is nearly impossible to
> tune as
> everything runs inside the database. One giant process chewing up cores of
> CPU power.

LedgerSMB has been moving in the direction of more logic in the
database because we have found the opposite.  Troubleshooting is
easier, code maintenance is easier, performance is easier to
troubleshoot and manage, and security is far more manageable.  Now,
granted we are retrofitting security onto a codebase which had none
when we forked, so that is a difference.....  We have eliminated a
much larger number of bottlenecks by going this way than we have run
into.  Again the issue is *which* logic goes into the db, and that's
an absolutely key question when running middle tiers in the dbms.
>
>
> Security is near impossible to manage as well. Again, almost everything
> needs to run as
> the same user. The database is now making calls to generate pdf objects and
> make
> printing calls.

Ouch.... I guess I could kinda see the PDF generation calls (I'd still
prefer a queue and transform approach), but not the printing calls.
And if you can't move security into the database, then you have a kind
of major problem:  you aren't *really* generating a consistent and
consistently enforced API in this way, and so you can't get to the
roles a middleware solution gives you.

As long as you still need the middleware, then the question really
becomes, what logic needs to be centralized in the db and what logic
is specific to each middleware application?  What do you get from
putting each thing in the database?  My recommendation is to review
that.  If it is a single app db, then use stored procs as essentially
named queries.  If it is possible, move the printing calls into a
separate process and have it signalled by the db app on database
commit.

But it really sounds like an unmaintainable mess.  IME, however, that
is avoidable while still placing the RDBMS in the center of the
logic-complete application server environment.
>
> None of the traditional tools can be used to integrate the application into
> the enterprise.
> The load balancer needs to add x-forwarded headers to http requests, but the
> custom http code can't handle that, so all web access appears to come from
> the load
> balancer. This violates regulatory requirements. Log file formats are not
> standard
> since none of the code is standard, this means that none of the event
> correlation
> tools can be used for intrusion detection etc.

Ouch.....  I second the suggestion that the architecture here lacks
the separation of concerns approach necessary to make this work, and
that either the software you are using is the problem or the architect
is.  However, it doesn't tell me that the approach of using the RDBMS
as the entry point into an application server environment is
necessarily a bad thing.

Best Wishes,
Chris Travers

On Tue, Aug 16, 2011 at 11:27 AM, Evan Rempel <erempel@uvic.ca> wrote:
> Technically it can be done, but just because we can do something does not
> mean we should do something. Having said that...
>
> We have been using a middleware product that shall remain nameless,
> that goes against a large commercial database that shall also remain
> nameless.
> The middleware has been migrating to a more and more database based code
> set, and as an administrator of such a system I can state that this is
> awful.
>
> Getting appropriate logging out of the application logic for both auditing
> purposes
> and trouble shooting is near impossible. Performance is nearly impossible to
> tune as
> everything runs inside the database. One giant process chewing up cores of
> CPU power.
>
>
> Security is near impossible to manage as well. Again, almost everything
> needs to run as
> the same user. The database is now making calls to generate pdf objects and
> make
> printing calls.
>
> None of the traditional tools can be used to integrate the application into
> the enterprise.
> The load balancer needs to add x-forwarded headers to http requests, but the
> custom http code can't handle that, so all web access appears to come from
> the load
> balancer. This violates regulatory requirements. Log file formats are not
> standard
> since none of the code is standard, this means that none of the event
> correlation
> tools can be used for intrusion detection etc.
>
> It is just a nightmare. The previous version that had real middleware and
> real database
> servers was much better. The workloads were different so each server could
> be tuned for
> what it was doing. We were able to purchase hardware appropriate to the
> task. Big RAM
> for database, big CPU for middleware. Overall it was cheaper.

very few of those complaints would apply to postgres -- the database
does not run in a single process (thank goodness for that!), logging
via the various pls is trivially done and effective, etc.  also if
you're serving http it does make sense to wrap postgres with a thin
http server (lighttpd, node.js, nginx are all good choices).   most of
the rest of your gripes seem to apply more to the specific middleware
implementation vs a general appraisal of the technique.  it's trivial
to implement database side security systems and many people do so.

one point that is getting lost in all this that if you are using a
database for an application server, this does not mean it's the same
database as your main database or even on the same machine -- you can
still keep traditional separation of roles and use something like
dblink to transfer data.  regarding the use of postgres functions for
things like reports and pdf generation, I see absolutely nothing wrong
with doing this...although plpgsql is not a very good
choice...pl/python or pl/java would be a better way to go.

merlin

On Tue, Aug 16, 2011 at 12:25 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> one point that is getting lost in all this that if you are using a
> database for an application server, this does not mean it's the same
> database as your main database or even on the same machine -- you can
> still keep traditional separation of roles and use something like
> dblink to transfer data.  regarding the use of postgres functions for

I'm thining pl/proxy would be pretty amazing here.  I still prefer
using another layer in another language for an app server, but now
that you've got me thinking a bit more out of the box, pl/proxy would
increase your ability to scale quite a lot.

On 8/16/11 10:24 AM, Chris Travers wrote:
> I can't let this slide :-D
>
> On Tue, Aug 16, 2011 at 9:27 AM, Evan Rempel<erempel@uvic.ca>  wrote:
>> Technically it can be done, but just because we can do something does not
>> mean we should do something. Having said that...
>>
>> We have been using a middleware product that shall remain nameless,
>> that goes against a large commercial database that shall also remain
>> nameless.
>> The middleware has been migrating to a more and more database based code
>> set, and as an administrator of such a system I can state that this is
>> awful.
> > From your description below, it truly sounds awful.  However, this
> strikes me as being an issue of *which* logic is moved into the
> database instead of *that* logic is being moved into the database.
In other words, it's just a typical engineering problem: pick the right tools for the job.  Just because you're a good
masondoesn't mean you should build a boat out of cement.  You can do it, but aluminum, steel, fiberglass and wood are
betterchoices.  Just because you are really good at database programming doesn't mean you should turn a database server
intoa web server.  You can do it, but it will be an expensive, suboptimal, and unmaintainable system. 

There are many opinions about the exact right balance of where functionality should be implemented, but extreme
solutionsare almost never optimal. 

Craig

>> Getting appropriate logging out of the application logic for both auditing
>> purposes
>> and trouble shooting is near impossible. Performance is nearly impossible to
>> tune as
>> everything runs inside the database. One giant process chewing up cores of
>> CPU power.
> LedgerSMB has been moving in the direction of more logic in the
> database because we have found the opposite.  Troubleshooting is
> easier, code maintenance is easier, performance is easier to
> troubleshoot and manage, and security is far more manageable.  Now,
> granted we are retrofitting security onto a codebase which had none
> when we forked, so that is a difference.....  We have eliminated a
> much larger number of bottlenecks by going this way than we have run
> into.  Again the issue is *which* logic goes into the db, and that's
> an absolutely key question when running middle tiers in the dbms.
>>
>> Security is near impossible to manage as well. Again, almost everything
>> needs to run as
>> the same user. The database is now making calls to generate pdf objects and
>> make
>> printing calls.
> Ouch.... I guess I could kinda see the PDF generation calls (I'd still
> prefer a queue and transform approach), but not the printing calls.
> And if you can't move security into the database, then you have a kind
> of major problem:  you aren't *really* generating a consistent and
> consistently enforced API in this way, and so you can't get to the
> roles a middleware solution gives you.
>
> As long as you still need the middleware, then the question really
> becomes, what logic needs to be centralized in the db and what logic
> is specific to each middleware application?  What do you get from
> putting each thing in the database?  My recommendation is to review
> that.  If it is a single app db, then use stored procs as essentially
> named queries.  If it is possible, move the printing calls into a
> separate process and have it signalled by the db app on database
> commit.
>
> But it really sounds like an unmaintainable mess.  IME, however, that
> is avoidable while still placing the RDBMS in the center of the
> logic-complete application server environment.
>> None of the traditional tools can be used to integrate the application into
>> the enterprise.
>> The load balancer needs to add x-forwarded headers to http requests, but the
>> custom http code can't handle that, so all web access appears to come from
>> the load
>> balancer. This violates regulatory requirements. Log file formats are not
>> standard
>> since none of the code is standard, this means that none of the event
>> correlation
>> tools can be used for intrusion detection etc.
> Ouch.....  I second the suggestion that the architecture here lacks
> the separation of concerns approach necessary to make this work, and
> that either the software you are using is the problem or the architect
> is.  However, it doesn't tell me that the approach of using the RDBMS
> as the entry point into an application server environment is
> necessarily a bad thing.
>
> Best Wishes,
> Chris Travers
>