Re: where to divide application and database

On Fri, 20 Feb 2009 20:45:20 +0000
Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:

> On Fri, Feb 20, 2009 at 04:51:33PM +0100, Ivan Sergio Borgonovo
> wrote:
> > What I find a bit annoying is politely deal with the error once
> > it is reported back to the application *and* connection and
> > *bandwidth* costs of moving clearly wrong data back and forward.

> This sounds a bit like premature optimization to me; I don't think

Well... I'd just know how things work. Not to optimise at the
starting blocks but rather to avoid cutting my way to optimisation
later. I'm glad to learn that not only postgresql is not seriously
affected by constraints but it may take advantage of them as
suggested by Ron Mayer.

> many people worry about optimizing the failure code paths. I know
> I prefer to make sure that things go quickly when they're
> working. If you're worried about someone performing a DOS attack
> on a failure then you'd want to optimize it, but surely you'd want
> the checks early in the application code.

There may be several reasons to "duplicate" checks in the
application too. Sometimes the failure path is more frequent than
the success path, sometimes you need quick feedback, sometimes it is
a matter of bandwidth etc...

> > If you've a good mapping between pg types and the application
> > language/library types it becomes easier to keep in sync those
> > checks otherwise it is a really boring job and DB checks becomes
> > just one more security net to maintain.

> It does, but constraints like that aren't going to be changing to
> regularly are they?

Actually if I was omniscient I wouldn't be so deeply involved with
programming... but even if I was, an application may serve different
needs during its lifespan.
And still having to write constraint in the application and in the
DB is twice the work.
Furthermore a DB reports error in a way that may not be useful to
the user.

create table test.zau(a int, b int);
insert into test.zau values('z','z');
ERROR: invalid input syntax for integer: "z"

create table test.zau(a int check (a>0), b int);
insert into test.zau values(-1,5);
ERROR: new row for relation "zau" violates check constraint
"zau_a_check"

And in a less than ideal world you may be tempted to put constraints
just in the client.

Once upon a long ago I gave a look to RoR and I vaguely remember you
could define tables with constraint in ruby and somehow you
automatically had constraints in the DB and some primitive check on
the client too.
But maybe I was daydreaming.
I wonder how all this magic works once you've to refactor.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it