結(jié)束進(jìn)程兩種方式：
SELECT pg_cancel_backend(PID)
取消后臺(tái)操作，回滾未提交事物 (select);
SELECT pg_terminate_backend(PID)
中斷session，回滾未提交事物(select、update、delete、drop);
SELECT * FROM pg_stat_activity;
根據(jù)datid=10841
SELECT pg_terminate_backend (10841);
補(bǔ)充：PostgreSQL無法在PL / pgSQL中開始/結(jié)束事務(wù)
我正在尋求澄清如何確保plpgsql函數(shù)中的原子事務(wù),以及為數(shù)據(jù)庫進(jìn)行此特定更改設(shè)置了隔離級(jí)別.
在下面顯示的plpgsql函數(shù)中,我想確保BOTH的刪除和插入成功.當(dāng)我嘗試將它們包裝在一個(gè)事務(wù)中時(shí),我收到一個(gè)錯(cuò)誤：
錯(cuò)誤：無法在PL / pgSQL中開始/結(jié)束事務(wù).
如果另一個(gè)用戶在此功能已刪除自定義記錄之后,但在此函數(shù)有機(jī)會(huì)插入自定義記錄之前,為情況(RAIN,NIGHT,45MPH)添加了默認(rèn)行為,下面的函數(shù)執(zhí)行過程中會(huì)發(fā)生什么？是否有一個(gè)隱式事務(wù)包裝插入和刪除,以便如果另一個(gè)用戶已經(jīng)更改了此函數(shù)引用的任何一個(gè)行,兩者都將回滾？我可以設(shè)置此功能的隔離級(jí)別嗎？
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
一個(gè)plpgsql函數(shù)在事務(wù)中自動(dòng)運(yùn)行.這一切都成功了,一切都失敗了.
我引用the manual on plpgsql functions：
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.
所以,如果你需要,你可以捕獲理論上可能發(fā)生的異常(但是不大可能).
Details on trapping errors in the manual.
您的功能審查和簡(jiǎn)化：
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
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL查看正在執(zhí)行的任務(wù)并強(qiáng)制結(jié)束的操作方法
- 解決PostgreSQL 執(zhí)行超時(shí)的情況
- PostgreSQL 實(shí)現(xiàn)定時(shí)job執(zhí)行(pgAgent)
- 在postgresql中通過命令行執(zhí)行sql文件
- Postgresql的pl/pgql使用操作--將多條執(zhí)行語句作為一個(gè)事務(wù)
- Postgresql psql文件執(zhí)行與批處理多個(gè)sql文件操作
- PostgreSQL 實(shí)現(xiàn)sql放入文件批量執(zhí)行
