PostgreSQL create procedure事务提交和回滚

最新推荐文章于 2025-05-16 20:00:36 发布

原创最新推荐文章于 2025-05-16 20:00:36 发布 · 6.7k 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#存储过程 #提交事务 #回滚事务

postgresql 专栏收录该内容

95 篇文章

订阅专栏

博客围绕PostgreSQL存储过程展开，要求在存储过程中处理向有唯一约束的表添加数据的错误。介绍了测试表，探讨在过程中提交或回滚的示例方法，指出错误方法会出现‘子事务处于活动状态时无法提交’问题，给出正确方法，还提及部分提交。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

注意create procedure只有PostgreSQL 服务端大于等于11的版本才支持

需求

我想写个存储过程向表里增加数据表里有唯一约束我想在存储过程中进行错误处理没有错误就提交返回’添加成功’ 有错误就回滚返回’添加失败’.

1 测试表

drop table if exists test;
create table test(
	id integer not null,
	constraint pk_test_id primary key(id)
);

2 在过程中提交或回滚

2.1 示例的方法

你在网上找到的示例大部份是这样

drop procedure if exists test_save();
create or replace procedure test_save()
as $$
	begin		
		insert into test(id) values(1);
		commit;
		insert into test(id) values(2);
		rollback;
	end
$$ language plpgsql;

--只插入了编号为1的数据,编号为2的数据回滚了
call test_save();
select * from test;

2.2 错误的方法

存储过程中注释为调试使用,以后相同
我们的存储过程大约会这么写:

drop procedure if exists test_save(integer,inout boolean);
create or replace procedure test_save(integer,inout v_isok boolean)
as $$
	declare
		v_sqlstate text;
		v_err text;
	begin		
		insert into test(id) values($1);
		commit;
		v_isok := true;
		--raise notice  '提交成功';		
		exception when others then
			rollback;
			v_isok := false;
			/*get stacked diagnostics v_sqlstate = returned_sqlstate,
				v_err = message_text;
			raise notice '错误回滚.%:%',v_sqlstate,v_err;*/
	end
$$ language plpgsql;
--执行两次,全部失败
do $$
	declare
		v_isok boolean;
	begin
		call test_save(1,v_isok);
		raise notice '%',v_isok;
		call test_save(1,v_isok);
		raise notice '%',v_isok;
	end
$$;
--查询数据
select * from test;

这是一个错误的方法,调用打印的异常消息为

2D000:cannot commit while a subtransaction is active

意思是"子事务处于活动状态时无法提交"

2.3 正确的方法

drop procedure if exists test_save(integer,inout boolean);
create or replace procedure test_save(integer,inout v_isok boolean)
as $$
	declare
		v_sqlstate text;
		v_err text;
	begin
		v_isok := true;
		begin		
			insert into test(id) values($1);		
			exception when others then		
				v_isok := false;
				rollback;
				/*get stacked diagnostics v_sqlstate = returned_sqlstate,
					v_err = message_text;
				raise notice '错误回滚.%:%',v_sqlstate,v_err;*/
		end;
		if(v_isok) then
			commit;
			--raise notice  '提交成功';
		end if;
	end
$$ language plpgsql;

--执行两次,第一次成功,第二次失败
do $$
	declare
		v_isok boolean;
	begin
		call test_save(1,v_isok);
		raise notice '%',v_isok;
		call test_save(1,v_isok);
		raise notice '%',v_isok;
	end
$$;
--查询数据
select * from test;

现在只要检查输入输出参数"v_isok"的值即可实现需求的效果.

2.4 部份提交


drop procedure if exists test_save(integer,inout boolean);
create or replace procedure test_save(integer,inout v_isok boolean)
as $$
	declare
		v_sqlstate text;
		v_err text;
	begin
		v_isok := true;
		begin
			begin		
				insert into test(id) values($1);		
				exception when others then		
					v_isok := false;
					rollback;
					get stacked diagnostics v_sqlstate = returned_sqlstate,
						v_err = message_text;
					raise notice '错误回滚.%:%',v_sqlstate,v_err;
			end;
			if(v_isok) then
				commit;
				raise notice  '提交成功';
			end if;
		end;
		
		begin
			begin		
				insert into test(id) values(3);		
				exception when others then		
					v_isok := false;
					rollback;
					get stacked diagnostics v_sqlstate = returned_sqlstate,
						v_err = message_text;
					raise notice '错误回滚.%:%',v_sqlstate,v_err;
			end;
			if(v_isok) then
				commit;
				raise notice  '提交成功';
			end if;
		end;
		
	end
$$ language plpgsql;