Поддерживает ли Postgres вложенные или автономные транзакции?

У меня есть ситуация, когда я должен совершить часть кода как собственную транзакцию.
Я создал таблицу subtransaction_tbl:

CREATE TABLE subtransaction_tbl
(
  entryval integer
)

И функция на языке plpython3u:

CREATE FUNCTION subtransaction_nested_test_t() RETURNS void
AS $$
plpy.execute("INSERT INTO subtransaction_tbl VALUES (1)")
with plpy.subtransaction():
    plpy.execute("INSERT INTO subtransaction_tbl VALUES (2)")
$$ LANGUAGE plpython3u;

Первая ситуация:

BEGIN TRANSACTION;
INSERT INTO subtransaction_tbl VALUES (4);
select  subtransaction_nested_test_t();
COMMIT TRANSACTION;

Записи в таблице верны: 1,2,4

Вторая ситуация:

BEGIN TRANSACTION;
INSERT INTO subtransaction_tbl VALUES (4);
select  subtransaction_nested_test_t();
ROLLBACK TRANSACTION;

Значения в таблице не заполняются

Я ожидал, что 1 или 2 следует добавить в таблицу subtransaction_tbl, но, к моему удивлению, значение не было вставлено. Я представил себе, что функция открыла новое подтранзакцию, и она не должна зависеть от родительской транзакции. Пожалуйста, дайте мне знать, прав я или нет.

Существуют ли автономные транзакции в Postgres? Или мне нужно изменить функцию plpython3u?

Ответ 1

В Postgres нет автономных транзакций до Postgres 11, где были добавлены процедуры SQL. Все, что сделано в функции, откатывается с транзакцией.

Вот обсуждение этой функции:

http://wiki.postgresql.org/wiki/Autonomous_subtransactions

В Postgres 10 или старше можно было бы обойти (ab-) использование dblink:

Существует также связанная концепция SAVEPOINT. (Не то же самое!)

plpython

plpython имеет subtransactions (with plpy.subtransaction():), но это не то же самое, как автономные транзакции. Нет отдельного COMMIT. Все, что он делает, это объединяет пару утверждений, чтобы сделать их атомарными. Без этого, если исключение возникает где-то посередине, и вы перехватываете это исключение, будет выполняться только код до этого исключения. Если вы заключите это в субтранзакцию, это все или ничего. Это похоже на использование SAVEPOINT, а не автономной транзакции. По документации:

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

Ответ 2

Postgres поддерживает вложенные транзакции, но они отличаются от обычного SQL, больше похожи на транзакции с вложенными частичными точками.

На верхнем уровне у вас всегда есть типичные BEGIN/COMMIT/ROLLBACK, а на вложенных уровнях вы должны использовать следующие команды:

SAVEPOINT name - создает новую точку сохранения с уникальным именем для транзакции
RELEASE SAVEPOINT name - фиксирует точку сохранения, хотя она будет сохраняться, только если содержащая транзакция фиксирует
ROLLBACK TO SAVEPOINT name - откатывает точку сохранения

Вы также должны убедиться, что:

Имена, используемые для каждого SAVEPOINT, уникальны;
Отказ в одном SAVEPOINT распространяется вверх на верхний уровень.

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

Когда я написал pg-обещание, я убедился, что эти два положения гарантированы:

Он автоматически генерирует имена точек сохранения, такие как level_1, level_2 и т.д., В зависимости от уровня транзакции;
Выполняется с ROLLBACK TO SAVEPOINT name плюс ROLLBACK верхнего уровня в случае сбоя дочерней транзакции - все построено на стандартной логике цепочки обещаний.

См. Также объяснения ограничений вложенных транзакций PostgreSQL...