Re: Current transaction is aborted, commands ignored until end of transaction block

Hi Leif,

On 30/12/11 22:44, Leif Biberg Kristensen wrote:
> Fredag 30. desember 2011 09.43.38 skrev Jan Bakuwel :
>
>> Would be nice to have an option in PostgreSQL something along the lines
>> of: 'abort-transaction-on-constraint-violation = false'....
> That option is called MySQL with MyISAM tables.
>
> Seriously, if the user encounters a constraint violation, that is IMO a
> symptom of bad design. Such conditions should be checked and caught _before_
> the transaction begins.

Really?

One of my detail tables here is a list of codes. The design currently is
so that you are not allowed to add two identical codes in that table for
a particular related master record, ie. if you try it raises a
constraint violation (duplicate key). Users try anyway (you know those
pesky users doing things they're not supposed to do).

Why would that a bad design?

I simply want to tell the user: sorry you can't do this because it
violates a constraint (duplicate key).

Sometimes they try to delete something that has other records referring
to it and the database design is so that it won't cascade delete (for
various reasons). In that case I want to tell them: sorry you can't do
this because there are related records.

In a well designed system, you'd have those constraints at the database
level not the application level and use exception handling to deal with
these, not write tests to find out the possible error conditions
beforehand. Of course it's possible to write code around all of this
(and I'm starting to realise that is what I might have to do) but I
consider that bad design.

I don't claim to know all other RDBMS but I think PostgreSQL might be
one of the few (or only one) that considers a constraint violation
something really really really serious... so serious that the
transaction will have to be aborted. Quite a few other RDBMS will give
you the error but will also allow you to continue on your merry way (and
not loose everything you've done up to that point).

Why-o-why have the PostgreSQL developers decided to do it this way...?

regards,
Jan