自治事务

自治事务指的是在存储过程中启动一个独立的事务，该事务与主事务相互独立，能够在主事务提交或回滚后继续其操作。通过启动新的数据库会话（SESSION）来执行存储过程，自治事务可能会增加系统资源的使用，包括内存、CPU和数据库连接等。

建议将自治事务主要用于记录业务日志，而不应将其作为业务流程的入口或核心环节。应尽量避免频繁使用自治事务，以减少对系统资源的消耗。

gaussdb=# create schema best_practices_for_procedure;
CREATE SCHEMA
gaussdb=# create table best_practices_for_procedure.log_table(log_time timestamptz, message text);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'log_time' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# create table best_practices_for_procedure.work_table(company text, balance float);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'company' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# insert into best_practices_for_procedure.work_table values('huawei', 100000);
INSERT 0 1
--创建自治事务存储过程。
gaussdb=# create or replace procedure best_practices_for_procedure.proc_auto(log_time timestamptz, message text) as
PRAGMA AUTONOMOUS_TRANSACTION; 
begin
insert into best_practices_for_procedure.log_table values (log_time, message); --只记录日志信息。
end;
/
CREATE PROCEDURE
--在存储过程内调用自治事务。
gaussdb=# create or replace procedure best_practices_for_procedure.proc1(companys text, turnover float) as
message text;
begin
    update best_practices_for_procedure.work_table set balance = balance + turnover where company = companys;
    message := 'Company turnover ' || turnover;
    best_practices_for_procedure.proc_auto(current_timestamp, message);
end;
/
CREATE PROCEDURE
gaussdb=# call best_practices_for_procedure.proc1('huawei', 1000);
 proc1
-------

(1 row)

gaussdb=# select * from best_practices_for_procedure.log_table;
           log_time            |        message
-------------------------------+-----------------------
 2024-11-25 15:23:27.202458+08 | Company turnover 1000
(1 row)

gaussdb=# drop schema best_practices_for_procedure cascade;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to table best_practices_for_procedure.log_table
drop cascades to table best_practices_for_procedure.work_table
drop cascades to function best_practices_for_procedure.proc_auto(timestamp with time zone,text)
drop cascades to function best_practices_for_procedure.proc1(text,double precision)
DROP SCHEMA