Re: Problem with aborting entire transactions on error

Obviously, it's not fast...

On Tue, Dec 11, 2012 at 5:42 AM, Abel Abraham Camarillo Ojeda <
acamari(at)the00z(dot)org> wrote:

> 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(at)gmail(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>