DocumentationError "Maximum value in temporary schema reached"
Error “Maximum value in temporary schema reached”
Description
Environment
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)
Resolution
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.