Re: Triggers, Stored Procedures, PHP. was: Re: PostgreSQL Advocacy, Thoughts and Comments - Mailing list pgsql-general

From: "Paul Thomas" <paul@tmsl.demon.co.uk>:
> Stored procedures can be a 2-edged sword. They can lead to business logic
> being scattered between the persistence layer and the business layer.
> Thats not good for maintaining the application 3 years down the line.
> Triggers can also cause maintenance problems. Its so easy to forget/fail
> to document that inserting a record into table x causes column y of table
> z to be updated. Be careful how and where you use these features as they
> can come back to bite you!

It is all how you organize your app.  Stored proceedures are extremely
useful when they represent a unified API for accessing parts of the
database.  Word of advice:  Keep the database self-contained.  If all you
want is object persistance, then why non use Berkeley Database?  It is even
transactional.  The point of having an RDBMS is to provide more flexibility
than a simple persistance store.  When used sensibly, stored proceedures are
extremely simplifying, not the other way arround.


> >
> > Why should I not be able to use the same views and triggers etc  in
there
> > that I use for my web apps.  PHP is quite powerful if used correctly.
>
> You are, of course, free to do whatever want. But if you have to use
> features of the database to compensate for inadequacies in your
> programming language maybe you should be using another language?

I don't think Jason was compensating for weaknesses in the language-- I
think that he was asking why he woudln't want to build into the database the
universal functions accessed by multiple applications.  And he would be
right in trying to do so.

Let me give you an example:  One of the large projects I maintain is HERMES
(http://hermes.sourceforge.net).  Hermes relies on its own user and
permissions catalogs in order to provide a consistant administrative
interface across database managers and simplify the task of assigning
permissions to users and groups.  The differences in syntax can them be
handled in wrapper layers, etc.

However, it makes sense to try to wrap these catalogs using stored
proceedures so that third-party apps don't necessarily need to be aware of
the structure of the catalogs when assigning permissions.  This way, too,
the db users' catalog and the user catalog in the RDBMS can be guaranteed to
be in sync.  It will also allow me eventually to directly enforce
permissions using triggers rather than rely on the RDBMS model (useful in
shared hosting environments).

>
> > Java has its own issues and I am not sure it is as far supiour as you
> > are claming it is.  But that is not for this dscussion.
>
> I'm not aware of any "issues" with Java (unless you mean Swing ;)).

Every language has "issues."  This is not the time or place for a
development environemnt holy war ;-)  But--- PHP and Python all the way ;-)

>
> Much of the populatity of MySQL seems to stem from PHPs out-of-the-box
> support for it. With the MySQL client library license change, this
> situation will probably change. There was a long thread about this earlier
> this year. Check the archives.
>
Putting the cart before the horse.  MySQL is far easier to administer in a
shared hosting environment.  Maybe one of these days, I will put together a
package for managing PostgreSQL accounts in this way.  If there is interest,
please email me off-list and we can get started.  I don't expect MySQL's
dominance to change until we can offer an easy-to-administer alternative for
these environments.

Best Wishes,
Chris Travers