Hi,
Currently, our application supports SQL Server databases and Oracle schemas. We are updating our application to support PostgreSQL schemas. We are using version 13.4 of PostgreSQL.
Our application is written in Java and connects to PostgreSQL schemas using JDBC.
In our framework, we have logic that attempts to retry an SQL statement when a row is locked. It does this up to 999 times before it gives up.
When this logic is connected using PostgreSQL schemas, we receive error messages similar to the ones below:
org.postgresql.util.PSQLException: ERROR: could not obtain lock on row in relation "my_table"
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
A Google search led to the following StackOverflow post:
hxxps://stackoverflow.com/questions/10399727/psqlexception-current-transaction-is-aborted-commands-ignored-until-end-of-tra (Replace hxxps by https to access it).
which indicates that PostgreSQL refuses to execute valid SQL statements on the same connection after an invalid SQL statement is executed.
To get around this, we have added the following arguments to the JDBC connection string:
autosave=always&cleanupSavepoints=true
This resolves the problem, but the concern we are having is in terms of performance.
Based on the following link:
hxxps://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/ (Replace hxxps by https to access it), it indicates that using this flag can have a serious impact on performance. However, it doesn’t really provide an alternative way to get around it.
In terms of real-world experience, what are the disadvantages of using the autosave and cleanupSavepoints arguments?
Would it be better to manually set the savepoint in our application code and then rollback if there is an error. This would probably have to be done for each attempt we try to lock a record, so I am not sure if there is any advantage of doing this.