我正在寻求澄清如何确保 plpgsql 函数中的原子事务，以及为数据库的此特定更改设置隔离级别的位置。
在下面显示的 plpgsql 函数中，我想确保删除和插入都成功。当我尝试将它们包装在单个事务中时出现错误:
ERROR: cannot begin/end transactions in PL/pgSQL
如果另一个用户在此函数删除自定义行之后但在它有机会插入自定义行之前添加了针对情况(“RAIN”、“NIGHT”、“45MPH”)的默认行为，则在执行下面的函数期间会发生什么排？是否有一个包含插入和删除的隐式事务，以便在另一个用户更改了此函数引用的行中的任何一行时都将回滚？我可以为此功能设置隔离级别吗？
create function foo(v_weather varchar(10), v_timeofday varchar(10), v_speed varchar(10),
v_behavior varchar(10))
returns setof CUSTOMBEHAVIOR
as $body$
begin
-- run-time error if either of these lines is un-commented
-- start transaction ISOLATION LEVEL READ COMMITTED;
-- or, alternatively, set transaction ISOLATION LEVEL READ COMMITTED;
delete from CUSTOMBEHAVIOR
where weather = 'RAIN' and timeofday = 'NIGHT' and speed= '45MPH' ;
-- if there is no default behavior insert a custom behavior
if not exists
(select id from DEFAULTBEHAVIOR where a = 'RAIN' and b = 'NIGHT' and c= '45MPH') then
insert into CUSTOMBEHAVIOR
(weather, timeofday, speed, behavior)
values
(v_weather, v_timeofday, v_speed, v_behavior);
end if;
return QUERY
select * from CUSTOMBEHAVIOR where ... ;
-- commit;
end
$body$ LANGUAGE plpgsql;
请您参考如下方法:
一个plpgsql 功能自动在事务内运行。要么全部成功，要么全部失败。 The manual:
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
EXCEPTIONclause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. For more about that see Section 42.6.6.
因此，如果需要，您可以捕获理论上可能发生的异常(但可能性很小)。
Details on trapping errors in the manual.
您的功能经过审查和简化:
CREATE FUNCTION foo(v_weather text
, v_timeofday text
, v_speed text
, v_behavior text)
RETURNS SETOF custombehavior
LANGUAGE plpgsql AS
$func$
BEGIN
DELETE FROM custombehavior
WHERE weather = 'RAIN'
AND timeofday = 'NIGHT'
AND speed = '45MPH';
INSERT INTO custombehavior (weather, timeofday, speed, behavior)
SELECT v_weather, v_timeofday, v_speed, v_behavior
WHERE NOT EXISTS (
SELECT FROM defaultbehavior
WHERE a = 'RAIN'
AND b = 'NIGHT'
AND c = '45MPH'
);
RETURN QUERY
SELECT * FROM custombehavior WHERE ... ;
END
$func$;
如果您确实需要 开始/结束交易 就像标题中指示的那样查看 SQL 程序在 Postgres 11 或更高版本 (
CREATE PROCEDURE ) 中。看:
