postgresql 11 的 procedure 里使用 commit、rollback

os: centos 7.4
db: postgresql 11.5

postgresql 11 之前的版本，function 和 procedure 是一回事，但是从 11 开始就不一样了。

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# su - postgres
Last login: Sat Oct 26 22:55:25 CST 2019 on pts/0
$
$ psql -c "select version();"version                                                 
---------------------------------------------------------------------------------------------------------PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

procedure commmit

# su - postgres
$ psql
psql (11.5)
Type "help" for help.postgres=# 
postgres=# postgres=# \h create procedure
Command:     CREATE PROCEDURE
Description: define a new procedure
Syntax:
CREATE [ OR REPLACE ] PROCEDUREname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ){ LANGUAGE lang_name| TRANSFORM { FOR TYPE type_name } [, ... ]| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER| SET configuration_parameter { TO value | = value | FROM CURRENT }| AS 'definition'| AS 'obj_file', 'link_symbol'} ...

创建个批量插入函数

postgres=# create table tmp_t0 (id   int8,name varchar(100));postgres=# 
postgres=# create or replace procedure sp_insert(in_lv_cnt       int8,in_lv_batchsize int8
)
language plpgsql
as $$declarelv_i int8;beginlv_i := 0;while lv_i < in_lv_cntloopinsert into tmp_t0 select id,md5(id::varchar) as namefrom generate_series(1, in_lv_batchsize) as id;lv_i := lv_i + 1;raise notice 'inserted % batch', lv_i;commit;end loop;return;end;
$$;

调用 procedure，发现使用 select 方式已经不行了，需要使用 call 。

postgres=# select sp_insert(10,100);
ERROR:  sp_insert(integer, integer) is a procedure
LINE 1: select sp_insert(10,100);^
HINT:  To call a procedure, use CALL.postgres=# 
postgres=# 
postgres=# call sp_insert(10,100);
NOTICE:  inserted 1 batch
NOTICE:  inserted 2 batch
NOTICE:  inserted 3 batch
NOTICE:  inserted 4 batch
NOTICE:  inserted 5 batch
NOTICE:  inserted 6 batch
NOTICE:  inserted 7 batch
NOTICE:  inserted 8 batch
NOTICE:  inserted 9 batch
NOTICE:  inserted 10 batch
CALL

查看 插入的数据量

postgres=# select count(1) from tmp_t0;count 
-------1000
(1 row)

procedure rollback

包含个 rollback 试试

postgres=# truncate table tmp_t0;
postgres=# 
postgres=# create or replace procedure sp_insert_rollback(in_lv_cnt       int8,in_lv_batchsize int8
)
language plpgsql
as $$declarelv_i int8;beginlv_i := 0;while lv_i < in_lv_cntloopinsert into tmp_t0 select id,md5(id::varchar) as namefrom generate_series(1, in_lv_batchsize) as id;if (lv_i=5) thenraise notice 'rollback inserted % batch', lv_i;rollback;elseraise notice 'inserted % batch', lv_i;commit;end if;lv_i := lv_i + 1;end loop;return;end;
$$;

调用 procedure，发现使用 select 方式已经不行了，需要使用 call 。

postgres=# call sp_insert_rollback(10,100);
NOTICE:  inserted 0 batch
NOTICE:  inserted 1 batch
NOTICE:  inserted 2 batch
NOTICE:  inserted 3 batch
NOTICE:  inserted 4 batch
NOTICE:  rollback inserted 5 batch
NOTICE:  inserted 6 batch
NOTICE:  inserted 7 batch
NOTICE:  inserted 8 batch
NOTICE:  inserted 9 batch
CALL

查看 插入的数据量

postgres=# select count(1) from tmp_t0;count 
-------900
(1 row)

nice

参考：
http://postgres.cn/docs/11/sql-createprocedure.html

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！