Sunday, November 07, 2021

Oracle to Postgres Migration Issue with Commit

If you have some PL/SQL, Oracle allows you to include commit statements between a begin and end. Assuming this is appropriate for your application, it can produce two benefits:

(1) If the code runs for a long time, it allows you to monitor progress by running select statements from a separate session.

(2) If the code fails, and you have written it in such a way that it can be restarted, you can fix the problem and start from where you left off.

I am working on a small project at home and decided to write it in PostgreSQL, which I am trying to learn about currently. However, I found out that PostgreSQL does not allow you to include commit statements between a begin and end. You can see what I mean in the example below:

andrew=# do
andrew-# $$
andrew$# declare
andrew$#  a numeric;
andrew$# begin
andrew$#  for a in 1..1000 loop
andrew$#   insert into tab1(col1) values(a);
andrew$#   commit;
andrew$#  end loop;
andrew$# end
andrew$# $$;
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function inline_code_block line 7 at SQL statement

I looked on some online forums and found that several other people had experienced the same problem. Unfortunately I did not understand the suggested workarounds. That's not too much of an issue for me as my project only contains around 50 lines of SQL shared among three scripts. I can quickly move it to Oracle 19 Express Edition and revisit PostgreSQL at a later date.

However, if you are working on a site which is thinking about moving from Oracle to PostgreSQL, it is something you are going to need to understand beforehand.