Re: Problem with aborting entire transactions on error

I generally do:

DO $$
BEGIN
        INSERT INTO ...;
EXCEPTION
        WHEN UNIQUE_VIOLATION or EXCLUSION_VIOLATION THEN
                NULL; /* ignore this record */
END;
$$;

On Sun, Dec 9, 2012 at 9:20 PM, 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. 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? 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.
--
regards,
Zbigniew


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general