1. 云图网首页
  2. 技术专区
  3. 数据库

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

• 数据库

近期有个生产库的大表需要全表更新一个字段，并且这张表为核心表,访问非常频繁, 考虑到一个 update 语句可能执行时间很长，会锁住其它更新会话，从而对业务产生较大影响，于是考虑到写个 function 来小批量提交，这样可以大大减轻对业务的影响。

但在这里遇到了问题，原本想每 10000 条更新提交一下，但 在 function 里输入 commit 时， 这个function 会立即提交并返回，后来想了会，有其它的办法实现批量提交。以下为详细刷新数据步骤。

目标表表结构

此表为大表，且访问频繁，表结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
 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。

创建临时表

1
2
3
4
 
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’ 表示更新成功。

创建数据刷新函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
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;

批量执行函数脚本

修改 batch_update.sql ,输入以下内容：

1
2
3
4
5
6
7
8
9
10
 
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();” 可以根据数据量估算下就行。

执行脚本

脚本自己组织下就行，例如：

1
 
psql -h ip_addr -d dbnmae -U rolename -fbatch_update.sql >batch_update.out

总结

虽然 一个 function 里不能实现批量提交，但是可以逻辑分为多个 function 串行执行，从而实现小批量提交！

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/236395.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论