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

"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...?"

I don't know these other rdbms, but it sounds strange to have
transaction and not rollback if something is wrong...

Option in db i don't think is good generic solution because that
business rule is dynamic... In some case user wants to import
everything what is ok.. And then manually fix errors, but in some not
simply they want all or nothing...

so manually entering and import are two different processes...

Our solution for partial import case is to import all data to staging
table without constraint... Validate data... Import valid... Not valid
show to user so they can fix what is wrong etc...

Kind Regards,

Misa

Sent from my Windows Phone
From: Jan Bakuwel
Sent: 30/12/2011 23:52
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] 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