Details
-
Bug
-
Resolution: Unresolved
-
Low
-
None
-
None
-
None
-
None
-
Severity 2 - Major
-
2
-
Description
Steps to reproduce
- Setup a Bitbucket Server connected to a supported Postgres database
- Create a plugin with 6 or more upgrade tasks on the same entity (e.g.: renaming a column each time)
- 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:
- Stop Bitbucket Server
- Changing the jdbc.url parameter to add a prepareThreshold parameter (e.g.: jdbc.url=jdbc:postgresql://localhost:5432/bitbucket?prepareThreshold=10)
- Start Bitbucket Server
- Install the plugin
- Stop Bitbucket Server
- Changing the jdbc.url parameter to remove the prepareThreshold parameter
- 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
- is duplicated by
-
BSERV-11430 Customers with Bitbucket 5.15 and PostgreSQL are not able to use Awesome Graphs.
- Closed
- is caused by
-
AO-697 Loading...