Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

Транзакции в функциях PL/pgSQl

Posted Август 13th, 2011 by radislav72
in

Выполняются ли функции Postgres целиком в контексте отдельной транзакции?

То-есть, если в функции какой-то запрос/ операция приведет к исключению, то должна ли откатиться функция целиком?

Или каждый запрос в функции выполняется в собственной автономной транзакции? И, соответственно, откатится только ошибочная операция?

Где можно детальней почитать об етом?

‹ Role vs pseudo user (web portal) оптимизация запросов с in ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

> То-есть, если в функции

On Август 13th, 2011 admin says:

> То-есть, если в функции какой-то запрос/ операция приведет к исключению, то должна ли откатиться функция целиком?
Не должна. Функции отдельно - транзакции отдельно. В функции кроме операций над таблицами могут осуществлятся и другие операции, на которые транзакции не распространяются, поэтому и откачены быть не могут. Если хотите, чтобы функция использовала транзакции. начинайте транзакцию в начале функции и завершайте в конце. Нужно только помнить, что вложенные транзакции не допускаются, а также о том, о чём я говорил ранее - не все операторы в PostgreSQL работают транзакционно.

Вот тестовая функция:

On Август 13th, 2011 radislav72 says:

Создал для теста функцию:

declare d double precision;
begin
  INSERT INTO t2 (f1) VALUES (1);
  INSERT INTO t2 (f1) VALUES (2);
  INSERT INTO t2 (f1) VALUES (3);
  d := 5 / 0;
  INSERT INTO t2 (f1) VALUES (4);
  RETURN 1;
end

После вставки 3 записей в таблицу - деление на 0, и, вследствие етого- исключение.
В результате выполнения ф-ции ни одна строка в таблицу не вставляется. Если строку с делением на 0 закомментировать- вставляются в таблицу все 4 строки.

Как ето обьяснить?

В FireBird, помнится мне, все содержимое ф-ции по умолчанию отрабатывалось в отдельной транзакции... Внешне похоже, что и тут так же?

Не вижу функцию

On Август 13th, 2011 admin says:

У вас нет функции в этом примере. Объявление функции начинается с CREATE FUNCTION

Да просто я кусок функции привел

On Август 13th, 2011 radislav72 says:

Я привел только тело функции. Еще раз повторюсь: ето функция, написанная на PL/pgSQL. И поведение такое, как я описал.

И все-таки, что можно сказать

On Август 14th, 2011 radislav72 says:

И все-таки, что можно сказать по поставленному вопросу? Может, где в конфигурации сервера настраивается?

Если нужнен полный код функции:

CREATE FUNCTION "public"."fntrans" (
)
RETURNS integer AS
$body$
declare d double precision;
begin
  INSERT INTO t2 (f1) VALUES (1);
  INSERT INTO t2 (f1) VALUES (2);
  INSERT INTO t2 (f1) VALUES (3);
  d := 5 / 0;
  INSERT INTO t2 (f1) VALUES (4);
  RETURN 1;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Но вряд ли что-то новое можно здесь увидеть...

Вот что нашёл в мануале

On Август 14th, 2011 admin says:

Здесь:
http://postgresql.ru.net/manual/plpgsql-structure.html

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction.

Важно не путать использование BEGIN/END для группировки операторов в PL/pgSQL с такими же командами SQL для управления транзакциями. BEGIN/END в PL/pgSQL только для группировки; они не начинают и не заканчивают транзакцию. Подпрограммы функций и триггеров всегда запускаются внутри транзакции, которая активна во внешнем запросе - они не начинают и не завершают эту транзакцию, поскольку не знают контекста, в котором они запускаются. Однако, блок, содержащий предложение EXCEPTION формирует подтранзакцию, которая может быть отменена без оказания эффекта на внешнюю транзакцию.

Надеюсь я ответил на ваш вопрос?

Спасибо за ответ. Мне важно

On Август 15th, 2011 radislav72 says:

Спасибо за ответ. Мне важно понять принцип действия ф-ций в PostgreSQL.
Приведенный вами фрагмент документации подтверждает раньше написанное вами.

Главый вопрос, который меня интересует: будут ли отменены все прежние действия функции, если в какой-то момент ее работы возникло исключение? Если в функции явно не начинается новой транзакции.

Судя по приведенном вами тексте, указанные действия функции НЕ должны отменятся (Подпрограммы функций и триггеров всегда запускаются внутри транзакции, которая активна во внешнем запросе - они не начинают и не завершают эту транзакцию...)
Но почему тогда функция:

CREATE OR REPLACE FUNCTION "public"."fntrans" (
)
RETURNS integer AS
$body$
declare d double precision;
begin
  INSERT INTO t2 (f1) VALUES (1);
  INSERT INTO t2 (f1) VALUES (2);
  INSERT INTO t2 (f1) VALUES (3);
  raise exception 'Oooooppsss!';
  INSERT INTO t2 (f1) VALUES (4);
  RETURN 1;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

при вызове не вставляет ни одной строки в таблицу?

Даже если я в вызывающей программе начинаю транзакцию непосредственно перед вызовом ф-ции и после етого подтверждаю транзакцию:

    QSqlDatabase::DATABASE().transaction();
    q.prepare("select public.fntrans()");
    q.exec();
    QSqlDatabase::DATABASE().commit();

Если же строку, вызывающую исключение, закомментировать, то конечно же все 4 строки вставляются, как и должно быть.

То-есть, мне удалось добиться лишь два результата выполнения: или исключения нет и ВСЕ действия ф-ции фиксируются, или исключение было и НИ ОДНО действие ф-ции не фиксируется.

Хотелось бы разобраться с таким поведением. Буде благодарен, если еще раз ответите...

На мой взгляд происходит

On Август 15th, 2011 admin says:

На мой взгляд происходит следующее:
1. Вы начинаете транзакцию
2. Внутри неё вызываете функцию
3. До момента исключения транзакция незавершена
4. Исключение необработано, поэтому когда оно случается оно аварийно завершает функцию, что в свою очередь аварийно завершает внутри транзакции внешний запрос
5. Аварийно завершившийся запрос мешает завершить транзакцию и происходит её откат

Судя по тому же куску перевод которого я привёл, если вы обработаете исключение так, что ваша функция не завершится аварийно, то всё должно сработать:
...Однако, блок, содержащий предложение EXCEPTION формирует подтранзакцию, которая может быть отменена без оказания эффекта на внешнюю транзакцию...
разумеется это необходимо проверить, но если следовать документации - это так

Изменил тестовую функцию.

On Август 16th, 2011 radislav72 says:

Изменил тестовую функцию. Теперь она такая:

CREATE OR REPLACE FUNCTION "public"."fntrans" (
)
RETURNS integer AS
$body$
declare d double precision;
BEGIN
  INSERT INTO t2 (f1) VALUES (1);
  INSERT INTO t2 (f1) VALUES (2);
  INSERT INTO t2 (f1) VALUES (3);
  raise exception 'Oooooppsss!';
  INSERT INTO t2 (f1) VALUES (4);
  RETURN 1;
EXCEPTION  
  WHEN OTHERS THEN RETURN 0;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Но поведение при работе не изменилось: или срабатывает исключение и ВСЕ действия ф-ции отменяются, или (если закомментировать строку с исключением), ВСЕ действия функции фиксируются.

Честно говоря, по логике (мне так кажется) так и должно быть. По крайней мере, в FireBird, Interbase и MS SQL Server так хранимые процедуры и работают...

А мне наоборот кажется что нелогично

On Август 16th, 2011 admin says:

Получается, что функция как бы транзакция в транзакции, и не очень-то это хорошо, хотя бы потому что это неочевидно!
Хотя что называется, почувствуйте разницу:
1. Берём вариант функции БЕЗ обработки исключения:

CREATE OR REPLACE FUNCTION "public"."fntrans" (
)
RETURNS integer AS
$body$
declare d double precision;
BEGIN
  INSERT INTO t2 (f1) VALUES (1);
  INSERT INTO t2 (f1) VALUES (2);
  INSERT INTO t2 (f1) VALUES (3);
  raise exception 'Oooooppsss!';
  INSERT INTO t2 (f1) VALUES (4);
  RETURN 1;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

$tmp=# BEGIN;
BEGIN
tmp=# INSERT INTO t2 (f1) VALUES (5);
INSERT 0 1
tmp=# SELECT fntrans();
ERROR:  Oooooppsss!
tmp=# COMMIT;
ROLLBACK
tmp=# select * from t2;
 f1 
----
(0 rows)

Т.е. после COMMIT, выполнился ROLLBACK и INSERT который был вызван внутри транзакции перед функцией не выполнился.

2. Берём вариант функции C обработкой исключения:

CREATE OR REPLACE FUNCTION "public"."fntrans" (
)
RETURNS integer AS
$body$
declare d double precision;
BEGIN
  INSERT INTO t2 (f1) VALUES (1);
  INSERT INTO t2 (f1) VALUES (2);
  INSERT INTO t2 (f1) VALUES (3);
  raise exception 'Oooooppsss!';
  INSERT INTO t2 (f1) VALUES (4);
  RETURN 1;
EXCEPTION  
  WHEN OTHERS THEN RETURN 0;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

tmp=# BEGIN;
BEGIN
tmp=# INSERT INTO t2 (f1) VALUES (5);
INSERT 0 1
tmp=# SELECT fntrans();
fntrans
---------
0
(1 row)
tmp=# COMMIT;
COMMIT
tmp=# select * from t2;
 f1 
----
  5
(1 row)

Т.е. хотя всё что внутри функции не сработало, но INSERT, выполненный до вызова функции, данные в таблицу занёс!

Спасибо, интаресный нюанс. Я

On Август 26th, 2011 radislav72 says:

Спасибо, интаресный нюанс. Я на ето не обратил внимания.

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023