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

On Mon, Aug 15, 2005 at 01:57:10PM -0400, Chris Browne wrote:
> 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.

It can get pretty high when the constraints are complicated, and until
there's DOMAINs are actually enforced (e.g. on being returned from a
function) the way TYPEs are, we've got no good way to handle this.

> 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."

It does.  The next question is, "what do other options for doing the
same thing cost on this scale?" and of course the answer, as with
fuzzy, complicated situations as a rule, is "it depends."

> 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.

Is it any more hassle than other options for multiple database
support?  I guess that comes down to "it depends," too.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!