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

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.

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg