Everyone hopes 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 original Oracle PL/SQL code
Here is my code in Oracle:
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> 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:
SCT used the proprietary aws_oracle_ext.ora_exception which I simplified to be PostgreSQL compatible by simply checking check_violation:
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:
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.
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:
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.
Top comments (0)