PostgreSQL忘记输入where条件update更新整张表的解决办法

Postgre SQL 2020-06-23

虽然出现这个错误很挫，但有时候还是会被你或者你的同事碰到。为了避免这个错误，Postgresql 数据库中可以通过触发器来解决，这里用的是plpgsql 。

1、修改postgresql.conf配置

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

2、创建触发器函数

CREATEORREPLACEFUNCTIONzero_counter()
RETURNSTRIGGER
LANGUAGEplpgsql
AS
$BODY$
DECLARE
BEGIN
PERFORMset_config('limits.test','0',true);
IFTG_OP='UPDATE'THEN
RETURNNEW;
ENDIF;
RETURNOLD;
END;
$BODY$;

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、测试

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

forummon=#updatetestsetpass='qq'whereid<5003;
ERROR:UPDATEofmorethan5000rowisforbidden.
forummon=#updatetestsetpass='qq'whereid<5000;
UPDATE4999
forummon=#droptabletest;
DROPTABLE