Re: Thoughs after discussions at OSCON - Mailing list pgsql-advocacy

ned@nedscape.com (Ned Lilly) writes:
> Chris Travers wrote:
>
>> Personally I have never bought the "Put as much logic into your
>> database as possible."  This can *easily* be taken way too far.
>> Review the discussions on pgsql-general about why sending email
>> from the database backend is a bad idea.  Can you write a CRM
>> application server in PLPGSQL?  Sure.  But I am not sure it is a
>> good idea.....
>
> We've got a very powerful ERP system that has most of its
> transactional business logic in pl/pgsql, so I'll respectfully
> disagree with you here ;-) We think it makes a powerful showcase for
> what PostgreSQL can do with even commodity-level server hardware.

It seems to me to be a tough call exactly where to stop.

There is considerable *obvious* merit to adding in logic that resides
at the "declarative" level such as the case where constraints provide
somewhat self-documenting data validation.

It seems to me that adding additional such "predicates" comes at a
relatively low cognitive cost.

Foreign key constraints are commonly worthwhile, albeit being
something that has a slightly higher "cognitive cost" as well as
having some potentially negative performance implications.  You
mightn't want to implement every FK that is theoretically possible to
implement.

Implementing APIs within the database falls, in my mind, into a more
ambiguous area.

There are numerous good things about making extensive use of pl/pgsql;
you cut down on round trips, and can keep the data validation in an
API that, by being in the DBMS, makes it accessible to ANY accessing
application regardless of what language the application may use.

But it adds an extra layer of logic, and figuring out what is running
where does introduce some "cognitive cost."

Furthermore, if there is a legitimate need for portability between
databases (e.g. - you have an important customer who REALLY wants
Oracle|DB2 support), the cost of using stored procedures efficiently
and quasi-portably goes way up.

For someone to argue that implementing most business logic inside the
DB isn't their favorite idea is something where there needs to be some
room for disagreement :-).
--
output = ("cbbrowne" "@" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/emacs.html
"Few people can be happy  unless they hate someother person, nation or
creed."  -- Bertrand Russell