注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

关于 PostgreSQL 的 function 里不能小批量提交  

2011-05-11 13:24:38|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

            
         近期有个生产库的大表需要全表更新一个字段,并且这张表为核心表,访问非常频繁,
考虑到一个 update 语句可能执行时间很长,会锁住其它更新会话,从而对业务产生
较大影响,于是考虑到写个 function 来小批量提交,这样可以大大减轻对业务的影响。
   
        但在这里遇到了问题,原本想每 10000 条更新提交一下,但 在 function 里
输入 commit 时, 这个function 会立即提交并返回,后来想了会,有其它的办法实现
批量提交。以下为详细刷新数据步骤。

 
--1 目标表,大表,访问频繁,
                            Table "skytf.tmp_basic_info"
    Column    |              Type              |                Modifiers               
--------------+--------------------------------+-----------------------------------------
 user_id      | integer                        | not null
 account      | character varying(32)          | not null
 nickname     | character varying(64)          |
 comlum1          | smallint                       | default 1
 comlum2     | character varying(20)          | default '1991-01-01'::character varying
 comlum3        | character varying(20)          |
 comlum4       | character varying(40)          |
 comlum5       | character varying(20)          |
 comlum6      | character varying(32)          | default '中国'::character varying
 comlum7     | character varying(20)          |
 comlum8         | character varying(16)          |
 comlum9    | character varying(128)         |
 comlum10    | integer                        | default 0
 comlum11        | integer                        | default 0
 comlum12     | timestamp(0) without time zone |
 comlum13 | integer                        | default 86
Indexes:
    "tmp_basic_info_pkey" PRIMARY KEY, btree (user_id)
    "tbl_mpc_user_info_username_key" UNIQUE, btree (account)
    "tmp_basic_info_comlum2" btree (comlum2)
    "tmp_basic_info_comlum8_index" btree (comlum8)
    "tmp_basic_info_nickname_index" btree (nickname)
    "tmp_basic_info_comlum7_index" btree (comlum7)
    "tmp_basic_info_comlum12_idx" btree (comlum12)
    "tmp_basic_info_comlum1_index" btree (comlum1, comlum8, comlum2, comlum6, comlum4)
    "tmp_basic_info_comlum9" btree (comlum9)
    "tmp_basic_info_userid_index" btree (user_id)
   
    由于业务需要,需要将 nickname 的值全表更新成 account。
 
   
--2 创建刷新临时表
create table tmp_nickname as select user_id,account,nickname from tmp_basic_info where nickname like '%蓝雪%';
create unique index idx_user_id on tmp_nickname using btree (user_id);
alter table tmp_nickname add column flag char(1);
update tmp_nickname set flag='N';
   
     flag 为刷新标识字段,'N' 表示为更新失败,'Y' 表示更新成功。


--3 创建刷新数据 function
CREATE OR REPLACE FUNCTION skytf.func_setnickname()
  RETURNS numeric AS
$BODY$
DECLARE
rec RECORD;
i   integer;
BEGIN
 
  i:=0;
 
  FOR rec IN SELECT user_id,account FROM skytf.tmp_nickname where flag ='N'  limit 10000 LOOP
   
     --记录不符合导入条件1的号码
     PERFORM 1 FROM skytf.tmp_basic_info  WHERE user_id= rec.user_id  ;
    
     IF  FOUND THEN
         UPDATE skytf.tmp_basic_info SET  nickname=rec.account WHERE user_id = rec.user_id;
         update tmp_nickname set flag='Y' where user_id=rec.user_id;
     END IF;
    
      i:= i+1;
     
      RAISE NOTICE ' here is %', i; 
     
  END LOOP;
  return 0;
 
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;

 

--4 修改批量执行 function脚本
vi batch_update.sql ,输入以下内容
select skytf.func_setnickname();
select skytf.func_setnickname();
select skytf.func_setnickname();
select skytf.func_setnickname();
select skytf.func_setnickname();
select skytf.func_setnickname();
select skytf.func_setnickname();
select skytf.func_setnickname();
select skytf.func_setnickname();
..........

   备注,函数 skytf.func_setnickname() 每更新10000条提交,这个文件输入多少
   行“select skytf.func_setnickname();” 可以根据数据量估算下就行。
 

--5 后台执行脚本
    脚本自己组织下就行,例如:

psql -h ip_addr -d dbnmae -U rolename -f  batch_update.sql  >  batch_update.out
   
      总结,虽然 一个 function 里不能实现批量提交,但是可以逻辑分为多个 function
串行执行,从而实现小批量提交!

  评论这张
 
阅读(26920)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016