postgres/perl, autocommit and BEGIN; COMMIT;

[Marcus Brubaker]

On Mon, 2004-05-31 at 14:24, cbbrowne-HInyCGIudOg at public.gmane.org wrote:
> > On Monday 31 May 2004 08:23, cbbrowne-HInyCGIudOg at public.gmane.org wrote:
> > 
> > > There are two usual approaches for dealing with invalid input:
> > >
> > > =A01. =A0Throw an error back to the application, or
> > > =A02. =A0Take a wild guess at some alternative value, and use it instead.
> > >
> > > If you want data integrity of your database, 1. is the right answer.
> > > MySQL does 2.
> > 
> > I had always thought the answer was:
> > 
> > 3. Never accept invalid input.
> 
> That's ideal; the challenge comes in that you may have lots of code out
> there that accesses the data.
> 
> If the database does no data validation, then that mandates that
> validation be spread deeply throughout your application.  If there is a
> single function that accesses the database that does not do all
> necessary validation, then you just created a route to corrupt the
> contents of the database.

Sounds a lot like the arguments for language level type checking.  I
treat (My)SQL like a half-wit data storage layer with some nice features
and assume it is generally the wrong place to check data integrity.  Let
it be fast at what it does and I'll do the detailed rule checking the
way I feel it should be done.

After reading over the page I find the claims of MySQL eating data to be
a bit sensationalistic.  Does it do some slightly unexpected things? 
Sure, but nothing really terrible and certainly nothing which would
affect a reasonably written piece of code.  About the worst behaviour I
could find was concerning the integer overflow and, frankly, overflow
errors are something that should be checked for at the application level
(along with the other data sanity checks you're doing, right?  you don't
just blindly trust user input data do you?) and designed for at the
database level.  If you're going to have prices of $22,000, then make
sure your database supports at least 5 unsigned digits or 6 signed
digits.  It's really not fair to blame the DB engine for poor database
design.

> 
> > If invalid input ever touches your db layer then your software has a
> > large bug.
> 
> If you can come up with a way to guarantee that ALL the code throughout
> ALL of a complex distributed application cannot be affected by invalid
> input, then you have solved one of the Great Problems of Distributed
> Computing.

How about database abstraction layer?  Never let applications modify or
insert data directly, only through the abstraction layer.  Seems not
only like a good way to assure data integrity but good programming
practice as well.  Particularly when you have a complex, distributed
application...

Regards,
-- 
Marcus Brubaker <marcus.brubaker-H217xnMUJC0sA/PxXw9srA at public.gmane.org>

--
The Toronto Linux Users Group.      Meetings: http://tlug.ss.org
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://tlug.ss.org/subscribe.shtml