транзакции в хранимых процедурах

pmn
Дата: 06.10.2004 19:19:54
PLSQL стал осваивать недавно, поэтому прошу не ругаться.

создал следующую хранимую процедуру:

CREATE FUNCTION modify_client(int4, varchar, int4, bool, timestamptz) RETURNS oid AS '
DECLARE
row_exists RECORD;
BEGIN
-- BEGIN WORK;
-- LOCK TABLE clients IN SHARE MODE;
SELECT INTO row_exists oid, * FROM clients WHERE code = $1 AND close = '' 1970-01-01'';
IF NOT FOUND THEN
INSERT INTO clients (code, name, credit, open, close)
VALUES ($1, $2, $3, $5, '' 1970-01-01 00:00:00 '');
SELECT INTO row_exists oid, * FROM clients WHERE code = $1 AND close = '' 1970-01-01'';
IF NOT FOUND THEN
RAISE EXCEPTION '' not inserted '';
ELSE
RETURN row_exists.oid;
END IF;
ELSE
IF (row_exists.name != $2 AND $2 != '''') OR (row_exists.credit != $3 AND $3 >= 0) THEN
UPDATE clients SET CLOSE = $5 WHERE oid = row_exists.oid;
IF $4 = FALSE THEN
IF ($2 != '''') THEN
row_exists.name = $2;
END IF;
IF ($3 > 0) THEN
row_exists.credit = $3;
END IF;
INSERT INTO clients (code, name, credit, open, close)
VALUES ($1, row_exists.name, row_exists.credit, $5, '' 1970-01-01 00:00:00 '');
SELECT INTO row_exists oid, * FROM clients WHERE code = $1 AND close = '' 1970-01-01'';
END IF;
END IF;
END IF;

-- COMMIT WORK;

RETURN row_exists.oid;
END
' LANGUAGE 'plpgsql';

Она должна править строку в таблице и при необходимости создавать новую, в зависимости от аргумента типа bool. Работает нормально для одного соединения, но в разных соединениях иногда неправильно из за параллельной работы. Знаю, что надо использовать транзакцию, но как ? Сделал, как описано в документации, игрался с блокировками, но выдается ошибка при компиляции. Что в теле хранимой процедуры так нельзя писать и можно ли достичь желаемого результата, так чтобы весь код содержался внутри процедуры ?
Буду премного благодарен за совет.
gamych
Дата: 06.10.2004 23:35:01
Во-первых, насколько я знаю (неплохо бы мне ошибиться:( ), вызов хранимой процедуры - сам по себе транзакция, а вложенных транзакций эхотаг пока не позволяет. Во вторых, вместо
pmn

SELECT INTO row_exists oid, * FROM clients WHERE code = $1 AND close = '' 1970-01-01'';

рекомендую использовать код
get diagnostics ins_oid=RESULT_OID;
Тогда в переменной ins_oid гарантировано окажется oid записи, вставленной из текущего соединения. В третьих, таблицу можно залочить на время вставки. Насколько мне известно, блокировка таблицы(команда LOCK) - атомарная операция.
pmn
Дата: 07.10.2004 10:16:41
Все, спасибо, разобрался. Действительно все дело было в блокировке. Просто в прошлый раз в синтаксисе ошибся.
pmn
Дата: 07.10.2004 16:04:14
Нет, рано я порадовался, стресс тест показал, что некорректность осталась.
В последний раз я использовал такую строку :
LOCK TABLE clients IN EXCLUSIVE ACCESS MODE, там где у меня коментарии.
Как я понимаю, если таблица заблокирована эсклюзивно процедурой в другом процессе(соединении), то и все SELECTы встанут в ожидание в конкурирующем процессе. То есть до тех пор пока транзакция не закончится, другой процесс так и будет висеть. А заканчивается она только если в процедуре произойдет COMMIT, либо он автоматически произойдет при возврате из функции. Вроде так, вроде должно работать, но не работает. :(
gamych
Дата: 07.10.2004 23:41:31
Что именно не работает? Опиши трабл подробнее.
pmn
Дата: 08.10.2004 12:11:18
Значит так.
Теперь мне более менее ясно, из за чего ошибка.
В процедуре у меня сначала идет сверка на наличие строки в таблице, если строки с данным идентификатором code, то вставляется новая строка, если есть, то она редактируется, и в зависимости от флага тоже добавляется строка. Делается это для создания истории изменений, на самом деле это будет использовано еще для многих других таблиц. Если не запретить одновременный доступ к таблице из разных соединений, то может произойти следующее. В процедуре одновременно проверяется наличие строки, и обе считают, что такой нет, и добавляют новую, в результате получаются ненужный дубликат, и вообще после этого весь алгоритм идет не так как надо, процедура идет на выход. Вообще я нашел путь, в общем то сделал как написано в мануале, стал вызывать не просто процедуру, а поместил ее в транзакцию:
begin;
lock table clients in ACCESS EXCLUSIVE MODE;
select modify_client(3,'pupkin',10000,FALSE,'2004-10-08 11:54:42');
commit;

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