=> DO $$
DECLARE
    n integer;
BEGIN
    SELECT id INTO STRICT n FROM t;
END;
$$;

ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

Чтобы перехватить ошибку, в блоке нужен обработчик:

=> DO $$
DECLARE
    n integer;
BEGIN
    SELECT id INTO STRICT n FROM t;
EXCEPTION
    WHEN no_data_found THEN
        RAISE NOTICE 'Ошибка перехвачена';
END;
$$;

NOTICE:  Ошибка перехвачена
DO

Вместо имени ошибки можно указать ее код, который всегда представляет собой строку из пяти символов. Определить код ошибки можно с помощью специальной переменной SQLSTATE (а SQLERRM покажет текст сообщения):

=> DO $$
DECLARE
    n integer;
BEGIN
    SELECT id INTO STRICT n FROM t;
EXCEPTION
    WHEN SQLSTATE 'P0002' THEN -- no_data_found
        RAISE NOTICE 'sqlstate = %, sqlerrm = %', SQLSTATE, SQLERRM;
END;
$$;

NOTICE:  sqlstate = P0002, sqlerrm = query returned no rows
DO

Ошибки организованы в своеобразную двухуровневую иерархию. Категория ошибки имеет код, заканчивающийся на три нуля, и соответствует любой ошибке с теми же первыми двумя символами.

=> DO $$
DECLARE
    n integer;
BEGIN
    SELECT id INTO STRICT n FROM t;
EXCEPTION
    WHEN plpgsql_error THEN -- SQLSTATE P0000
        RAISE NOTICE 'sqlstate = %, sqlerrm = %', sqlstate, sqlerrm;
END;
$$;

NOTICE:  sqlstate = P0002, sqlerrm = query returned no rows
DO

И в обратную сторону - ошибку можно принудительно вызвать по ее коду или имени:

=> DO $$
BEGIN
    RAISE no_data_found;
EXCEPTION
    WHEN others THEN
        RAISE NOTICE 'sqlstate = %, sqlerrm = %', sqlstate, sqlerrm;
END;
$$;

NOTICE:  sqlstate = P0002, sqlerrm = no_data_found
DO

Здесь мы используем специальное имя others, которое соответствует любой ошибке, которую имеет смысл перехватывать (за исключение прерванного клиентом выполнения и нарушения отладочной проверки assert).

При необходимости можно задействовать и пользовательские коды ошибок, отсутствующие в справочнике, а также указать некоторую дополнительную информацию (в примере - только часть из возможного):

=> DO $$
BEGIN
    RAISE SQLSTATE 'ERR01' USING
        message = 'Сбой матрицы',
        detail  = 'При выполнении функции произошел непоправимый сбой матрицы',
        hint = 'Обратитесь к системному администратору';
END;
$$;

ERROR:  Сбой матрицы
DETAIL:  При выполнении функции произошел непоправимый сбой матрицы
HINT:  Обратитесь к системному администратору
CONTEXT:  PL/pgSQL function inline_code_block line 3 at RAISE

Эту информацию нельзя получить из переменных; если нужно проанализировать такие данные в коде, есть специальная конструкция:

=> DO $$
DECLARE
    message text;
    detail text;
    hint text;
BEGIN
    RAISE SQLSTATE 'ERR01' USING
        message = 'Сбой матрицы',
        detail  = 'При выполнении функции произошел непоправимый сбой матрицы',
        hint = 'Обратитесь к системному администратору';
EXCEPTION
    WHEN others THEN
        GET STACKED DIAGNOSTICS
            message = message_text,
            detail = pg_exception_detail,
            hint = pg_exception_hint;
        RAISE NOTICE E'\nmessage = %\ndetail = %\nhint = %', message, detail, hint;
END;
$$;

NOTICE:  
message = Сбой матрицы
detail = При выполнении функции произошел непоправимый сбой матрицы
hint = Обратитесь к системному администратору
DO

Стек вызовов

=> CREATE FUNCTION f1(n integer) RETURNS float AS $$
BEGIN
    RETURN n * f2(n-1);
END;
$$ IMMUTABLE LANGUAGE plpgsql;

=> CREATE FUNCTION f2(n integer) RETURNS float AS $$
BEGIN
    RETURN n + f3(n-1);
END;
$$ IMMUTABLE LANGUAGE plpgsql;

=> CREATE FUNCTION f3(n integer) RETURNS float AS $$
BEGIN
    RETURN 1.0 / n;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

ERROR:  division by zero
CONTEXT:  PL/pgSQL function f3(integer) line 3 at RETURN
PL/pgSQL function f2(integer) line 3 at RETURN
PL/pgSQL function f1(integer) line 3 at RETURN

То, что мы видим в сообщении об ошибке - это стек вызовов: сверху вниз = изнутри наружу.

В обработчике ошибки можно получить доступ к стеку, правда, в виде одной строки:

=> CREATE OR REPLACE FUNCTION f3(n integer) RETURNS float AS $$
DECLARE
    ctx text;
BEGIN
    RETURN 1.0 / n;
EXCEPTION
    WHEN division_by_zero THEN
        GET STACKED DIAGNOSTICS ctx = pg_exception_context;
        RAISE NOTICE '%', ctx;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

NOTICE:  PL/pgSQL function f3(integer) line 5 at RETURN
PL/pgSQL function f2(integer) line 3 at RETURN
PL/pgSQL function f1(integer) line 3 at RETURN
ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function f3(integer)
PL/pgSQL function f2(integer) line 3 at RETURN
PL/pgSQL function f1(integer) line 3 at RETURN

Мы перехватили ошибку, но если теперь функция f3() завершается успешно, она должна вернуть какое-то значение. Либо нужно вызвать какую-то другую ошибку, или даже ту же самую (написав просто RAISE).

Это случай, когда ошибка происходит внутри обработчика ошибок. Такую ошибку тоже можно перехватить, но уже не в этом блоке, а во внешнем.

=> CREATE OR REPLACE FUNCTION f3(n integer) RETURNS float AS $$
DECLARE
    ctx text;
BEGIN
    RETURN 1.0 / n;
EXCEPTION
    WHEN division_by_zero THEN
        GET STACKED DIAGNOSTICS ctx = pg_exception_context;
        RAISE NOTICE '%', ctx;
        RETURN 0.0; -- добавили
END;
$$ IMMUTABLE LANGUAGE plpgsql;

NOTICE:  PL/pgSQL function f3(integer) line 5 at RETURN
PL/pgSQL function f2(integer) line 3 at RETURN
PL/pgSQL function f1(integer) line 3 at RETURN
 f1 
----
  2
(1 row)

Как не надо

Встречаются (и довольно часто) случаи, когда можно обойтись без обработки ошибок, если выбрать другие подходящие средства.

Скажем, в предыдущем примере можно было явно проверить знаменатель на ноль:

=> CREATE OR REPLACE FUNCTION f3(n integer) RETURNS float AS $$
BEGIN
    RETURN CASE WHEN n = 0 THEN 0.0 ELSE 1.0 / n END;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

Посмотрим несколько более сложных примеров.

Как не надо: значение или null

Задача: вернуть строку из справочника или NULL, если строки нет.

=> CREATE TABLE categories(code text, description text);

=> INSERT INTO categories VALUES ('books','Книги'), ('discs','Диски');

=> CREATE UNIQUE INDEX ON categories(code);

=> CREATE FUNCTION get_cat_desc(code text) RETURNS text AS $$
DECLARE
    desc text;
BEGIN
    SELECT c.description INTO STRICT desc
    FROM categories c
    WHERE c.code = get_cat_desc.code;

    RETURN desc;
EXCEPTION
    WHEN no_data_found THEN
        RETURN null;
END;
$$ STABLE LANGUAGE plpgsql;

 get_cat_desc 
--------------
 Книги
(1 row)

Можно ли проще? Да, надо просто убрать STRICT или использовать подзапрос:

=> CREATE OR REPLACE FUNCTION get_cat_desc(code text) RETURNS text AS $$
BEGIN
    RETURN (SELECT c.description FROM categories c WHERE c.code = get_cat_desc.code);
END;
$$ STABLE LANGUAGE plpgsql;

В таком варианте хорошо видно, что PL/pgSQL тут вообще не нужен - достаточно SQL.

 get_cat_desc 
--------------
 Книги
(1 row)

Как не надо: обновить или вставить

Задача: обновить строку таблицы с определенным идентификатором; если такой строки нет - вставить ее.

=> CREATE OR REPLACE FUNCTION change(code text, description text) RETURNS void AS $$
DECLARE
    cnt integer;
BEGIN
    SELECT count(*) INTO cnt FROM categories c WHERE c.code = change.code;
    IF cnt = 0 THEN
        INSERT INTO categories VALUES (code, description);
    ELSE
        UPDATE categories c
        SET description = change.description
        WHERE c.code = change.code;
    END IF;
END;
$$ VOLATILE LANGUAGE plpgsql;

Практически все, начиная с того, что такая функция будет работать некорректно на уровне изоляции read committed при наличии нескольких параллельно работающих сеансов. Причина в том, что после выполнения SELECT и перед следующей операцией данные в базе могут измениться.

Это легко продемонстрировать, если добавить задержку между командами. Для разнообразия возьмем немного другой (но тоже неправильный) вариант:

=> CREATE OR REPLACE FUNCTION change(code text, description text) RETURNS void AS $$
BEGIN
    UPDATE categories c
    SET description = change.description
    WHERE c.code = change.code;

    IF NOT found THEN
        PERFORM pg_sleep(1); -- тут может произойти все, что угодно
        INSERT INTO categories VALUES (code, description);
    END IF;
END;
$$ VOLATILE LANGUAGE plpgsql;

Теперь выполним функцию в двух сеансах почти одновременно:

=> SELECT change('games', 'Игры');

=> SELECT change('games', 'Игры');

ERROR:  duplicate key value violates unique constraint "categories_code_idx"
DETAIL:  Key (code)=(games) already exists.
CONTEXT:  SQL statement "INSERT INTO categories VALUES (code, description)"
PL/pgSQL function change(text,text) line 9 at SQL statement

Попробуем решить задачу с помощью обработки ошибки:

=> CREATE OR REPLACE FUNCTION change(code text, description text) RETURNS void AS $$
BEGIN
    LOOP
        UPDATE categories c
        SET description = change.description
        WHERE c.code = change.code;

        EXIT WHEN found;
        PERFORM pg_sleep(1); -- для демонстрации

        BEGIN
            INSERT INTO categories VALUES (code, description);
            EXIT;
        EXCEPTION
            WHEN unique_violation THEN NULL;
        END;
    END LOOP;
END;
$$ VOLATILE LANGUAGE plpgsql;

=> SELECT change('vinyl', 'Грампластинки');

=> SELECT change('vinyl', 'Грампластинки');

=> CREATE OR REPLACE FUNCTION change(code text, description text) RETURNS void AS $$
    INSERT INTO categories VALUES (code, description)
    ON CONFLICT(code)
        DO UPDATE SET description = change.description;
$$ VOLATILE LANGUAGE sql;

Такая команда выполняется атомарно. И снова достаточно простого SQL.

Как не надо: гарантия однопоточности

Задача: гарантировать, что данные обрабатываются одновременно только одним процессом (на уровне изоляции read committed).

Используя ту же таблицу, представим, что периодически категория требует специальной однопоточной обработки. Можно написать функцию следующим образом:

=> CREATE OR REPLACE FUNCTION process_cat(code text) RETURNS text AS $$
BEGIN
    PERFORM c.code FROM categories c WHERE c.code = process_cat.code
        FOR UPDATE NOWAIT;
    PERFORM pg_sleep(1); -- собственно обработка
    RETURN 'Категория обработана';
EXCEPTION
    WHEN lock_not_available THEN
        RETURN 'Другой процесс уже обрабатывает эту категорию';
END;
$$ VOLATILE LANGUAGE plpgsql;

                  process_cat                  
-----------------------------------------------
 Другой процесс уже обрабатывает эту категорию
(1 row)

     process_cat      
----------------------
 Категория обработана
(1 row)

Но и эту задачу можно решить без обработки ошибок, используя рекомендательные блокировки:

=> CREATE OR REPLACE FUNCTION process_cat(code text) RETURNS text AS $$
BEGIN
    IF pg_try_advisory_lock(hashtext(code)) THEN
        PERFORM pg_sleep(1); -- собственно обработка
        RETURN 'Категория обработана';
    ELSE
        RETURN 'Другой процесс уже обрабатывает эту категорию';
    END IF;
END;
$$ VOLATILE LANGUAGE plpgsql;

                  process_cat                  
-----------------------------------------------
 Другой процесс уже обрабатывает эту категорию
(1 row)

     process_cat      
----------------------
 Категория обработана
(1 row)

Накладные расходы

Почему может захотеться использовать не исключения, а альтернативное решение (если оно есть)? Дело в накладных расходах, связанных с установкой неявных точек сохранения и откатом к ним.

Задача: преобразовать текстовые данные в числа там, где это возможно.

Пусть имеется таблица с текстовым полем, в которое пользователи заносят произвольные данные (обычно это признак неудачного дизайна, но иногда приходится). Нам нужно выделить числа в отдельный столбец числового типа.

=> CREATE TABLE data(comment text, n numeric);

=> INSERT INTO data(comment)
SELECT CASE WHEN random() < 0.01 THEN 'не число' --  1%
            ELSE random()::text END              -- 99%
FROM generate_series(1,1000000);

=> CREATE FUNCTION safe_to_numeric_ex(s text) RETURNS numeric AS $$
BEGIN
    RETURN s::numeric;
EXCEPTION
    WHEN invalid_text_representation THEN
        RETURN null;
END
$$ IMMUTABLE LANGUAGE plpgsql;

=> UPDATE data SET n = safe_to_numeric_ex(comment);

=> SELECT count(*) FROM data WHERE n IS NOT NULL;

В другом варианте функции вместо обработки ошибки будем проверять формат с помощью регулярного выражения (слегка упрощенного):

=> CREATE FUNCTION safe_to_numeric_re(s text) RETURNS numeric AS $$
BEGIN
    RETURN CASE WHEN s ~ '^\d+(\.\d+)?$'
        THEN s::numeric
        ELSE NULL
    END;
END
$$ IMMUTABLE LANGUAGE plpgsql;

=> UPDATE data SET n = safe_to_numeric_re(comment);

=> SELECT count(*) FROM data WHERE n IS NOT NULL;

Получается почти в два раза быстрее. В этом примере исключение срабатывало всего в 1% случаев. Чем чаще - тем больше будет дополнительных накладных расходов на откат к точке сохранения.

=> UPDATE data SET comment = 'не число'; -- 100%

=> UPDATE data SET n = safe_to_numeric_ex(comment);

Это не значит, что обработкой ошибок не нужно пользоваться.

Во-первых, язык PL/pgSQL довольно медленный сам по себе из-за интерпретации и постоянного обращения к SQL для вычисления любого выражения.

Во-вторых, очень часто эта скорость вполне удовлетворительна. Да, можно и быстрее, если написать на C, но зачем?

И в-третьих, основные проблемы с производительностью, как правило, связаны со скоростью выполнения запросов из-за неправильно построенных планов, а вовсе не со скоростью работы процедурного кода.

Но если есть альтернативное решение, которое и проще, и быстрее - конечно лучше воспользоваться им.

Как надо: обработка пакета документов

Задача: организовать обработку пакета документов; ошибка при обработке одного документа не должна приводить к общему сбою.

=> CREATE TYPE doc_status AS ENUM ('READY', 'ERROR', 'PROCESSED');

=> CREATE TABLE documents(
    id integer,
    version integer,
    status doc_status,
    message text
);

=> INSERT INTO documents(id, version, status)
    SELECT g.id, 1, 'READY' FROM generate_series(1,100) g(id);

Начнем с функции, обрабатывающей все документы. Она вызывает в цикле обработку одного документа и (для наглядности) возвращает сводную таблицу по итоговым статусам документов после обработки.

=> CREATE FUNCTION process_docs()
RETURNS TABLE(status doc_status, version integer, cnt integer) AS $$
DECLARE
    doc record;
BEGIN
    FOR doc IN (SELECT id FROM documents) LOOP
        PERFORM process_one_doc(doc.id);
    END LOOP;
    RETURN QUERY SELECT d.status, d.version, count(*)::integer
        FROM documents d
        GROUP BY d.status, d.version;
END;
$$ VOLATILE LANGUAGE plpgsql;

Функция, обрабатывающая один документ. Время от времени в ней может возникать ошибка - в таком случае надо откатить все изменения (это выполняется автоматически при перехвате ошибки) и поставить ошибочный статус.

=> CREATE FUNCTION process_one_doc(id integer) RETURNS void AS $$
BEGIN
    UPDATE documents d ----------------------- начало обработки
    SET version = version + 1
    WHERE d.id = process_one_doc.id;

    IF random() < 0.1 THEN
        RAISE EXCEPTION 'Случилось страшное';
    END IF; ----------------------------------- конец обработки

    UPDATE documents d
    SET status = 'PROCESSED'
    WHERE d.id = process_one_doc.id;
EXCEPTION
    WHEN others THEN
        UPDATE documents d
        SET status = 'ERROR', message = sqlerrm
        WHERE d.id = process_one_doc.id;
END;
$$ VOLATILE LANGUAGE plpgsql;

  status   | version | cnt 
-----------+---------+-----
 ERROR     |       1 |  10
 PROCESSED |       2 |  90
(2 rows)

Как видно, часть документов не обработалась, но это не помешало остальным.

В этом примере обойтись без обработки ошибок не получилось бы: если ошибка произойдет и не будет обработана - откатится вся транзакция целиком.

Еще раз обратим внимание, что при возникновении ошибки происходит откат к точке сохранения в начале блока: благодаря этому версии документов в статусе ERROR остались равными 1. Таким образом, хотя в функции нельзя писать команды управления транзакциями (в том числе SAVEPOINT и ROLLBACK TO SAVEPOINT), точку сохранения и откат к ней все-таки можно использовать - неявно.