Performance question about using autosave=always and cleanupSavepoints=true

On Tue, 16 Nov 2021 at 11:09, Joel Rabinovitch <Joel.Rabinovitch@tecsys.com>
wrote:

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
<https://stackoverflow.com/questions/10399727/psqlexception-current-transaction-is-aborted-commands-ignored-until-end-of-tra&gt;
(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/
<https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/&gt; (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.

Adding the autosave and cleanupSavepoints is a workaround that allows you
to use your current code without changes. It does come with a performance
penalty as you have discovered. I'm afraid the only way to really address
the performance is to deal with the first problem correctly by retrying
transactions when they fail instead of using savepoints.

Regards,
Dave