Re: Thoughs after discussions at OSCON

Certainly as you get 'farther away' from data storage, it becomes less
reasonable to do stuff in the database and more reasonable to do it
outside. Sending email is an example of something that could well be
done outside the database server. But, here's some things to consider:

- Anything done outside the database is roughly equivalent to cut and
pasting code instead of using functions

- Anything done outside of the database can't be transactionally safe

Let me expand on this with an example:

A bank offers a service to customers where anytime a deposit is made
into their account they'll get an email (BTW, this isn't BS: my bank
does this). There's multiple ways a deposit can hit a bank. You can
mail a deposit to them or see a teller. You can do it at an ATM. You can
get an electronic transfer. Now, though I'm not a banking expert, I'm
pretty certain that these will utilize different systems.

So, a (bad) way to do this would be to have each application (ie:
whatever tellers use) commit the transaction to the database and then
send an email. This means duplication of code. Or worse, a new
application is brought online and the team working on it didn't realize
they had to support sending an email when a deposit is made, so now the
feature that the bank is marketing to it's customers is broken.

A good way to do this would be to make the database responsible for
making sure these emails were sent out any time a deposit was made. This
doesn't mean the database server itself has to send the emails; you
could have a trigger that records a list of deposits that notifications
have to be sent out for. The point is you know that every deposit that
needs an email sent out is being flagged by the only part of the system
that can do that: the database.

Of course, email isn't something that's very transaction-safe, but even
in this example if the database is involved you can do a better job of
ensuring that emails will be sent. If the company email server is
temporarily down the process that runs through the list of deposits that
need an email sent out will stop, but that list is still there. Once the
email server is back the emails will start going out again. If the
emails were processed as transactions came in though, you'd either have
to stop accepting transactions while the email server was down, or the
emails just get lost.

Of course, just because I've come up with an example where it makes
sense to involve the database doesn't mean that it's always the best
thing to do. But ISTM there's a lot of people out there who do
everything they can to keep code out of the database when in fact they
should be doing the exact opposite.

So I view it this way: unless you have a very compelling reason why some
piece of code dealing with your data shouldn't be in the database, it's
a safe bet that it should be in the database. Even in this example,
there's not really much reason why the emails can't be sent from the
database server, assuming you have another machine that handles the
final email delivery to remote systems.

On Mon, Aug 15, 2005 at 01:57:10PM -0400, Chris Browne wrote:
> ned(at)nedscape(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461