欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-27 15:10:32
结束进程两种方式:select pg_cancel_backend(pid)取消后台操作,回滚未提交事物 (select);select pg_terminate_backend(pid)中断sess...

结束进程两种方式:

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.

所以,如果你需要,你可以捕获理论上可能发生的异常(但是不大可能).

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

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。

相关标签: postgresql SQL