Creating a Table for Tracking Logs:

-- Creating a table for log storage
DROP TABLE IF EXISTS test1;
CREATE TABLE table_tracking(
  log_id serial, 
  username text, 
  event_date timestamp, 
  msg text
);

Creating log_action_atx Function with Autonomous Transaction:

-- Defining a function with autonomous transaction for logging
CREATE OR REPLACE FUNCTION log_action_atx (
  username text, 
  event_date timestamp, 
  msg text
) RETURNS VOID AS
$body$
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  START TRANSACTION;
  INSERT INTO table_tracking 
    VALUES (
      nextval('table_tracking_log_id_seq'::regclass),
      username, 
      event_date, 
      msg
    );
  COMMIT;
END;
$body$
LANGUAGE plpgsql;

Creating the Main Function log_action that Calls log_action_atx:

-- Main function for performing actions and calling the logging function
CREATE OR REPLACE FUNCTION log_action (
  username text, 
  event_date timestamp, 
  msg text
) RETURNS VOID AS
$body$
DECLARE
  v_query text;
BEGIN
  -- Call the autonomous transaction for logging
  v_query := 'SELECT log_action_atx ( ' 
    || quote_nullable(username) 
    || ',' || quote_nullable(event_date) 
    || ',' || quote_nullable(msg) 
    || ' )';
  EXECUTE v_query;

  -- Executing additional actions with an error
  RAISE NOTICE 'Division by zero';
  PERFORM 5/0;
END;
$body$
LANGUAGE PLPGSQL;

Executing the Main Function to Demonstrate its Functionality:

-- Calling the log_action function to demonstrate logging operation
SELECT * 
FROM log_action(
  current_user::text, 
  now()::timestamp, 
  'Test'::text
);  

This code demonstrates using an autonomous transaction within a PL/pgSQL function to log information separately from the main transaction. Here is what it is doing:

So in summary, the autonomous transaction allows logging to occur separately from the main transaction isolation. This ensures logs are not lost on transaction rollback. The trick is the AUTONOMOUS_TRANSACTION pragma declaration in log_action_atx().

NOTICE:  Division by zero
ERROR:  division by zero
CONTEXT:  SQL statement "SELECT 5/0"
PL/pgSQL function log_action(text,timestamp without time zone,text) line 11 at PERFORM

-- Query to check stored logs
SELECT msg FROM table_tracking;
msg

Creating a Procedure with an Autonomous Transaction:

-- Creating a procedure with an autonomous transaction for independent operations
CREATE OR REPLACE PROCEDURE insert_two_rows() LANGUAGE plpgsql AS $$
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO dept VALUES (60, 'FINANCE', 'CHICAGO');
  CALL insert_dept_70(); 
  COMMIT; 
END;
$$;

Main Transaction Executing with a Rollback:

-- Executing main transaction with operations and a rollback
BEGIN;
  INSERT INTO dept VALUES (50, 'HR', 'DENVER');
  CALL insert_two_rows(); 
ROLLBACK;

Query to Display the Final State of the Table:

-- Display the final state of the table after transactions
test=# SELECT * FROM dept;
 deptno |   dname    |     loc     
--------+------------+-------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     60 | FINANCE    | CHICAGO
     70 | MARKETING  | LOS ANGELES
(6 rows)

Key Points:

Outcome: Records 60 and 70 are persisted in the table, but not 50.

Creating a Procedure for Inserting Department 70:

-- Creating a procedure to insert department 70 into the dept table
CREATE OR REPLACE PROCEDURE insert_dept_70() LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
END;
$$;

Creating a Procedure with Autonomous Transaction and Rollback:

-- Creating a procedure with an autonomous transaction that inserts and then rolls back
CREATE OR REPLACE PROCEDURE insert_to_values() LANGUAGE plpgsql AS $$
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
START TRANSACTION;
  INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
  CALL insert_dept_70();
  ROLLBACK;
END;
$$;

Executing Main Transaction and Committing:

-- Executing main transaction that inserts a record and calls the insert_to_values procedure
START TRANSACTION;
  INSERT INTO dept VALUES (50,'HR','DENVER');
  CALL insert_to_values();
COMMIT;

Display the Final State of the Table:

-- Display the final state of the dept table after transactions
 test=# SELECT * FROM dept;
 deptno |   dname    |     loc     
--------+------------+-------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | HR         | DENVER
     70 | MARKETING  | LOS ANGELES
(6 rows)

Key Points:

Outcome: Records 50 and 70 are persisted in the table, but not 60.

The PRAGMA AUTONOMOUS_TRANSACTION directive allows for autonomous transactions within procedures, making these transactions independent from the main or outer transactions. Thus, actions like COMMIT or ROLLBACK in an autonomous transaction don't affect the main transaction and vice versa.

Creating Schema and Tables for User Activity Auditing:

-- Setup for user activity auditing: schema and related tables
CREATE SCHEMA audtrig;

CREATE TABLE audtrig.user_activity (
    id        INT,
    user_name VARCHAR
);

CREATE TABLE audtrig.log (
  log_time timestamp,
  user_name varchar,
  event varchar
);

Creating Logging Function with Autonomous Transaction:

-- Defining a function to autonomously log operations on user_activity
CREATE OR REPLACE FUNCTION audtrig.log_func()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_action        VARCHAR;
BEGIN
  IF (TG_OP = 'INSERT') THEN
      v_action := 'Added id=' || NEW.id::text;
  ELSIF (TG_OP = 'UPDATE') THEN
      v_action := 'Updated id=' || NEW.id::text;
  ELSIF (TG_OP = 'DELETE') THEN
      v_action := 'Deleted id=' || OLD.id::text;
  END IF;
  INSERT INTO audtrig.log VALUES (now(), current_user, v_action);

RETURN NEW;
END;
$$;

Creating Trigger for Logging Function Execution:

-- Trigger to capture and log changes in the user_activity table
CREATE OR REPLACE TRIGGER audtrig_trig_func
AFTER INSERT OR UPDATE OR DELETE
ON audtrig.user_activity
FOR EACH ROW
EXECUTE PROCEDURE audtrig.log_func();

SELECT * FROM audtrig.log;
SELECT * FROM audtrig.user_activity;

Executing Data Operations and Logging Them:

-- Inserting and deleting data in user_activity, and checking the resulting logs
INSERT INTO audtrig.user_activity VALUES (9001,'SMITH');
INSERT INTO audtrig.user_activity VALUES (9002,'JONES');
DELETE FROM audtrig.user_activity WHERE id=9001;

SELECT * FROM audtrig.log;
SELECT * FROM audtrig.user_activity;

Testing Rollback of Transaction and Its Effect on Logs:

-- Demonstrating transaction rollback and its impact on logs
TRUNCATE audtrig.log;
TRUNCATE audtrig.user_activity;
START TRANSACTION;
INSERT INTO audtrig.user_activity VALUES (9001,'SMITH');
INSERT INTO audtrig.user_activity VALUES (9002,'JONES');
DELETE FROM audtrig.user_activity WHERE id=9001;
ROLLBACK;

SELECT * FROM audtrig.log;
SELECT * FROM audtrig.user_activity;

Cleanup: Dropping Tables and Schema:

-- Cleaning up: Dropping the log and user_activity tables and the audtrig schema
DROP TABLE audtrig.log;
DROP TABLE audtrig.user_activity;

Operations and Results: