Migrate Oracle Autonomous Transaction to CloudSQL for PostgreSQL.

One of the most common migration patterns usually encountered on migrating from Oracle to PostgreSQL compatible is Autonomous Transaction. Autonomous transactions provide functionality to implement sub programs that can be transactionally committed or rollback independent of parent transactions.Natively PostgreSQL does not provide similar features.

As part of building migration patterns to implement similar features in PostgreSQL compatible we follow below guidelines.

1. Refactor functionality implemented within autonomous transactions within Application or working backwards to understand requirements and implement as cloud native if feasible.
2. If a refactor or alternative solution is not possible, leverage PostgreSQL extensions supported by CloudSQL like dblink.

In this post, we will implement similar functionality using dblink extension and use cloud sql database flags to avoid passing passwords for dblink local connection to same instance.

Autonomous Transaction in Oracle

Let’s create a mock code to understand autonomous transaction behaviour in Oracle.

create table sample_tbl (col1 integer);
create table sample_tbl_calls(col1 varchar2(10) , col2 date);
CREATE OR REPLACE PROCEDURE sample_proc_auto_trans
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO sample_tbl_calls VALUES(‘called’,sysdate);
commit;
END sample_proc_auto_trans;
/

PRAGMA is compiler directives in Oracle. AUTONOMOUS_TRANSACTION highlights procedural blocks to be independent of parent transactions and run within its own scope of defined Transaction.

We will use the below block of plsql code to mock autonomous transaction calls and rollback the parent insert by raising random exceptions.

begin
insert into sample_tbl values (1);
sample_proc_auto_trans();
raise ZERO_DIVIDE;
commit;
exception when others then
rollback;
end;

We have rollback the actual insert, but insert within autonomous procedure is committed.

Extensions within PostgreSQL enhance the overall functionality provided by the database. Check here for a complete list of extensions supported in Cloud SQL.

dblink within PostgreSQL will explicitly open a new connection and execute an autonomous component within its own transactional scope. It has its own overhead in terms of new connections that need to be considered on overall impacts.

Let’s start by creating extensions within the Database.

dblinksample=> create extension dblink;
create extension dblink;
CREATE EXTENSION

Once dblink extension is added, we can invoke remote calls by passing necessary details. With Autonomous transaction remote call is implemented using dblink but it is connecting to the same database and instance.

Typically autonomous transaction converted functions will look like below.

CREATE OR REPLACE FUNCTION sample_proc_auto_trans()
RETURNS void
AS
$BODY$
DECLARE
v_sql text;
BEGIN
PERFORM dblink_connect('conn','dbname=dbuser user=postgres password=********** port=5432');
v_sql := 'INSERT INTO sample_tbl_calls VALUES (''called'',clock_timestamp())';
PERFORM dblink_exec('conn', v_sql);
PERFORM dblink_disconnect(‘conn’);
END;
$BODY$
LANGUAGE plpgsql;

please note :- we have explicitly passed the credential of the db user and no instance details are passed as it will connect to the same instance.

Let’s run below the pl\pgsql block that will trigger rollback on exception.

DO $$
BEGIN
insert into sample_tbl values (1);
PERFORM sample_proc_auto_trans();
raise;
END$$;

It implicitly rollback on exception but dml from sample_proc_auto_trans is committed.

One primary issue with such implementation is hard coded password being passed as part of procedural code logic.

CloudSQL provides a Database flag “allow_passwordless_local_connections” that enables local connections without the need of passing passwords. We can enable it and change previous code logic to not include password as below.

PERFORM dblink_connect('conn','dbname=dblinksample user=postgres port=5432');

Once database flags are set, we can use dblink for all local connections without passing user credentials.

We can extend similar behaviour while creating foreign server and user mapping as well.It can be used for any local connection from dblink.

CREATE SERVER loopback_dblink FOREIGN DATA WRAPPER dblink_fdw OPTIONS (dbname 'dblinksample');
CREATE USER MAPPING FOR postgres SERVER loopback_dblink
OPTIONS (user 'dbuser');

Overall dblink implementation with cloud sql flags help us facilitate similar behaviour as in Oracle for Autonomous Transaction. With no need of password using CloudSQL provided flag, we can avoid passing as part or local connection from dblink.
It also adds additional overhead on connections, so it needs to be tested and measure impact accordingly before implementing module.