Standalone Transactions in PostgreSQL
Let's start by defining what autonomous transactions are in principle:
Autonomous transactions allow you to create new subtransactions that can save or revert changes regardless of the parent transaction. More details here .
They are implemented in the Oracle DBMS, and are a very flexible and convenient tool. The most popular (but by no means the only) use of autonomous transactions is logging. Consider a simple example. Imagine the following situation - a long and complex stored procedure, such as monthly billing, is implemented in your database. You probably would like to see important events in the log table. Of course, you can simply write into it without any tricks. But there is one serious drawback - if an unhandled error occurs in the procedure - the transaction is rolled back along with all entries in the log table. This is where autonomous transactions come to the rescue. They are executed regardless of the parent transaction, and you can see the entries in the log table before the completion of the logged billing procedure.
Let's move on to the topic topic. Despite the pretentious title, there are no stand-alone transactions in PostgreSQL. But there is a way by which we can get a similar result.
First, we need the dblink contrib module . This module allows using its functions to access another instance of PostgreSQL. A detailed description is beyond the scope of the topic. On my own behalf I will say that dblink is one of several useful contrib-modules that I always install regardless of the tasks solved by the database.
I will show the described technique on an example from the beginning of the topic. We implement logging on "autonomous" transactions.
Create a log table:
Let's create a function for logging:
Now, at any moment, we can call the “log” function and the record will immediately appear in the log table, regardless of the status of the transaction during which we called it.
As you can see, the whole trick is that we write to the log table using the “dblink_exec” function. This means that a new connection, session and transaction are being created, in the context of which the recording takes place.
Autonomous transactions allow you to create new subtransactions that can save or revert changes regardless of the parent transaction. More details here .
They are implemented in the Oracle DBMS, and are a very flexible and convenient tool. The most popular (but by no means the only) use of autonomous transactions is logging. Consider a simple example. Imagine the following situation - a long and complex stored procedure, such as monthly billing, is implemented in your database. You probably would like to see important events in the log table. Of course, you can simply write into it without any tricks. But there is one serious drawback - if an unhandled error occurs in the procedure - the transaction is rolled back along with all entries in the log table. This is where autonomous transactions come to the rescue. They are executed regardless of the parent transaction, and you can see the entries in the log table before the completion of the logged billing procedure.
Let's move on to the topic topic. Despite the pretentious title, there are no stand-alone transactions in PostgreSQL. But there is a way by which we can get a similar result.
dblink
First, we need the dblink contrib module . This module allows using its functions to access another instance of PostgreSQL. A detailed description is beyond the scope of the topic. On my own behalf I will say that dblink is one of several useful contrib-modules that I always install regardless of the tasks solved by the database.
Example
I will show the described technique on an example from the beginning of the topic. We implement logging on "autonomous" transactions.
Create a log table:
CREATE TABLE "public"."logs" (
"log_id" BIGSERIAL,
"source" TEXT NOT NULL,
"level" TEXT NOT NULL,
"message" TEXT NOT NULL,
"time_added" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
CONSTRAINT "logs_pkey" PRIMARY KEY("log_id")
) WITH OIDS;
* This source code was highlighted with Source Code Highlighter.Let's create a function for logging:
CREATE OR REPLACE FUNCTION "public"."log"(IN p_source text, IN p_level text, IN p_message text)
RETURNS void AS
$$
DECLARE
INSERT_SQL text := 'INSERT INTO public.logs (source, level, message) VALUES (''[SOURCE]'',''[LEVEL]'',''[MESSAGE]'')';
BEGIN
IF p_level != 'ERROR' AND p_level != 'WARNING'
AND p_level != 'INFO' AND p_level != 'DEBUG' THEN
RAISE EXCEPTION 'Log level should be one of the following types: ERROR, WARNING, INFO, DEBUG';
END IF;
INSERT_SQL := replace(INSERT_SQL, '[SOURCE]', p_source);
INSERT_SQL := replace(INSERT_SQL, '[LEVEL]', p_level);
INSERT_SQL := replace(INSERT_SQL, '[MESSAGE]', p_message);
PERFORM dblink_exec('dbname=' || current_database(), INSERT_SQL);
END;
$$ LANGUAGE 'plpgsql';
* This source code was highlighted with Source Code Highlighter.Now, at any moment, we can call the “log” function and the record will immediately appear in the log table, regardless of the status of the transaction during which we called it.
PERFORM "public"."log"('monthly billing', 'INFO', 'Starting monthly billing');
* This source code was highlighted with Source Code Highlighter.As you can see, the whole trick is that we write to the log table using the “dblink_exec” function. This means that a new connection, session and transaction are being created, in the context of which the recording takes place.
Remarks
- Unfortunately, this feint cannot completely replace Oracle autonomous transactions. The problem is performance - I would not recommend using this technique left and right, think about where it really is needed.
- A similar method applies not only to PostgreSQL. Every DBMS I know has features like dblink_exec
- As correctly noted in the comments, using dblink_connect to create a persistent connection, and using this connection in the future, will significantly speed up the work.