2019獨角獸企業重金招聘Python工程師標準>>>

雖然出現這個錯誤很挫，但有時候還是會被你或者你的同事碰到。為了避免這個錯誤，PostgreSQL數據庫中可以通過觸發器來解決，這里用的是plpgsql 。

1、修改postgresql.conf配置

增加：custom_variable_classes = 'limits'
重新加載：pg_ctl reload

2、創建觸發器函數

CREATE?OR?REPLACE?FUNCTION?zero_counter()RETURNS?TRIGGERLANGUAGE?plpgsqlAS $BODY$ DECLARE BEGINPERFORM?set_config('limits.test',?'0',?true);IF?TG_OP?=?'UPDATE'?THENRETURN?NEW;END?IF;RETURN?OLD; END; $BODY$; CREATE?OR?REPLACE?FUNCTION?limit_modifications()RETURNS?TRIGGERLANGUAGE?plpgsqlAS $BODY$ DECLAREi?INT4; BEGINi?:=?current_setting('limits.test')::INT4?+?1;PERFORM?set_config('limits.test',?i::TEXT,?true);IF?i?>?5000?THENRAISE?EXCEPTION?'%?of?more?than?5000?row?is?forbidden.',?TG_OP;END?IF;IF?TG_OP?=?'UPDATE'?THENRETURN?NEW;END?IF;RETURN?OLD; END; $BODY$;

3、測試

forummon=#?create?table?test?as?select?i?as?id,?'password?for:?'?||?i?as?pass?from?generate_series(1,10000)?i; forummon=#?CREATE?TRIGGER?zero_counter?BEFORE?UPDATE?OR?DELETE?ON?test?FOR?EACH?STATEMENT?EXECUTE?PROCEDURE?zero_counter(); CREATE?TRIGGER forummon=#?CREATE?TRIGGER?limit_modifications?BEFORE?UPDATE?OR?DELETE?ON?test?FOR?EACH?ROW?EXECUTE?PROCEDURE?limit_modifications(); CREATE?TRIGGER forummon=#?\d+?test?Table?"public.test"Column?|?Type?|?Modifiers?|?Storage?|?Stats?target?|?Description? --------+---------+-----------+----------+--------------+-------------id?|?integer?|?|?plain?|?|?pass?|?text?|?|?extended?|?|? Triggers:limit_modifications?BEFORE?DELETE?OR?UPDATE?ON?test?FOR?EACH?ROW?EXECUTE?PROCEDURE?limit_modifications()zero_counter?BEFORE?DELETE?OR?UPDATE?ON?test?FOR?EACH?STATEMENT?EXECUTE?PROCEDURE?zero_counter() Has?OIDs:?no forummon=#?update?test?set?pass?=?'qq'?where?id<5003?; ERROR:?UPDATE?of?more?than?5000?row?is?forbidden. forummon=#?update?test?set?pass?=?'qq'?where?id<5000?; UPDATE?4999 forummon=#?drop?table?test?; DROP?TABLE

總結

批量操作的數量限制可以在limit_modifications中修改，切記在update不確定時先select再update。

參考連接：http://www.depesz.com/2007/07/27/update-account-set-password-new_password-oops/

原文地址：http://www.sijitao.net/1996.html

轉載于:https://my.oschina.net/u/270810/blog/502000

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的PostgreSQL忘记输入where条件update更新整张表的解决办法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。