Установить уровень изоляции для хранимых процедур postgresql

Надеюсь, простой вопрос, но тот, за который я не нашел подходящего ответа. Я уверенно информирован о том, что хранимые процедуры (пользовательские функции DB) в PostgreSQL (в частности, версия 9.0.4) по сути являются транзакционными, поскольку они вызываются с помощью инструкции SELECT, которая сама является транзакцией. Итак, как выбрать уровень изоляции хранимой процедуры? Я верю, что в других СУБД желаемый блок транзакций будет завернут в блок START TRANSACTION, для которого желаемый уровень изоляции является необязательным параметром.

Как конкретный подготовленный пример, скажем, я хочу сделать это:

CREATE FUNCTION add_new_row(rowtext TEXT)
RETURNS VOID AS 
$$
BEGIN
        INSERT INTO data_table VALUES (rowtext);
        UPDATE row_counts_table SET count=count+1;
END;
$$  
LANGUAGE plpgsql
SECURITY DEFINER;

И представьте, что я хочу убедиться, что эта функция всегда выполняется как сериализуемая транзакция (да, да, PostgreSQL SERIALIZABLE не является надлежащим сериализуемым, но это не так). Я не хочу, чтобы он назывался

START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT add_new_row('foo');
COMMIT;

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

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

Самый очевидный подход ко мне - использовать SET TRANSACTION где-нибудь в определении функции, например:

CREATE FUNCTION add_new_row(rowtext TEXT)
RETURNS VOID AS 
$$
BEGIN
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        INSERT INTO data_table VALUES (rowtext);
        UPDATE row_counts_table SET count=count+1;
END;
$$  
LANGUAGE plpgsql
SECURITY DEFINER;

Хотя это будет принято, это не ясно, чем я могу положиться на это, чтобы работать. документация для SET TRANSACTION говорит

Если SET TRANSACTION выполняется без предварительная СТАВКА НАЧАЛА или НАЧАТЬ, это будет казаться недействительным, поскольку транзакция будет немедленно завершена.

Что оставляет меня озадаченным, так как если я назову одиночный оператор SELECT add_new_row('foo');, который я ожидал бы (если бы я не отключил autocommit), SELECT будет выполняться как однострочная транзакция с уровнем изоляции по умолчанию для сеанса.

В справочнике также говорится:

Уровень изоляции транзакции не может после первого запроса или выражение о модификации данных (SELECT, INSERT, DELETE, UPDATE, FETCH или COPY) транзакции выполняется.

Итак, что происходит, если функция вызывается из транзакции с более низким уровнем изоляции, например,:

START TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE row_counts_table SET count=0;
SELECT add_new_row('foo');
COMMIT;

За бонусный вопрос: имеет ли язык функции какой-либо разницы? Можно ли установить уровень изоляции по-разному в PL/pgSQL, чем в обычном SQL?

Я поклонник стандартов и документированных лучших практик, поэтому любые достойные ссылки будут оценены.

Ответ 1

Вы не можете этого сделать.

Что вы можете сделать, так это проверить свою работоспособность на то, что текущий уровень изоляции транзакции, и отменить, если он не тот, который вам нужен. Вы можете сделать это, запустив SELECT current_setting('transaction_isolation'), а затем проверив результат.

Ответ 2

Язык функции не имеет никакого значения.

Это не удается:

test=# create function test() returns int as $$
  set transaction isolation level serializable;
  select 1;
$$ language sql;
CREATE FUNCTION
test=# select test();
ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL function "test" statement 1

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

Я поклонник стандартов

PL/языки специфичны для платформы.

Ответ 3

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

Если вы хотите сериализовать выполнение, вам нужно использовать блокировки.

Вы можете использовать триггер строки и количество обновлений. "UPDATE row_counts_table" заблокирует таблицу, и все транзакции будут сериализованы. Он медленный.

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

Ответ 4

В PG ваши процедуры не являются отдельными транзакциями. Это хранимая процедура принимает участие в существующей транзакции.

BEGIN TRAN

SELECT 1;
SELECT my_proc(99);

ROLLBACK TRAN;

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

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