Documentation

Error “Maximum value in temporary schema reached”

Last update: Wed Jun 19 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

Temporary schema throws an error after reaching the max value in sequence. Learn how to resolve this issue.

Description description

Environment

  • Adobe Campaign
  • Adobe Campaign Classic v7

Issue/Symptoms

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. 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.

Resolution resolution

Here are the steps that need to be taken to address the issue.

  • 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.

Cause

Custom Feature

recommendation-more-help
3d58f420-19b5-47a0-a122-5c9dab55ec7f