Error “Maximum value in temporary schema reached”
Description
Issue Description:
Temporary Schema has reached the max value in sequence and getting 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
Objective: To check the temporary schema and its function on why the sequence is failing
Environment: Adobe Campaign Classic
Cause: Custom Feature
Resolution:
Below Steps were investigated:
- The workflow in question does an update 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 not an option.
- Since the ID of the NMS: 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.
Solutions :
- Introduce a JOIN / Reconciliation based on the recipientID field in the failing workflow
- Explore the idea of Introducing Negative sequencing in the recipient table. It may not solve the current issue with the Update, though.
- Move the OOB recipient table from 32 bit to 64 bit – Not possible due to code limitations in Campaign V6/V7. They are planned for V8 already.