Re: Does RAISE EXCEPTION rollback previous commands in a stored function?

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Re: Does RAISE EXCEPTION rollback previous commands in a stored function?
Дата
Msg-id A737B7A37273E048B164557ADEF4A58B53805B37@ntex2010i.host.magwien.gv.at
обсуждение исходный текст
Ответ на Re: Does RAISE EXCEPTION rollback previous commands in a stored function?  (Alexander Farber <alexander.farber@gmail.com>)
Список pgsql-general
Alexander Farber wrote:
> On Wed, Mar 2, 2016 at 11:18 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>> You cannot use START TRANSACTION, BEGIN, SAVEPOINT, COMMIT or ROLLBACK
>> inside a function.  A function always runs within one transaction.
>> 
>> Savepoints or subtransactions are written with a BEGIN ... EXCEPTION
>> block in PL/pgSQL, so you could write:
>> 
>> DECLARE FUNCTION .... AS
>> $$BEGIN
>>    /* UPDATE 1 */
>>    UPDATE ...;
>>    BEGIN  /* sets a savepoint */
>>       /* UPDATE 2, can cause an error */
>>       UPDATE ...;
>>    EXCEPTION
>>       /* rollback to savepoint, ignore error */
>>       WHEN OTHERS THEN NULL;
>>    END;
>> END;$$;
>> 
>> Even if UPDATE 2 throws an error, UPDATE 1 will be committed.

> Thank you, this is very helpful, just 1 little question:
> 
> 
> Why do you write just EXCEPTION?
> 
> 
> Shouldn't it be RAISE EXCEPTION?

That's something entirely different, see
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

The above construct *catches* the exception, which might be
raised by the UPDATE statement.

Yours,
Laurenz Albe

В списке pgsql-general по дате отправления:

Предыдущее
От: Alexander Farber
Дата:
Сообщение: Re: Does RAISE EXCEPTION rollback previous commands in a stored function?
Следующее
От:
Дата:
Сообщение: "missing chunk number XX for toast value YY in pg_toast ..." after pg_basebackup.