PGS-220000 PostgreSQL: ERROR: bigint out of range error in Campaign Classic

Learn how to solve the  PGS-220000 PostgreSQL: ERROR:  bigint out of range error in Campaign Classic using execution instance ID .

Description

Environment
Adobe Campaign Classic v7

Issue
The following error runs in the archive workflow (mcSynch) from Message Center:

Resolution

Solution
The issue is the execution instance ID that has been used to identify the RT execution instance.

As shown above, the value used is 50,805,170.

The execution instance ID is used by the archiving workflow as follows:

vars.CELL_OFFSET + “*” + vars.cellId + " + @id"

Where

These two numbers above are multiplied together. The end-result number is bigger than the maximum value of the Postgres bigint type (PostgreSQL Numeric Types).

The two values are added to the query as follows:

SELECT    :#(1)# * :#(2)# + B0.iBatchEventId, B0.iBatchEventId, B0.iStatus, B0.iRetry, B0.tsSubmit, B0.tsScheduled, B0.tsExpiration, B0.tsCreated, B0.tsProcessing, B0.tsProcessed, B0.tsLastModified, B0.sType, B0.iDeliveryId, B0.iWishedChannel, B0.sExternalId, B0.sOrigin, B0.sEmail, B0.iEmailFormat, B0.sMobilePhone, B0.sRegistrationToken, B0.iMobileAppId, B0.sLine1, B0.sLine2, B0.sLine3, B0.sLine4, B0.sLine5, B0.sLine6, B0.sLine7, B0.sCountryCode, B0.sLanguage, B0.iFolderId, B0.iCreatedById, B0.mData
  FROM NmsBatchEvent B0
 WHERE ((B0.iFolderId IN (SELECT xr.iFolderId FROM XtkRights xr WHERE xr.iOperatorId IN (3798)) OR B0.iFolderId=0))
   AND (((B0.iFolderId IN (SELECT xr.iFolderId FROM XtkRights xr WHERE xr.iOperatorId IN (3798)) OR B0.iFolderId=0))
   AND ((B0.iBatchEventId < > :#(3)#) AND (B0.tsLastModified < :#(4)#)))
 ORDER BY truncdate(B0.tsLastModified, :#(5)#, ‘Europe/London’), B0.iBatchEventId LIMIT 501’

Param(0)=72057594037927936\n  —> :#(1)#

Param(1)=50805170\n  —> :#(2)#

Param(2)=0\n  Param(3)=09/04/2021 05:00:26\n  Param(4)=1

The solution is to utilize a smaller number as the execution instance ID.