Autonomous Transactions

An autonomous transaction is an independent transaction started in a stored procedure. The transaction is independent of the primary transaction and can continue its operations even after the primary transaction is committed or rolled back. Executing a stored procedure by starting a new database session may increase the usage of system resources, including memory, CPU, and database connections.

It is recommended that autonomous transactions be used to record service logs instead of being used as the entry or core of a service process. Frequent use of autonomous transactions should be avoided to minimize consumption of system resources.

gaussdb=# create schema best_practices_for_procedure;
CREATE SCHEMA
gaussdb=# create table best_practices_for_procedure.log_table(log_time timestamptz, message text);
CREATE TABLE
gaussdb=# create table best_practices_for_procedure.work_table(company text, balance float);
CREATE TABLE
gaussdb=# insert into best_practices_for_procedure.work_table values('huawei', 100000);
INSERT 0 1
-- Create a stored procedure that contains an autonomous transaction.
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); -- Record only logs.
end;
/
CREATE PROCEDURE
-- Call an autonomous transaction in a stored 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-22 16:21:35.27499+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