Re: What is your favorite front end for user interaction to postgresql databases?

On Mon, May 08, 2006 at 04:17:53PM -0700, Casey Duncan wrote:
> From personal experience (and others will disagree), I find putting
> logic in the database to be a bad idea. I only use stored procedures
> for triggers and the like, and I try to avoid those whenever possible.
>
> Here are my reasons why:
>
> - I don't like the PL/pgSQL language (yes there are alternatives, but
> they have their own drawbacks)

Huh? Just use whatever language you're already writing in.

> - It's complex to test and upgrade (we actually wrote non-trivial
> infrastructure to automate both)

Uhm... CREATE OR REPLACE FUNCTION. Unless you're changing parameters it
works fine.

As for testing, I fail to see how it's more difficult than testing the
same thing using external code. I will grant that unit testing is harder
though, since you don't have as many opportunities to hook into the
code, but if you create a set of known test data it's not all that
difficult.

> - It's difficult to debug (compared to external languages like python
> or java)
> - It's difficult to profile, therefore getting good performance can
> be difficult

Actually, I think there's a commercial product that allows you to do
both, but I'm not sure. It would certanly be nice if it was built in.

> I had a very complex system coded in stored procedures that performed
> poorly and was hard to maintain. It's now refactored into java/
> hibernate code that's simpler to understand, performs much better and
> is easy to extend and maintain. Of course that's just my particular
> case and obviously YMMV.

If hibernate is performing better it's due to application design. A lot
of times people try and approach database development the same way you'd
approach procedural coding, which is a bad idea. Hibernate and other
products go to great lengths (ie: caching) to try and make procedural
coding techniques work well on databases.

> Stored procs could make a lot of sense if you have many different
> clients accessing the db in different ways and you want to strictly
> enforce business rules across all of them. I had no such requirements
> in my case.

You sure there won't every be any other apps hitting that database? :)
Part of how Pervasive makes money is dealing with exactly that kind of
attitude... "nothing else will ever have to communicate with this
system".

> In any case I would strongly recommend doing the simplest thing that
> you can get away with. If your business rules can be fulfilled with
> grants, views and constraints alone, use them.

Or maybe more accurately, do what you have the expertise for. If you've
got a good database developer on staff there's a lot to be said for
putting stuff into procedures, especially if it's database-intensive.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461