Uploaded image for project: 'Bitbucket Server'
  1. Bitbucket Server
  2. BSERV-11441

Multiple AO upgrade tasks fail on Postgres



    • Type: Bug
    • Status: Gathering Impact (View Workflow)
    • Priority: Low
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:


      Steps to reproduce

      1. Setup a Bitbucket Server connected to a supported Postgres database
      2. Create a plugin with 6 or more upgrade tasks on the same entity (e.g.: renaming a column each time)
      3. Install the plugin on the running Bitbucket Server instance

      Expected: The plugin installs without any problems

      Actual: The plugin fails to be installed and a PSQLException can be found in the logs with the message: ERROR: cached plan must not change result type

      Caused by: org.postgresql.util.PSQLException: ERROR: cached plan must not change result type
              at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
              at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
              at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
              at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
              at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
              at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
              at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:117)
              at net.java.ao.schema.helper.DatabaseMetaDataReaderImpl.getResultSetMetaData(DatabaseMetaDataReaderImpl.java:265)
              at net.java.ao.schema.helper.DatabaseMetaDataReaderImpl.getFields(DatabaseMetaDataReaderImpl.java:89)
              ... 37 common frames omitted

      Detailed description

      Postgres databases can use server side prepared statements as a performance optimisation for query execution.

      The Postgres JDBC driver supports server side prepared statements. Users can configure a prepareThreshold and once a query has been executed more than the specified threshold (5 by default) the driver will start running it with server side prepared statements.

      However server side prepared statements don't support queries of the type SELECT * ... executed in the same transaction as a DDL query (e.g.: ALTER <TABLE_NAME> ADD column ...). This causes an error with the message: "cached plan must not change result type".

      While running upgrade tasks, Active Objects will run SELECT * FROM <TABLE_NAME> LIMIT 1 queries in order to "read the schema" causing the above mentioned error when the table is migrated.


      Customers who are running into this issue while trying to install a plugin can, as a workaround, increase the value of the prepared threshold to complete the installation. This will require:

      1. Stop Bitbucket Server
      2. Changing the jdbc.url parameter to add a prepareThreshold parameter (e.g.: jdbc.url=jdbc:postgresql://localhost:5432/bitbucket?prepareThreshold=10)
      3. Start Bitbucket Server
      4. Install the plugin
      5. Stop Bitbucket Server
      6. Changing the jdbc.url parameter to remove the prepareThreshold parameter
      7. Start Bitbucket Server

      NOTE: Server side prepared statements are a performance optimisation and there may performance impacts when running with a value which differs from the default configuration**

      Tested on Bitbucket Server 5.15 with Postgres 9.6.


          Issue Links



              jpalacios Juan Palacios
              4 Vote for this issue
              5 Start watching this issue