Starting the journey of moving PRAGMA AUTONOMOUS_TRANSACTION from Oracle to PostgreSQL is like translating old writings into modern language. It’s a bit tricky but exciting because we can do magic with code, turning problems into opportunities. This guide will help you understand and use PRAGMA AUTONOMOUS_TRANSACTION in Oracle and find similar functionality in PostgreSQL.
Understanding PRAGMA AUTONOMOUS_TRANSACTION in Oracle
In Oracle, PRAGMA AUTONOMOUS_TRANSACTION is used to declare that a transaction within a PL/SQL block is autonomous from its parent transaction. This means it can commit or roll back independently, without affecting the parent transaction. It’s commonly used for logging, auditing, or any scenario where a discrete transaction is needed.
The Challenge in PostgreSQL
PostgreSQL does not have a direct equivalent of PRAGMA AUTONOMOUS_TRANSACTION. Transactions in PostgreSQL are managed differently, with a focus on transaction blocks and savepoints. To achieve similar functionality, you need to leverage PostgreSQL’s advanced features like dblink or stored procedures with autonomous transactions through extensions. Here we are using dblink.
Migration Strategies:
Scenario 1:
Suppose we need to roll back all changes in the parent transaction but keep the data inserted by the child transaction after running the main procedure only.
It is necessary to execute both procedures in separate transactions or sessions. Alternatively, Oracle provides a PRAGMA AUTONOMOUS TRANSACTION directive function that can create a new session or transaction.
Let’s assume we have a table named PRAG in Oracle with the following syntax:
CREATE TABLE “PRAGMA_TEST”.PRAG
( ms VARCHAR(256),
intime date ) ;
–Parent procedure syntax
CREATE OR REPLACE PROCEDURE PRAGMA_TEST.massrollback
IS
BEGIN
Insert into PRAG values (‘main1’, CURRENT_TIMESTAMP);
Insert into PRAG values (‘main2’, CURRENT_TIMESTAMP);
PRAGMA_TEST.chlcommit;
Insert into PRAG values (‘main2’, CURRENT_TIMESTAMP);
Rollback;
END;
Above procedure inserts records into the PRAG table, calls the chlcommit procedure, inserts another record, and then rolls back the transaction.
–Child procedure syntax:-
Create or replace procedure “PRAGMA_TEST”.chlcommit
Is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Insert into PRAG values (‘child’, CURRENT_TIMESTAMP);
COMMIT;
END;
This procedure inserts a record into the PRAG table and commits the transaction. Usage of PRAGMA AUTONOMOUS_TRANSACTION, which allows the child procedure to execute in its own transaction context independent of the calling transaction.
After executing both procedures:
The PRAG table will not have any new records inserted by the massrollback() procedure because its transaction was rolled back.
The PRAG table will have one new record inserted by the chlcommit() procedure with the value ‘child’ that we can see in above output. This record will remain in the table since its transaction was committed independently.
PRAGMA AUTONOMOUS TRANSACTION Equivalent In PostgreSQL
We can achieve the PRAGMA AUTONOMOUS_TRANSACTION functionality in PostgreSQL using DBLinks. In the scenario that we have checked above using autonomous transaction functionality in Oracle, we will try to achieve the same functionality in PostgreSQL.
Scenario 1 (As Oracle Scenario 1 above)
CREATE TABLE poc.prag
( ms text,
intime date ) ;
–Parent Procedure syntax
CREATE OR REPLACE PROCEDURE poc.masrollback( )
LANGUAGE ‘plpgsql’
AS $BODY$
begin
insert into poc.prag (select ‘mas1’,now());
insert into poc.prag (select ‘mas2’,now());
call poc.chlcommit();
insert into poc.prag (select ‘mas3’,now());
rollback;
end;
$BODY$;
Above procedure inserts records into the prag table, calls the chlcommit procedure, inserts another record, and then rolls back the transaction.
–Child Procedure Syntax
CREATE OR REPLACE PROCEDURE poc.chlcommit( )
LANGUAGE ‘plpgsql’
AS $BODY$
begin
perform dblink(‘host=localhost port=5432 user=postgres password=password dbname=practice’,’insert into poc.prag values (”child”,now());commit;’);
end;
$BODY$;
This procedure performs an insert operation using dblink, which executes an insert statement on a remote PostgreSQL database. After the insert operation, it commits the transaction.
As shown in the below image we are getting the same output as we have got in Scenario 1 of Oracle, as DBLink will create a new session for the child procedure.
The Magic of Transformation
In our journey from Oracle’s ancient places to PostgreSQL’s magical lands, we’ve changed a difficult thing called PRAGMA AUTONOMOUS_TRANSACTION into spells that work just as well. This magical process might seem hard, but those who are brave enough to try it gain valuable knowledge and the ability to come up with new ideas.
When you use these new spells, remember that the real magic isn’t just in saying the words. It comes from being creative and understanding what you’re doing. I hope your transitions go smoothly, and your transactions work independently. We’re heading into the world of PostgreSQL with knowledge and old spells, ready to start the next part of our journey in using SQL magic.
Ready to embark on your migration journey from Oracle to PostgreSQL with confidence?
Dive deeper into the intricacies of autonomous transaction migration strategies and unlock the full potential of your database operations. Explore how Newt Global’s DMAP can facilitate seamless migration processes, ensuring efficiency and reliability every step of the way.
Learn more about DMAP and its capabilities at newtglobal.com and reach out to us at marketing@newtglobalcorp.com to discover how we can empower your migration project.
Remember, Newt Global DMAP is a world-class product enabling mass migration of Oracle DB to cloud-native PostgreSQL, faster, better, and cheaper.