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

On Thu, Mar 15, 2012 at 9:11 AM, Lew <noone@lewscanon.com> wrote:
> Gavin Flower wrote:
>>
>> Rory Campbell-Lange wrote:
>>
>>> Frank Lanitz 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.
>
>
> +1 to that.
>
> I use the rule of thumb that if it's strictly about data, e.g., enforcing
> consistency between tables, it can be a stored procedure. If it's about
> application logic, it goes in middleware.
>
> People have tried to put application logic in stored procedures. Some of
> those are vehement acolytes for their error.
>
> As Gavin says, it can hurt ease of development and maintenance to push too
> much into stored procedures. It doesn't scale. You need DBA/programmers to
> write your stuff. Debugging is harder. You lose flexibility to develop new
> applications for the same data. You can break the relational model too much.
>
> How much is "too much"? If it isn't strictly about data and the (relational)
> data model, it's too much. If it looks like entities and domain-specific
> abstractions instead of tables and rows and columns, it's too much.
>
> Database and business logic have different domains of discourse. If they're
> crossing over to each other, except in ORM mapping code, it's a problem.

Let me offer a dissenting opinion.  First of all, "business logic" is
an amorphous term with no formal definition.  If you work in a
business and employ logic, you are using business logic.  I put that
logic in the technologies that get the job done the fastest and best.
In the earlier days of my career I used to think SQL was an
anachronism and OO style programmatic management of data was the
future.  I have since totally repudiated that view.  I realize that is
against the prevailing winds of current development trends -- OO is
popular in corporate development because it tends to mesh well
(superficially) with industrial type programming techniques but in
most reasonable metrics starting with output progress per unit of
input work OO falls short.

Programming in the enterprise tends to be very data driven for the
most part and SQL is a language designed for moving data around.  The
relational model and SQL were invented to formalize and abstract
common problems working with data.  Java and friends are all
imperative system programming languages that rely on a mixture of
native constructs (definition of classes and such)  and libraries to
do the data crunching.  Those libraries strongly define your ability
to deal with problems and still tend to come up woefully short in
terms of offering things that SQL has had for 20 years now, like being
able to roll back execution state AND stored data to a known safe
point, or a concurrency model that the average programmer can code
against without introducing a bug with every other line of code.  ORMs
and the like try to work around these problems and generally come up
short in my experience.

merlin