Re: [ADMIN] Using Postgresql as application server - Mailing list pgsql-general

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