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

发表时间:2015-05-15 11:19 | 分类:PostgreSQL | 浏览:1,723 次

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

1、修改postgresql.conf配置

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

2、创建触发器函数

CREATE OR REPLACE FUNCTION zero_counter()
 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/

本文标签:

本文链接:http://www.sijitao.net/1996.html

欢迎您在本博客中留下评论,如需转载原创文章请注明出处,谢谢!

已经有8个回复
Comment (8)
Trackbacks (0)
  1. 网上兼职网站  ( 2015.05.19 15:11 ) : #-9

    不错的分享 赞

  2. 久闻网佳莉  ( 2015.05.20 16:19 ) : #-8

    学习 :|

  3. 波兰移民条件  ( 2015.05.23 16:07 ) : #-7

    两年没逛逛博客了,过来支持一下坚持的兄弟们

  4. http://www.vprol.com/daohang  ( 2015.05.30 16:01 ) : #-6

    技术性很强的博客,大部分文章看完不一定会记住,不过很多很有用。。。来访哦,来访首页展示贵站哦

  5. 微部落博客  ( 2015.05.30 16:02 ) : #-5

    刚才资料填错了,重新评论,呵呵。。。技术性很强的博客,大部分文章看完不一定会记住,不过很多很有用。。。来访哦,来访首页展示贵站哦

    • 章郎虫  ( 2015.06.2 09:17 ) :

      贵站很不错,会常去看看学习下。

  6. 极品飞鸽  ( 2015.05.30 19:04 ) : #-4

    NoSQL牛掰啊,俺们还在oracle、sqlserver、mysql玩儿呢

  7. 火锅桌椅  ( 2015.06.5 10:58 ) : #-3

    技术性的东西,像我这种菜鸟估计是要把它收藏起来研究半个月也不一定得到的结果。

  1. 还没有Trackbacks
博客历程 留言本 文章归档 网站地图 谷歌地图 浙ICP备13025236号-1
托管于阿里云&七牛云 已加入博客联盟 浙公网安备 33021202000610号
Copyright © 2010-2017 虫虫开源 All Rights Reserved.