Error “Maximum value in temporary schema reached”
Temporary Schema has reached the max value in sequence and shows the below error:
writer pient R2 ON (R2.iRecipientId = R1.iRecipientID) ON (R2.sEmail = R3.sEmail)’ could not be executed.
writer 5, AddDays(GetDate() , 1), W0.iId1, W0.iType, W0.iStatus, W0.tsCreated, W0.mQuarantineText, W0.iQuarantineReason, W0.iId FROM wkfxxxxx_xxxx_1 W0 JOIN NmsRecipient R3 ON (R3.iRecipientId = W0.iId) LEFT JOIN SsgRecipientsProfile R1 LEFT JOIN NmsReci
writer WDB-200001 SQL statement 'INSERT INTO wkfxxxxx_4968_1 (iRecTrnState, iPKey1, iPKey_1, iFldxxxxxx, tsFldxxxxxx, iSrc7, iSrc6, iSrc5, tsSrc4, mSrc3, iSrc2, iSrc1) SELECT 0, case when W0.iId 0 then W0.iId else NULL end, R1.iRecipientID, 14
writer PGS-220000 PostgreSQL error: ERROR: nextval: reached maximum value of sequence “wkfxxxxx_xxxx_1_irecprocstate_seq” (xxxxx)
the steps given below were investigated:
- The workflow in question updates without any relation/reconciliation with the table’s primary key causing the issue.
- The same table inserts data when the recipient is manually supplied in the workflow.
- The sequence name in the workflow logs is misleading as it shows a temporary sequence. We can confirm that the temp sequence is not exhausted.
- The solution for changing the data type to int64 bit will work in this workflow, but the corresponding INSERT workflows will start to fail. So that is not an option.
- Since the ID of the NMS: the recipient Table is used to populate the SSG: recipients profile table, adding a new column called Primary key and assigning a sequence will not work.
The solutions :
- Introduce a JOIN / Reconciliation based on the
recipientID field in the failing workflow
- Explore the idea of introducing a negative sequence in the recipient table. It might still not resolve the current issue with the Update.
- Move the OOB recipient table from 32 bit to 64 bit – this is not possible due to code limitations in Campaign V6/V7. They are planned for V8 already.
Cause: Custom Feature