Re: Performance question about using autosave=always and cleanupSavepoints=true

On Tue, 16 Nov 2021 at 11:09, Joel Rabinovitch <Joel(dot)Rabinovitch(at)tecsys(dot)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>
> (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/> (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