Oracle to AlloyDB | Cloud SQL (PostgreSQL) migration series: A developer’s guide (Part 3)

Google Cloud Database
, , ,
1

Implementing autonomous transactions in AlloyDB

A step-by-step guide to replicating Oracle’s independent commits for robust logging and error handling in AlloyDB.

Imagine a long-running database procedure making dozens of changes over several minutes. Halfway through, a fatal error occurs.

Your code catches the exception and issues a ROLLBACK, undoing partial changes. That’s great — except the detailed error message you logged right before the rollback also disappears.

In Oracle, this is where PRAGMA AUTONOMOUS_TRANSACTION shines—it allows specific operations (like logging) to commit independently from the main transaction, ensuring your log entries survive even if everything else rolls back.

It’s the key to building resilient systems that can reliably log errors, audit actions, and perform other critical tasks that must survive a rollback.

The challenge: No built-in autonomy, the challenge in PostgreSQL (and AlloyDB)

When migrating to PostgreSQL, and specifically AlloyDB for PostgreSQL, you’ll quickly discover that PostgreSQL has no built-in equivalent to Oracle’s autonomous transactions.
Its transaction model is strictly hierarchical — everything inside a BEGIN...COMMIT block is tied to the same transaction.

To get similar behavior in AlloyDB for PostgreSQL, we need to step outside the normal transaction boundaries.

The solution: The plproxy extension and the two-function pattern

For AlloyDB, the answer lies in using the powerful plproxy extension. This extension allows you to execute a function in a completely separate database session. Since it runs in a new session, it has its own transaction context, which can be committed independently.

The implementation pattern is simple but effective:

  1. The Implementation Function: This is a standard plpgsql function that contains the actual logic you want to run autonomously (e.g., INSERT INTO audit_log...).

  2. The Proxy Procedure: This is a plproxy procedure that your application calls. It contains no business logic. Its only job is to point to the implementation function and tell plproxy to execute it on a specific connection cluster.

Step-by-step implementation

Let’s convert a simple Oracle autonomous procedure into the two-function pattern in AlloyDB.

1. The Original Oracle Procedure Here’s a typical logging procedure in Oracle that needs to commit its work no matter what.

-- Oracle PL/SQL
CREATE OR REPLACE PROCEDURE log_audit_message (
    p_message IN VARCHAR2
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO audit_log (message, log_time)
    VALUES (p_message, SYSDATE);
    COMMIT; -- This commits only the INSERT, not the main transaction
END;
/

2. The AlloyDB Implementation Function (_proxy) First, create the standard plpgsql function that will contain the core logic. A common convention is to append _proxy to the name.

-- AlloyDB - The Implementation Function (plpgsql)
CREATE OR REPLACE FUNCTION log_audit_message_proxy(
    p_message TEXT
)
RETURNS void LANGUAGE 'plpgsql' AS $$
BEGIN
    INSERT INTO audit_log (message, log_time)
    VALUES (p_message, NOW());
    -- No explicit COMMIT is needed here; the separate session handles it.
END;
$$;

3. The AlloyDB Proxy Procedure (plproxy) Next, create the plproxy procedure that your application will call. This is the entry point.

-- AlloyDB - The Proxy Procedure (plproxy)
CREATE OR REPLACE PROCEDURE log_audit_message(
    IN p_message TEXT
)
LANGUAGE 'plproxy' AS $$
    CLUSTER 'autonomous_transaction'; -- Name of the plproxy cluster to use
    TARGET log_audit_message_proxy;  -- Points to our implementation function
$$;

Your plproxy cluster would be configured to connect back to the same database, effectively opening a new session to run the target function.

How it all works together

  1. Your main application transaction calls CALL log_audit_message('Something happened');.

  2. The plproxy procedure intercepts the call.

  3. plproxy opens a new, separate database session and executes log_audit_message_proxy('Something happened').

  4. The INSERT statement runs and is committed within that new session. The session then closes.

  5. Control returns to your main application transaction. If it later fails and rolls back, the committed log message remains untouched.

The takeaway

While PostgreSQL requires a different approach to autonomous transactions, the plproxy pattern in AlloyDB provides a clean and powerful solution.

By separating the proxy call from the implementation logic, you can build robust, auditable, and resilient procedures that reliably persist data, even when the main transaction fails.

Next up in our series**:** “No Oracle AQ (Queues) in Postgres? No Problem. A Guide to Building Your Own Queueing System.”

2 Likes