search

datanucleus / datanucleus-rdbms

DataNucleus support for persistence to RDBMS Datastores
30 stars 67 forks source link

PostgreSQLAdapter : change default IsolationForSchemaCreation to READ_COMMITTED #478

Closed mingmxu closed 1 year ago

mingmxu commented 1 year ago

what's the problem

It's the same as described in https://github.com/datanucleus/datanucleus-rdbms/issues/336.

When initionalizing Hive Metastore service on a replication DB of Postgres, it fails with error message as below:

NestedThrowablesStackTrace:
org.postgresql.util.PSQLException: ERROR: cannot use serializable mode in a hot standby
  Detail: "default_transaction_isolation" is set to "serializable".
  Hint: You can use "SET default_transaction_isolation = 'repeatable read'" to change the default.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
        at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:174)
        at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeQuery(ParamLoggingPreparedStatement.java:375)
        at org.datanucleus.store.rdbms.SQLController.executeStatementQuery(SQLController.java:552)
        at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:617)

What's the solution

Similar as OracleAdapter and DB2AS400Adapter, this PR override the default value(TRANSACTION_SERIALIZABLE defined in BaseDatastoreAdapter), with TRANSACTION_READ_COMMITTED which is compatible with Postgres replication DB.

What's the risk

This method is only used by ClassAdder in RDBMSStoreManager to initialize DatastoreAdapter, the risk should be low as it's not used in user queries.

cc @andyjefferson