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

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

2021-03-03 18:26hy.ding PostgreSQL

这篇文章主要介绍了在postgresql中结束掉正在执行的SQL语句操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

结束进程两种方式：

1	`SELECT` `pg_cancel_backend(PID)`

取消后台操作，回滚未提交事物 (select);

1	`SELECT` `pg_terminate_backend(PID)`

中断session，回滚未提交事物(select、update、delete、drop);

1	`SELECT` `*` `FROM` `pg_stat_activity;`

根据datid=10841

1	`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