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 :

1. Introduce a JOIN / Reconciliation based on the recipientID field in the failing workflow
2. Explore the idea of Introducing Negative sequencing in the recipient table. It may not solve the current issue with the Update, though.
3. 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.