Stored Procedures & Exception Handling when migrating from Oracle to PostgreSQL or YugabyteDB
Tags: oracle, postgres, yugabytedb, migration
Published: 2024-07-03 16:42:50

Lot of companies hope to find a solution to easily move their PL/SQL code from Oracle Database to PostgreSQL or PostgreSQL-compatible managed services such as Amazon Aurora or YugabyteDB. The AWS Schema Conversion Tool and YugabyteDB Voyager are helpful tools for this, but migrating the syntax is just one aspect of the process. Each database engine behaves differently, and failing to understand these differences can result in unexpected outcomes.

## An example

I have two tables: gamers, each with a specified amount of cash, and bids. The business logic is deployed as a stored procedure. A business rule specifies that no bid can exceed $10, which is enforced by a check constraint. If this rule is violated, no error is raised, but the bid is considered a no-op, resulting in a $0 bid with a rejection comment. The constraint violation is catched by the stored procedure code.

## The original Oracle PL/SQL code

Here is my code in Oracle:

```sql
create table gamers (
    name varchar2(50) primary key,
    cash number(10, 2)
);

create table bids (
    name varchar2(50) references gamers,
    bid_amount number(5, 2),
    bid_time timestamp default current_timestamp,
    operation_text varchar2(255),
    constraint bid_amount_check check (bid_amount <= 10)
);

create or replace procedure insert_bid 
(p_name in varchar2, p_bid_amount in number) is
    bid_too_high exception;
    pragma exception_init(bid_too_high, -2290); -- check constraint violated
    l_cash number(10, 2);
begin
    -- Deduct the cash amount
    update gamers
     set cash = cash - p_bid_amount
     where name = p_name;
    -- Insert the bid
    insert into bids (name, bid_amount, operation_text)
     values (p_name, p_bid_amount, 'new bid');
exception
    when bid_too_high then
        -- Reverse the cash deduction
        update gamers
        set cash = cash + p_bid_amount
        where name = p_name;
        -- Insert the no-op bid
        insert into bids (name, bid_amount, operation_text) values 
         (p_name, 0, 'attempted bid of ' || p_bid_amount || ' rejected');
  end;
/

```

Let's run it. I have a gamer with $42 and making 3 bids:
- Bid $20, which exceeds the maximum possible bid in the table.
- Bid $2, which should be successful.

The final cash amount should be $40.

```sql
SQL> column name format a8
SQL> column bid_time format a30
SQL> column operation_text format a30
SQL> set linesize 100
SQL>
SQL> insert into gamers values ('G4mR', 42);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> execute insert_bid('G4mR', 20);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> execute insert_bid('G4mR', 2);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from gamers;

NAME           CASH
-------- ----------
G4mR             40

SQL> select * from bids;

NAME     BID_AMOUNT BID_TIME                       OPERATION_TEXT
-------- ---------- ------------------------------ ------------------------------
G4mR              0 03-JUL-24 08.09.29.742321 AM   attempted bid of 20 rejected
G4mR              2 03-JUL-24 08.09.31.626613 AM   new bid

```

That's the correct result. Let's try to convert that to PostgreSQL.

## Using AWS SCT to transform it to PostgreSQL

I've used AWS Schema Conversion Tool to get the equivalent PostgreSQL syntax:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dfnzfahxkp3h9l1lml80.png)

SCT used the proprietary `aws_oracle_ext.ora_exception` which I simplified to be PostgreSQL compatible by simply checking `check_violation`:


```sql
CREATE OR REPLACE PROCEDURE insert_bid
 (IN p_name TEXT, IN p_bid_amount DOUBLE PRECISION)
AS 
$BODY$
DECLARE
    l_cash NUMERIC(10, 2);
BEGIN
    /* Deduct the cash amount */
    UPDATE gamers
    SET cash = cash - p_bid_amount
        WHERE name = p_name;
    /* Insert the bid */
    INSERT INTO bids (name, bid_amount, operation_text)
    VALUES (p_name, p_bid_amount, 'new bid');
    EXCEPTION
        WHEN check_violation THEN
                    UPDATE gamers
                    SET cash = cash + p_bid_amount
                        WHERE name = p_name;
                    /* Insert the no-op bid */
                    INSERT INTO bids (name, bid_amount, operation_text)
                    VALUES (p_name, 0, CONCAT_WS('', 'attempted bid of ', p_bid_amount, ' rejected'));
END;
$BODY$
LANGUAGE plpgsql;

```

If you know how PostgreSQL works you already spot the problem. 

## Wrong result with the same logic in PostgreSQL

Let's test it:
```sql
yugabyte=# insert into gamers values ('G4mR', 42);
INSERT 0 1

yugabyte=# begin; call insert_bid('G4mR', 20); commit;
BEGIN
CALL
COMMIT

yugabyte=# begin; call insert_bid('G4mR', 2); commit;
BEGIN
CALL
COMMIT

yugabyte=# select * from gamers;
 name | cash
------+-------
 G4mR | 60.00
(1 row)

yugabyte=# select * from bids;
 name | bid_amount |          bid_time          |        operation_text
------+------------+----------------------------+------------------------------
 G4mR |       2.00 | 2024-07-03 12:43:08.643212 | new bid
 G4mR |       0.00 | 2024-07-03 12:43:07.522352 | attempted bid of 20 rejected
(2 rows)
```

The logic that was written for Oracle doesn't work correctly in PostgreSQL. Instead of decreasing to $40, the cash increases from $42 to $60. 

This is because, in PostgreSQL and YugabyteDB, the PL/pgSQL block is atomic and doesn't need compensation in the exception block like in Oracle.

To prevent partial changes when entering the exception block, the main block rolls back before entering the exception block. Unlike in Oracle, there's no need to clean up or compensate for partial changes in the exception block.

## The right logic for PostgreSQL and YugabyteDB

Here is the correct code, much simpler. It implicitly rolls back the update of the cash in the gamers' table when entering the exception so that the exception only has to insert the no-op bid.

```sql
CREATE OR REPLACE PROCEDURE insert_bid
 (IN p_name TEXT, IN p_bid_amount DOUBLE PRECISION)
AS 
$BODY$
DECLARE
    l_cash NUMERIC(10, 2);
BEGIN
    /* Deduct the cash amount */
    UPDATE gamers
    SET cash = cash - p_bid_amount
        WHERE name = p_name;
    /* Insert the bid */
    INSERT INTO bids (name, bid_amount, operation_text)
    VALUES (p_name, p_bid_amount, 'new bid');
    EXCEPTION
        WHEN check_violation THEN
                    /* Insert the no-op bid */
                    INSERT INTO bids (name, bid_amount, operation_text)
                    VALUES (p_name, 0, CONCAT_WS('', 'attempted bid of ', p_bid_amount, ' rejected'));
END;
$BODY$
LANGUAGE plpgsql;
```

Without any attempt to compensate for changes, because that is done by the database, it provides the right result:
```sql
yugabyte=# insert into gamers values ('G4mR', 42);
INSERT 0 1
yugabyte=# begin; call insert_bid('G4mR', 20); commit;
BEGIN
CALL
COMMIT
yugabyte=# begin; call insert_bid('G4mR', 2); commit;
BEGIN
CALL
COMMIT
yugabyte=# select * from gamers;
 name | cash
------+-------
 G4mR | 40.00
(1 row)

yugabyte=# select * from bids;
 name | bid_amount |          bid_time          |        operation_text
------+------------+----------------------------+------------------------------
 G4mR |       2.00 | 2024-07-03 12:53:50.889344 | new bid
 G4mR |       0.00 | 2024-07-03 12:53:49.787083 | attempted bid of 20 rejected
(2 rows)

```

PostgreSQL implicitly creates a savepoint at the beginning of execution. It's important to note that savepoints can be expensive in PostgreSQL, even though it has been optimized for PG17. On the other hand, YugabyteDB behaves similarly to PostgreSQL but with a scalable implementation that avoids the issues related to savepoints.

This example demonstrates that the most crucial aspect of migration is not just switching between languages but also verifying the behavior and outcome. To avoid being locked in with a particular vendor, it is important to have a good understanding of how both databases work and good regression tests that encompass all your business logic.