Oracle to PostgreSQL code migration pain

kpoluektov — Thu, 17 Aug 2023 03:51:52 +0000

Pessimistic exception

Well, who will use pessimistic locks now in times of eventual consistency? But, actually the approach still is the main pattern to handling double-entry bookkeeping. In rdbms world it is quite easy to provide the pattern with the select ... for update [no]wait statement.
"So what's the catch?” you can ask? After all, the ANSI SQL complaint construction is supported in both rdbms (well, in the nowaitpart, for sure)! Exactly! for update nowait works fine. But there is a big difference with exception handling.

object TestLockException extends App {
  val updateSql = "select 1 from type_test_table where id = ? for update nowait";
  def execLockTest(exec: DBExecute, id: Integer) {
    val check: Try[Unit] = Using(exec.getConnection) { conn =>
      def LockAndLog(conn: Connection): Unit = {
        var isLockSuccess = true
        val updStatement = conn.prepareCall(updateSql)
        try {
          updStatement.setInt(1, id /*potentially locked row ID*/)
          updStatement.execute()
        } catch {
          case e: SQLException if exec.isRowLockException(e) => println("Row is already locked"); isLockSuccess = false
        } finally {
          exec.logIt(conn, id, isLockSuccess)
        }
      }
      conn.setAutoCommit(false)
      // place for some prep-code including savepoint
      LockAndLog(conn)
      // place for some after-code including overall transaction control - commit or rollback
    }
    println(exec.getClass.getName + " test is " + check)
  }
  execLockTest(new OraExecute(), 1)
  execLockTest(new PGExecute, 1)
}

The key function is LockAndLog: we firstly try to lock the row with the lock_not_available/resource_busy exception catching, then we write result in the log_table.

Let's run the code snippet

sbt:NotesExamples> run
[info] running pol.notes.lock.TestLockException TestLockException
pol.notes.lock.OraExecute test is Success(())
pol.notes.lock.PGExecute test is Success(())
[success] Total time: 2 s, completed Aug 16, 2023, 8:47:09 PM

So far so good. And now let's check: what happens if the row lock is already set.
Blocking a row in PostgreSQL

postgres=# \set AUTOCOMMIT off
postgres=# select id from pol.type_test_table where id = 1 for update;
 id
----
  1
(1 row)

and in Oracle at once

SQL> select id from type_test_table where id = 1 for update nowait;

        ID
----------
         1

And now rerun our code

sbt:NotesExamples> run
[info] running pol.notes.lock.TestLockException TestLockException
Row is already locked
pol.notes.lock.OraExecute test is Success(())
Row is already locked
pol.notes.lock.PGExecute test is Failure(org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block)
[success] Total time: 3 s, completed Aug 16, 2023, 8:09:12 PM

Feel the difference.

We will not delve into the study of the reasons for this difference. We do migration and we need a solution.

The fastest one - jdbc driver settings

autosave (String) Default never

Let's add the property

override def getConnection(): Connection = {
  Class.forName("org.postgresql.Driver")
  val url = "jdbc:postgresql://localhost:5432/postgres?user=*****&password=*****&currentSchema=pol"
  props.setProperty("autosave", "always")
  DriverManager.getConnection(url, props)
}

and check

sbt:NotesExamples> run
[info] running pol.notes.lock.TestLockException TestLockException
pol.notes.lock.OraExecute test is Success(())
pol.notes.lock.PGExecute test is Success(())
[success] Total time: 1 s, completed Jul 24, 2023 10:18:06 PM

Voila
Everything seems to be the same now? Looks like yes. But if you have a really high-load system, pay attention to the remark:

Note autosave might result in severe performance issues for long transactions, as PostgreSQL® backend is not optimized for the case of long transactions and lots of savepoints.

https://jdbc.postgresql.org/documentation/server-prepare/

This setting will lead to work in the "sub transactions" mode for each SQL statement. In addition to cited limit the number of savepoints, using this setting can lead to a significantly higher load on the internal transaction counter and, as a result, reduce the wraparound interval.

Rewrite the code - add an explicit rollback (or rollback to savepoint) to the exception handling.

    def LockAndLog(conn: Connection): Unit = {
      var isLockSuccess = true
      val updStatement = conn.prepareCall(updateSql)
      try {
        updStatement.setInt(1, id /*potentially locked row ID*/)
        updStatement.execute()
      } catch {
        case e: SQLException if exec.isRowLockException(e) => conn.rollback(); println("Row is already locked"); isLockSuccess = false
      } finally {
        exec.logIt(conn, id, isLockSuccess)
      }
    }

Code snippets are available at https://github.com/kpoluektov/pol.notes

DEV Community: kpoluektov

Oracle to PostgreSQL code migration pain

Pessimistic exception