Troubleshooting PostgreSQL Error When Upgrading: "cached plan must not change result type"

Introduction

On PostgreSQL, when upgrading the plugin, you may encounter the following condition:

1. The plugin upgrade appears to complete successfully

2. The plugin is not usable and plugin pages return errors

3. Stack traces in the Bitbucket logs contain the error cached plan must not change result type

Cause

This condition is a known issue in the way Bitbucket executes database migrations for upgrading applications on PostgreSQL databases:

• AO-697: After schema changes ActiveObjects yields exception "cached plan must not change result type" with PostgreSQL - JDBC driver 9.4

• BSERV-11441: Multiple AO upgrade tasks fail on Postgres

An issue exists in the code Atlassian uses to migrate plugin data stores that causes certain migration statements to be prepared and executed improperly. When this happens, Bitbucket will mark Soteri Security for Bitbucket’s database connection as broken and prevent any more interaction with the Bitbucket data store.

More information on PostgreSQL prepared statements can be found here. If the underlying table’s schema changes between the PREPARE and EXEC statements, a cached plan must not change result type error will be thrown.

Remediation

Plugin data store upgrades can take some time to complete on large instances. Soteri Security for Bitbucket will write Bitbucket log messages such as [soteri] Starting upgrade to ... and [soteri] Finished upgrade to ... - when troubleshooting this issue, these log lines can be used to indicate progress. Do not disable the plugin while upgrades are in progress.

1. Disable and re-enable the plugin. Generally, the migration properly upgrades the data store and the plugin can start normally. If you are upgrading many versions, you may need to do this multiple times until the plugin works.

2. If this doesn’t work you can try adding prepareThreshold=0 to your JDBC connection URI. You can find this in your bitbucket.properties file, under jdbc.url.

   • Setting this threshold to 0 hurts overall Bitbucket performance. It should be undone after updating Security for Bitbucket.

3. If none of these solutions fix your issue, collect a support zip and attach it to a new ticket at https://support.soteri.io