正在执行的SQL语句如何在postgresql中结束-创新互联
本篇文章为大家展示了正在执行的SQL语句如何在postgresql中结束,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。
目前创新互联已为成百上千家的企业提供了网站建设、域名、网站空间、网站托管维护、企业网站设计、浈江网站维护等服务,公司将坚持客户导向、应用为本的策略,正道将秉承"和谐、参与、激情"的文化,与客户和合作伙伴齐心协力一起成长,共同发展。结束进程两种方式:
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函数在事务中自动运行.这一切都成功了,一切都失败了.
我引用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.
所以,如果你需要,你可以捕获理论上可能发生的异常(但是不大可能).
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 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
上述内容就是正在执行的SQL语句如何在postgresql中结束,你们学到知识或技能了吗?如果还想学到更多技能或者丰富自己的知识储备,欢迎关注创新互联行业资讯频道。
分享名称:正在执行的SQL语句如何在postgresql中结束-创新互联
链接分享:http://myzitong.com/article/csdesh.html