Re: Problem with aborting entire transactions on error - Mailing list pgsql-general

On Dec 9, 2012, at 22:20, Zbigniew <zbigniew2011@gmail.com> wrote:

> Hello,
>
> As I read while googling the web, many people complained about this
> before. Couldn't it be made optional (can be even with "default ON")?
> I understand, that there are situations, when it is a must - for
> example, when the rest of queries rely on the result of first ones -
> but there are numerous situations, when just skipping a faulty query
> is all we need.
>
> A simple - but very common - example: I wanted to perform really large
> number of inserts - using transaction, to make it faster - while being
> sure the duplicate entries will be skipped.

And what happens if one of those inserts errors out for reasons other than a duplicate?

> Of course, this job will
> be done best by server itself, which is keeping an eye on "primary
> key" of the table. Unfortunately: not during a transaction! Any "dupe"
> will trash thousands other (proper) entries immediately.
>
> Why is this? My guess is, there is kind of logic in the code, like this:
>
> if { no error during query } {
>  do it
> } else {
> withdraw this one
> rollback entire transaction
> }
>
> Therefore my request - and, as I saw, of many others - would be just
> to introduce a little change:
>
> if { no error during query } {
>  do it
> } else {
> withdraw this one
> if { ROLLBACK_ON_ERROR } {
>   rollback entire transaction
>  }
> }
>
> (if there's no ROLLBACK_ON_ERROR - it should carry on with the
> remaining queries)
>
> Is it really so problematic to introduce such code change, allowing
> the users to control this behaviour?

Since current belief is that such behavior is unwise no one is willing to give their time to doing so. I'm not sure
whetherthat means that if you supplied such a patch it would be rejected.  Since their is a correct way to solve the
duplicatesscenario (see below) without savepoints you may wish to supply another example if you want to try and
convincepeople. 

> Yes, I read about using
> "savepoints" - but I think we agree, it's just cumbersome workaround -
> and not real solution, like my proposal. All we need is either a
> variable to set, or a command, that will allow to modify the present
> functionality in the way described above.

The true solution is to insert into a staging table that allows duplicates (but ideally contains other constraints that
youdo care about) and then de-dupe and insert into your final table. 

> --
> regards,
> Zbigniew
>

This may be an undesirable instance of the database not allowing you to shoot yourself in the foot but as ignoring
errorsis bad practice motivation to allow it is small.  You should always be able to import the data without errors
intoan explicitly defined table and then write queries to convert between the input format and the final format -
explicitlymaking no coding the necessary translation decisions and procedures.  In that way you always know that your
importroutine is always working as expected and not guessing whether it was the known error condition or an unknown
conditionthe caused a record to go missing. 

David J.