虽然出现这个错误很挫，但有时候还是会被你或者你的同事碰到。为了避免这个错误，Postgresql数据库中可以通过触发器来解决，这里用的是plpgsql 。
1、修改postgresql.conf配置
增加：custom_variable_classes = 'limits'
重新加载：pg_ctl reload
2、创建触发器函数
@H_403_13@CREATEORREPLACEFUNCTIONzero_counter() RETURNSTRIGGER LANGUAGEplpgsql AS $BODY$ DECLARE BEGIN PERFORMset_config('limits.test','0',true); IFTG_OP='UPDATE'THEN RETURNNEW; ENDIF; RETURNOLD; END; $BODY$; @H_403_13@CREATEORREPLACEFUNCTIONlimit_modifications() RETURNSTRIGGER LANGUAGEplpgsql AS $BODY$ DECLARE iINT4; BEGIN i:=current_setting('limits.test')::INT4+1; PERFORMset_config('limits.test',i::TEXT,true); IFi>5000THEN RAISEEXCEPTION'%ofmorethan5000rowisforbidden.',TG_OP; ENDIF; IFTG_OP='UPDATE'THEN RETURNNEW; ENDIF; RETURNOLD; END; $BODY$;3、测试
@H_403_13@forummon=#createtabletestasselectiasid,'passwordfor:'||iaspassfromgenerate_series(1,10000)i; forummon=#CREATETRIGGERzero_counterBEFOREUPDATEORDELETEONtestFOREACHSTATEMENTEXECUTEPROCEDUREzero_counter(); CREATETRIGGER forummon=#CREATETRIGGERlimit_modificationsBEFOREUPDATEORDELETEONtestFOREACHROWEXECUTEPROCEDURElimit_modifications(); CREATETRIGGER forummon=#\d+test Table"public.test" Column|Type|Modifiers|Storage|Statstarget|Description --------+---------+-----------+----------+--------------+------------- id|integer||plain|| pass|text||extended|| Triggers: limit_modificationsBEFOREDELETEORUPDATEONtestFOREACHROWEXECUTEPROCEDURElimit_modifications() zero_counterBEFOREDELETEORUPDATEONtestFOREACHSTATEMENTEXECUTEPROCEDUREzero_counter() HasOIDs:no @H_403_13@forummon=#updatetestsetpass='qq'whereid<5003; ERROR:UPDATEofmorethan5000rowisforbidden. forummon=#updatetestsetpass='qq'whereid<5000; UPDATE4999 forummon=#droptabletest; DROPTABLE总结
批量操作的数量限制可以在limit_modifications中修改，切记在update不确定时先select再update。
参考连接：http://www.depesz.com/2007/07/27/update-account-set-password-new_password-oops/
