PostgreSQL driver 42.2.11 Exception: Problem committing a transaction

[dcasavant]

Describe the bug
The recently released PostgreSQL driver version 42.2.11 contains a change in behavior which is resulting in an exception that stops Platform startup. 42.2.10 does not exhibit this.

Looks like this is related to the following change in 42.2.11: pgjdbc/pgjdbc#1729

This is reverted for the next patch (42.2.12) but will be re-introduced in 42.3.0: “This reverts commit adcb194. we still want to do this but it is a breaking change and we will introduce this change in 42.3.0”. Therefore the issue will still need to be fixed.

To Reproduce
Startup a system using PostgreSQL driver version 42.2.11

Logs

20-03-20 13:34:26,294 [os-app1.bounceme.net] [          ] [                    ] [                    ] (ernal.store.ManagedTransaction) ERROR   - There was a problem committing a transaction on database pegarules
com.pega.pegarules.pub.database.DatabaseException: Database-General    There was a problem committing a transaction on database pegarules    0    25P02    The database returned ROLLBACK, so the transaction cannot be committed. Transaction failure cause is <<ERROR: duplicate key value violates unique constraint "pr_sys_ruleset_index_pk"
  Detail: Key (pzinskey)=(SYSTEM-RULESET-INDEX C5F8D11E1C6712B822BE592273FD678F!PEGA-PROCESSCOMMANDER) already exists.>>
DatabaseException caused by prior exception: org.postgresql.util.PSQLException: The database returned ROLLBACK, so the transaction cannot be committed. Transaction failure cause is <<ERROR: duplicate key value violates unique constraint "pr_sys_ruleset_index_pk"
  Detail: Key (pzinskey)=(SYSTEM-RULESET-INDEX C5F8D11E1C6712B822BE592273FD678F!PEGA-PROCESSCOMMANDER) already exists.>>
 | SQL Code: 0 | SQL State: 25P02

    at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:384) ~[prprivate-data.jar:?]
    at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:363) ~[prprivate-data.jar:?]
    at com.pega.pegarules.data.internal.store.ManagedTransaction.commit(ManagedTransaction.java:422) ~[prprivate-data.jar:?]
    at com.pega.pegarules.data.internal.store.DataStoreManager.commit(DataStoreManager.java:344) ~[prprivate-data.jar:?]
    at com.pega.pegarules.data.internal.store.DataStoreManager.doInTransaction(DataStoreManager.java:217) ~[prprivate-data.jar:?]
    at com.pega.pegarules.data.internal.access.SaverImpl.save(SaverImpl.java:234) ~[prprivate-data.jar:?]
    at com.pega.pegarules.data.internal.access.SaverImpl.saveAllOrNone(SaverImpl.java:128) ~[prprivate-data.jar:?]
    at com.pega.pegarules.session.internal.authorization.context.RulesetIndexWriter.persistRSLRows(RulesetIndexWriter.java:425) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.context.RulesetIndexWriter.writeIndexRows(RulesetIndexWriter.java:297) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.context.RulesetIndexWriter.writeRuleSetListHashToDB(RulesetIndexWriter.java:211) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.context.LocalizedApplicationContextImmutableImpl.<init>(LocalizedApplicationContextImmutableImpl.java:155) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.context.ApplicationContextFactory.createLocalizedApplicationContext(ApplicationContextFactory.java:92) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.context.GlobalContextCache.getLocalizedApplicationContext(GlobalContextCache.java:456) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.context.AuthorizationContextManagerImpl.getContext(AuthorizationContextManagerImpl.java:109) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.SessionAuthorization.getContext(SessionAuthorization.java:497) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.SessionAuthorization.getContext(SessionAuthorization.java:441) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.SessionAuthorization.reset(SessionAuthorization.java:376) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.SessionAuthorization.<init>(SessionAuthorization.java:147) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.PRRequestorBase.allocateSessionAuthorization(PRRequestorBase.java:548) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.PRRequestorImpl.allocateSessionAuthorization(PRRequestorImpl.java:2289) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.Authorization.reset(Authorization.java:258) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.Authorization.reset(Authorization.java:196) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.authorization.Authorization.onBeforeThreadUse(Authorization.java:1779) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.base.ThreadPassivation.configureThreadImpl(ThreadPassivation.java:344) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.PRThreadBase.configureThread(PRThreadBase.java:184) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.PRThreadImpl.<init>(PRThreadImpl.java:157) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.PRThreadImpl.acquire(PRThreadImpl.java:182) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.base.RequestorThreadSync.getOrCreateThread(RequestorThreadSync.java:195) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.base.RequestorThreadSync.getOrCreateThread(RequestorThreadSync.java:171) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.base.RequestorThreadSync.getOrCreateThread(RequestorThreadSync.java:167) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.base.RequestorPassivation.configureRequestorImpl(RequestorPassivation.java:510) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.PRRequestorBase.configureRequestor(PRRequestorBase.java:491) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.PRRequestorImpl.<init>(PRRequestorImpl.java:332) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.PRRequestorImpl.acquire(PRRequestorImpl.java:353) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.base.MultiThreadRequestorFactory.acquire(MultiThreadRequestorFactory.java:76) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.base.NodeRequestorMgt.createRequestorImpl(NodeRequestorMgt.java:1671) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.base.NodeRequestorMgt.createRequestorImpl(NodeRequestorMgt.java:1650) ~[prprivate-session.jar:?]
    at com.pega.pegarules.session.internal.mgmt.PRNodeImpl.initializeSystem(PRNodeImpl.java:878) ~[prprivate-session.jar:?]

Pega internal ref BUG-549667

[pega-roska]

FYI: There is a raiseExceptionOnSilentRollback property that you can set to false to force the old behavior if you need a more immediate workaround:

jdbc:postgresql://hostname:5432/dbName?raiseExceptionOnSilentRollback=false

[pega-talba]

Originally, Postgres driver version 42.3.0 was going to re-introduce these changes. However, this did not actually happen, and now we're at 42.3.3 and the changes were never re-introduced into the driver. It's possible that they've decided the changes are no longer worth it, or Postgres itself rendered the changes unimportant.

Closing this issue as the driver doesn't seem like it's going to introduce this change again. If it comes up again it may manifest differently and use a different setting name regardless, so there is not much we can do in advance.