Re: When to choose putting logic into PL/pgSQL and when to use an app server

On 11/03/12 05:45, Rory Campbell-Lange wrote:
> On 10/03/12, Frank Lanitz (frank(at)frank(dot)uvena(dot)de) wrote:
>> I started to get into PL/pgSQL a bit as I'm started to work at an
>> application where I might can make usage of it. But I'm not sure even
>> those I read a bunch of documentation where is it useful to put logic
>> into database server and when I shall do the computing inside an
>> application layer... Does anyone of you have some kind of rule of a
>> thumb about that?
> It might depend on what sort of logic you are referring to. Some web
> frameworks provide very simple ways of persisting objects to the
> database and then querying these which are fine for many applications.
>
> If your data model requires good quality data and you are making heavy
> use of the relational aspects of the database, plpgsql can be an
> excellent choice.
>
> Wherever you do the bulk of the data "logic" you will no doubt be doing
> some work in SQL even if you are using an Object-Relational Mapper
> (ORM), or in the middleware language even if you are using plpgsql.
> Things like web forms are best validated in the middleware.
>
> From our experience we have altered our web frontend and middleware
> several times over the last 7 years while running much the same plpgsql
> functions. We are very happy with this arrangement.
>
> Rory
>

Before I read Rory's answer, my first thought was "It depends..." - one
of the most annoying and irritating answers an expert can give, but
unfortunately all too true in practice! :-)

I think for 'simple' situations it is better and faster to put the logic
in the database. However, I deliberately leave the definition of
'simple' undefined! My notion of 'simple' depends on my mindset at the
time -- have I been just been heavily immersed I database thinking, or
in considering aspects of middleware. So the answers you will get, and
I've seen I previous threads, depends a lot on where people's experience
mostly is.

I think whichever way you decide to jump, some use of stored procedures
may be beneficial -- a lot depends on your situation: skill sets, size
of team, business case, use cases, performance requirements, and
complexity of logic required -- both now (time to develop and
reliability) and in the medium to longer term (maintenance
considerations and chances of major changes in functionality).

If the application life is likely to be long term, beware of committing
to a database only approach - as you may suddenly realize a year or so
down the track, that the complexity has grown beyond what can be safely
down solely in the database (in terms of ease of development and
maintenance), and that you should have used middleware.

So in some situations, it may be best to start with using middleware,
even though it is not really required initially and may delay the
implementation of the first phase. However, by doing the middleware
initially you will gain experience when the system is less complicated
to understand, and the system can more easily scale into handling
greater complexity.

Note that middleware, like JBoss, can provide a lot of useful features
almost for free -- that otherwise you would have to investigate, design
, implement, and have to maintain yourself. For serious enterprise
stuff, nothing beats Java. I started working life with FORTRAN on
minicomputers, have had many years experience with COBOL systems on a
mainframe , and have taught C to experienced programmers -- yes, I am
ancient! :-)

It may be, that unless you have investigated what features and
facilities middleware can offer, that you have vastly underestimated
what is required to implement a robust and secure production system.

So I suggest that you have a look at middleware, even if you decide not
to use it.

Cheers,
Gavin