PGJDBC Autosave Quirk: Shared Memory Errors and a Cleaner Workaround

Using the PostgreSQL JDBC driver's connection parameter autosave=always can trigger "ERROR: out of shared memory" (#1407) in certain scenarios. The cause is an excessive number of locks with the locktype transactionid. While bumping the server-side max_locks_per_transaction parameter may serve as a stopgap, a more effective approach exists.

The recommended solution is to pair autosave=always with the additional connection parameter cleanupSavepoints=true. This setting automatically releases the artificial PGJDBC_AUTOSAVE savepoints after each statement execution, which subsequently drops the associated transactionid locks that were piling up.

It is worth noting that cleanupSavepoints=true previously caused problems with OID/BLOB columns (issue #3910), but those defects have been resolved in driver version v42.7.10 and later.

  • Performance boost: Measurements show that combining autosave=always with cleanupSavepoints=true delivers "significantly better performance" than using autosave=always alone, especially for batch transactions containing several thousand statements.
  • Known limitation: The cleanupSavepoints=true parameter interferes with manual savepoint management. User-defined savepoints will not function properly when it is enabled (#1834).