虽然出现这个错误很挫,但有时候还是会被你或者你的同事碰到。为了避免这个错误,PostgreSQL数据库中可以通过触发器来解决,这里用的是plpgsql 。 1、修改postgresql.conf配置 增加:cus
虽然出现这个错误很挫,但有时候还是会被你或者你的同事碰到。为了避免这个错误,PostgreSQL数据库中可以通过触发器来解决,这里用的是plpgsql 。
1、修改postgresql.conf配置
增加:custom_variable_classes = 'limits'
重新加载:pg_ctl reload
2、创建触发器函数
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$
DECLARE
BEGIN
PERFORM set_config('limits.test', '0', true);
IF TG_OP = 'UPDATE' THEN
RETURN NEW;
END IF;
RETURN OLD;
END;
$BODY$;
CREATE OR REPLACE FUNCTION limit_modifications()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$
DECLARE
i INT4;
BEGIN
i := current_setting('limits.test')::INT4 + 1;
PERFORM set_config('limits.test', i::TEXT, true);
IF i > 5000 THEN
RAISE EXCEPTION '% of more than 5000 row is forbidden.', TG_OP;
END IF;
IF TG_OP = 'UPDATE' THEN
RETURN 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/