自治事务
- 主事务的提交和回滚与⼦事务的提交和回滚互不影响;
- gp中不存在⾃治事务,并且⼀个函数就是⼀个事务,通过gp的扩展组件dblink实现伪⾃治事务。
创建dblink扩展组件
create extension dblink;
创建序列
drop sequence seq_dblink_sessionid;
create sequence seq_dblink_sessionid
increment by 1
minvalue 1
maxvalue 9999
start 1
cycle;
创建存储过程 sp_dblink_exec
create or replace function sp_dblink_exec(vsql varchar,retrytimes int default 60)
returns text
language plpgsql
security definer
as $function$
declare
v_sql varchar := vsql;
p_retrytimes int := retrytimes;
p_count int := 0;
p_session_id varchar := nextval('seq_dblink_sessionid')::varchar;
p_session_name varchar := 'db_bddj_'||p_session_id;
p_result text;
begin
while true loop
begin
perform dblink_connect(p_session_name,'host=192.168.233.10 port=5432 dbname=etl user=db_ods password=db_odsAa123456');
exit;
exception when others then
if p_count >= p_retrytimes then
exit;
end if;
p_count := p_count + 1;
perform pg_sleep(1);
continue;
end;
end loop;
p_result := dblink_exec(p_session_name,v_sql);
perform dblink_disconnect(p_session_name);
return p_result;
exception when others then
begin
perform dblink_disconnect(p_session_name);
exception when others then
null;
end;
raise exception '%',sqlerrm;
end;
$function$
;
测试
create table tmp (
id numeric
) distributed by (id);
do $$
begin
insert into tmp values (1);
perform sp_dblink_exec('insert into tmp values (2);');
raise exception '手工抛出异常!';
end$$;
- 执行代码块的时候会报错
- 此时我们来看一下,数据有没有插入进去
select * from tmp;