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

Multiple AO upgrade tasks fail on Postgres

    XMLWordPrintable

    Details

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

      Description

      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.

      Workaround

      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.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                4 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated: