GreenPlum 实现伪自治事务

自治事务

• 主事务的提交和回滚与⼦事务的提交和回滚互不影响；
• 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$
/* * 作者：v-yuzhenc * 功能：执行动态sql，并且该执行过程是自治的 * 相当于另外开了一个sql串窗口执行 * vsql：执行的动态sql * retrytimes：拿不到连接时拿连接重试次数 * */
declare 
	v_sql varchar := vsql;  --动态sql
	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
		--获取dblink连接
		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;
			--睡眠1s再拿连接
			perform pg_sleep(1);
			continue;
	end;
	end loop;

	--执行动态sql语句
	p_result := dblink_exec(p_session_name,v_sql);
	--关闭dblink连接
	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;

• 自治插入成功，普通插入失败