自治事务

功能描述

该功能支持在定义PL/pgSQL定义函数/存储过程中，在声明部分增加指令：PRAGMA AUTONOMOUS_TRANSACTION，用来控制对当前存储过程中的内部事务可以进行独立提交，而不影响其他的事务。该执行方式与普通方式执行函数/存储过程相同。此外，自治事务还支持如下功能：

支持PL/pgSQL块内的SQL语句使用函数/存储过程参数和定义的变量。
允许在匿名块内声明自治事务。
支持自治事务的嵌套使用。
允许在触发器内声明自治事务。包括DML类型的触发器和DDL事件触发器。该功能仅在数据库版本为V2.2 Build 10 （Patch No.16）及以上版本支持。

注意事项

声明为自治事务的函数/存储过程对返回值以及参数有如下限制：

不支持集合作为返回值。
不支持组合类型作为返回类型。
不支持游标类型。
不支持out参数。

语法格式

PRAGMA AUTONOMOUS_TRANSACTION;

示例

示例1： 自治事务的嵌套使用。

1、创建测试表。

create table at_tb2(id int, val varchar(64));

2、创建声明为自治事务的存储过程。

create or replace procedure at_test3(i int)
AS
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  insert into at_tb2 values(1, 'before s1');
  insert into at_tb2 values(2, 'after s1');
  if i > 10 then
  rollback;
  else
  commit;
  end if;
end;
/
commit;  -- 如果数据库未开启默认自动提交，则需要手动提交

3、调用存储过程。

call at_test3(6);

4、查询表中的数据。

select * from at_tb2;

返回结果为：

 id |    val
----+-----------
  1 | before s1
  2 | after s1
(2 rows)

5、支持PL/pgSQL块内的SQL语句使用函数/存储过程参数和定义的变量。

create or replace procedure at_proc(i int)
as
declare
strval varchar;
pragma autonomous_transaction;
begin
strval:= 'begin:insert by autonomous transaction procedure<at_proc>';
insert into at_tb2 values(i,strval);
commit;
end;
/

commit; -- 如果数据库未开启默认自动提交，则需要手动提交

6、调用存储过程。

call at_proc(1);

7、查询表中的数据。

select * from at_tb2;

返回结果为：

 id |                            val
----+-----------------------------------------------------------
  1 | before s1
  2 | after s1
  1 | begin:insert by autonomous transaction procedure<at_proc>
(3 rows)

8、支持在匿名块内声明自治事务。

declare
pragma autonomous_transaction;
strval varchar;
begin strval:='insert by autonomous transaction by anonymous block';
insert into at_tb2 values(1,strval);
commit;
end;
/

9、查询表中数据。

select * from at_tb2;

返回结果为：

 id |                            val
----+-----------------------------------------------------------
  1 | before s1
  2 | after s1
  1 | begin:insert by autonomous transaction procedure<at_proc>
  1 | insert by autonomous transaction by anonymous block
(4 rows)

10、支持自治事务嵌套使用-创建嵌套自治事务的存储过程。

create or replace procedure at_proc_inner(i int)
as
declare
pragma autonomous_transaction;
begin
insert into at_tb2 values(1,'insert by autonomous transaction procedure<at_proc_inner>');
if i>5 then
rollback;
else
commit;
end if;
end;
/

create or replace procedure at_proc_outer(i int)
as
declare
pragma autonomous_transaction;
begin
insert into at_tb2 values(1,'insert by autonomous transaction procedure<at_proc_outer>');
at_proc_inner(i);
if i>10 then rollback;
else
commit;
end if;
end;
/

11、调用存储过程并查询结果。

truncate at_tb2;
call at_proc_outer(8);
select * from at_tb2;

返回结果为：

id |                                 val
---------------------------------------------------------------
  1 | insert by autonomous transaction procedure<at_proc_outer>
(1 row)

示例2： 在触发器中使用自治事务。

1、创建测试表。

create table test_trigger_src_tbl(id1 int, id2 int, id3 int);
create table test_trigger_des_tbl(id1 int, id2 int, id3 int);

2、创建触发函数。

create or replace function tri_insert_func() returns trigger as
$$
declare
pragma autonomous_transaction;
begin
insert into test_trigger_des_tbl values(new.id1, new.id2, new.id3);
return new;
end
$$ language plpgsql;

3、创建触发器。

create trigger insert_trigger
before insert on test_trigger_src_tbl
for each row
execute procedure tri_insert_func();

4、执行INSERT操作。

insert into test_trigger_src_tbl values(100,200,300);

5、查询表数据。

select * from test_trigger_src_tbl;
select * from test_trigger_des_tbl;

返回结果均为：

id1 | id2 | id3
----+-----+-----
100 | 200 | 300
(1 row)

示例3： 触发器函数支持自治事务：CREATE/ALTER/DROP TABLE命令触发事件触发器。

1、创建触发函数。

CREATE OR REPLACE FUNCTION eth1_1196400()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
declare
pragma autonomous_transaction;
BEGIN
RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
END;
$$;

2、创建事件触发器。

CREATE EVENT TRIGGER start_eventtri_1196400 ON ddl_command_start EXECUTE PROCEDURE eth1_1196400();
CREATE EVENT TRIGGER end_eventtri_1196400 ON ddl_command_end EXECUTE PROCEDURE eth1_1196400();
CREATE EVENT TRIGGER drop_eventtri_1196400 ON sql_drop EXECUTE PROCEDURE eth1_1196400();
CREATE EVENT TRIGGER rewrite_eventtri_1196400 ON table_rewrite EXECUTE PROCEDURE eth1_1196400();

3、创建表。

CREATE TABLE tb1_1196400(id serial primary key, foo float);
insert into tb1_1196400 select x * 1.001 from generate_series(1,500) as t(x);

返回结果如下，触发执行函数eth1():

NOTICE: test_event_trigger: ddl_command_start CREATE TABLE
NOTICE: CREATE TABLE will create implicit sequence "tbl_1196400_id_seq" for serial column "tbl_1196400.id"
NOTICE: test_event_trigger: ddl_command_start CREATE SEQUENCE
NOTICE: test_event_trigger: ddl_command_end CREATE SEQUENCE
NOTICE: test_event_trigger: ddl_command_start CREATE INDEX
NOTICE: CREATE TABLE /PRIMARY KEY will create implicit index "tbl1196400_pkey" for table "tbl_1196400"
NOTICE: test_event_trigger: ddl_command_end CREATE INDEX
NOTICE: test_event_trigger: ddl_command_start ALTER SEQUENCE
NOTICE: test_event_trigger: ddl_command_end ALTER SEQUENCE
NOTICE: test_event_trigger: ddl_command_end CREATE TABLE
NOTICE: CREATE TABLE

4、向表中插入数据。

insert into tb1_1196400 select x * 1.001 from generate_series(1,500) as t(x);

5、修改表字段。

ALTER TABLE tb1_1196400 alter column foo type numeric;
ALTER TABLE tb1_1196400 drop column foo;

返回结果分别为：

NOTICE: test_event_trigger:ddl_command_start ALTER TABLE
NOTICE: test_event_trigger:table_rewrite ALTER TABLE
NOTICE: test_event_trigger:sql_drop ALTER TABLE
NOTICE: test_event_trigger:ddl_command_end ALTER TABLE
ALTER TABLE

NOTICE: test_event_trigger:ddl_command_start ALTER TABLE
NOTICE: test_event_trigger:sql_drop ALTER TABLE
NOTICE: test_event_trigger:ddl_command_end ALTER TABLE
ALTER TABLE

6、删除表。

DROP TABLE tb1_1196400;

返回结果为：

NOTICE: test_event_trigger: ddl_command_start,DROP TABLE
NOTICE: test_event_trigger: sql_drop,DROP TABLE
NOTICE: test_event_trigger: ddl_command_end,DROP TABLE
DROP TABLE

VastbaseG100

自治事务

功能描述

注意事项

语法格式

示例

弹框头部