Создание таблицы для отслеживания журналов:

-- 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
);

Создание функции log_action_atx с автономной транзакцией:

-- 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;

Создание основной функции log_action, вызывающей 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;

Выполнение главной функции для демонстрации ее функциональности:

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

Этот код демонстрирует использование autonomous transaction внутри функции PL/pgSQL для регистрации информации отдельно от основной транзакции. Вот что он делает:

Итак, вкратце, autonomous transaction позволяет вести журнал отдельно от основной транзакции. Это гарантирует, что журналы не будут потеряны при откате транзакции. Секрет заключается в объявлении прагмы AUTONOMOUS_TRANSACTION в 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

Создание процедуры с 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;
$$;

Основная транзакция выполняется с откатом:

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

Запрос для отображения конечного состояния таблицы:

-- 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)

Ключевые моменты:

Результат: Записи 60 и 70 сохраняются в таблице, но не 50.

Создание процедуры для вставки отдела 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;
$$;

Создание процедуры с Autonomous Transaction и откатом:

-- 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 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 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)

Ключевые моменты:

Результат: Записи 50 и 70 сохраняются в таблице, но не 60.

Директива PRAGMA AUTONOMOUS_TRANSACTION позволяет использовать autonomous transactions внутри процедур, делая эти транзакции независимыми от основных или внешних транзакций. Таким образом, действия, такие как COMMIT или ROLLBACK в autonomous transaction, не влияют на основную транзакцию и наоборот.

Создание схемы и таблиц для аудита действий пользователей:

-- 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
);

Создание функции журналирования с 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;
$$;

Создание триггера для регистрации выполнения функции:

-- 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;

Выполнение операций с данными и их регистрация:

-- 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;

Тестирование отката транзакции и его влияние на журналы:

-- 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;

Очистка: Удаление таблиц и схемы:

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

Операции и Результаты: