SA-9399

SUMMARY

Instead of INSERT and UPDATE statements in PRPC 6, Pega 7.1.7 is now generating MERGE statements which displays pyCommitError in many places in the system after upgrade.

The solution below resolved the issue partially:

Disable JDBC batch by adding the "database/batchUpdates" prconfig setting and setting its value to "-1".

The issue pertaining to FK (foreign key) constraint has been resolved after making the configuration change in prConfig xml. However, the issue persists in case of tables where a database trigger is present.

The difference between SQL queries generated is stated below:


PRPC 6.1 SP2 query:

insert into xxxx (xxx,xxx,xxx,xxx,xxxx,xxxx,xxxx,xxxx,xxxx,xxxx) values (? , ? , ? , ? , ? , ? , ? , ? , ? , ? )

Pega 7.1.7 query:

MERGE INTO dbo.xxxx t USING (VALUES (? , ? , ? , ? )) AS inputs ("xxxx" , "xxxx" , "xxxx" , "xxxx") ON (t.ClaimID = inputs."ClaimID")
WHEN MATCHED THEN UPDATE SET xxxx = inputs."xxxx" , xxxx = inputs.xxxx" , xxxx = inputs."xxxx" , xxxx = inputs."xxxx" 
WHEN NOT MATCHED THEN INSERT (xxxx , xxxx , xxxx , xxxx ) VALUES (inputs."xxxx" , inputs."xxxx" , inputs."xxxx" , inputs."xxxx");


ERROR MESSAGES

125 2015-04-13T12:57:08.311Z <n/a> XXXXXX PS1__TABTHREAD1 xxx 0.0 0.0 3333 open instance of class Rule-Message by keys Activity=FinishAssignment WORK- COMMITWITHERRORHANDLING #20141016T192731.277 GMT Step: 4 Circum: 0 cachedNotFound null Rule-Message Looking for instance Rule-Message @BASECLASS!THE MERGE STATEMENT CONFLICTED WITH THE FOREIGN KEY CONSTRAINT "FK_xxxx". THE CONFLICT OCCURRED IN DATABASE "DATABASE", TABLE "DBO.xxxx", COLUMN 'xxxx'. in the Rule Resolution cache pxRuleIOElapsed/pxRuleCPUpxRuleIOElapsed

STEPS TO REPRODUCE

Generate MERGE statements an dobserve the behaviour. 

ROOT CAUSE

In Pega 7.1.7, merge/batch is introduced as default mode for saving (deferred/immediate) instances.

In the same transaction, the instances could be grouped into multiple batches by prepared statements. The process does not necessarily maintain deterministic orders for instances. This could potentially violate execution orders wherever a mandatory order is needed (for example, foreign key constraints).

RESOLUTION

This issue is resolved through the following local change: 
Disable merge/batch by change the following prconfig settings. This requires to restart PRPC.

<env name="classmap/usemergestatement"  value ="false" />

<env name="database/batchupdates" value="-1" />