在postgresql中结束掉正在执行的SQL语句操作

在postgresql中结束掉正在执⾏的SQL语句操作
结束进程两种⽅式：
SELECT pg_cancel_backend(PID)
取消后台操作，回滚未提交事物 (select);
SELECT pg_terminate_backend(PID)
中断session，回滚未提交事物(select、update、delete、drop);
SELECT * FROM pg_stat_activity;
根据datid=10841
SELECT pg_terminate_backend (10841);
补充：PostgreSQL⽆法在PL / pgSQL中开始/结束事务
我正在寻求澄清如何确保plpgsql函数中的原⼦事务,以及为数据库进⾏此特定更改设置了隔离级别.
在下⾯显⽰的plpgsql函数中,我想确保BOTH的删除和插⼊成功.当我尝试将它们包装在⼀个事务中时,我收到⼀个错误：
错误：⽆法在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函数在事务中⾃动运⾏.这⼀切都成功了,⼀切都失败了.
我引⽤
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.
所以,如果你需要,你可以捕获理论上可能发⽣的异常(但是不⼤可能).
您的功能审查和简化：
CREATE FUNCTION foo(v_weather text
, v_timeofday text
, v_speed text
, v_behavior text)
RETURNS SETOF custombehavior AS
$body$
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 1 FROM defaultbehavior
WHERE a = 'RAIN'
AND b = 'NIGHT'
AND c = '45MPH'
);
RETURN QUERY
SELECT * FROM custombehavior WHERE ... ;
END
$body$LANGUAGE plpgsql
以上为个⼈经验，希望能给⼤家⼀个参考，也希望⼤家多多⽀持。

如有错误或未考虑完全的地⽅，望不吝赐教。