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.

Cause:  Custom Feature

On this page