Oracle to PostgreSQL code migration pain
Tags: sql, postgres
Published: 2023-08-17 03:51:52

## 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 `nowait `part, 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