PGS-220000 PostgreSQL error: relation “t1” does not exist
If you are getting an error: PGS-220000 PostgreSQL error: relation "t1" does not exist..., generated by the tracking workflow in the Adobe Campaign Classic v7, go through this article to understand how to resolve this issue.
Description description
Environment
Campaign Classic V7
Issue/Symptoms
After a recent build upgrade 9343 or higher, the following errors are generated by the tracking workflow:
nlmodule PGS-220000 PostgreSQL error: ERROR: relation "t1" does not exist LINE 1: ...t, T3.iArticle, T2.iType FROM NmsTrackingUrl T2, T1 LEFT OU... nlmodule WDB-200001 SQL statement 'SELECT T1.tsLog, T1.iDeliveryId, T1.iUrlId, T1.sSourceType, T3.dAmount, T3.iArticle, T2.iType FROM NmsTrackingUrl T2, T1 LEFT OUTER JOIN NmsWebTrackingLog T3 ON iTrackingLogId = iWebTrackingLogId WHERE T1.tsLog < :#(1)# AND T1.tsLog >= :#(2)# AND T1.iUrlId=T2.iTrackingUrlId ORDER BY T1.tsLog' could not be executed. Param(0)=09/17/2020 8:55:00 PM Param(1)=09/16/2020 8:55:00 PMnlmodule Attribute 'broadLog-id' unknown (see definition of schema 'Tracking logs (nms:trackingLog)').
Resolution resolution
From the verbose tracking logs, one can see that the tracking workflow is failing at the end, of consolidating the data:
00000000 00000000 1 info log Consolidating tracking logs for the period [ 09/16/2020 3:55:00 PM, 09/17/2020 3:55:00 PM[ ...00000000 00000000 1 info wdbc Select: [ 00000000 ] SELECT T1.tsLog, T1.iDeliveryId, T1.iUrlId, T1.sSourceType, T3.dAmount, T3.iArticle, T2.iType FROM NmsTrackingUrl T2, T1 LEFT OUTER JOIN NmsWebTrackingLog T3 ON iTrackingLogId = iWebTrackingLogId WHERE T1.tsLog < :#(1)# AND T1.tsLog >= :#(2)# AND T1.iUrlId=T2.iTrackingUrlId ORDER BY T1.tsLog00000000 00000000 2 info wdbc Param(0)=09/17/2020 8:55:00 PM00000000 00000000 2 info wdbc Param(1)=09/16/2020 8:55:00 PM00000000 00000000 1 error log PGS-220000 PostgreSQL error: ERROR: relation "t1" does not exist\nLINE 1: ...t, T3.iArticle, T2.iType FROM NmsTrackingUrl T2, T1 LEFT OU...\n \n. (iRc=-2006)00000000 00000000 1 error log WDB-200001 SQL statement 'SELECT T1.tsLog, T1.iDeliveryId, T1.iUrlId, T1.sSourceType, T3.dAmount, T3.iArticle, T2.iType FROM NmsTrackingUrl T2, T1 LEFT OUTER JOIN NmsWebTrackingLog T3 ON iTrackingLogId = iWebTrackingLogId WHERE T1.tsLog < :#(1)# AND T1.tsLog >= :#(2)# AND T1.iUrlId=T2.iTrackingUrlId ORDER BY T1.tsLog' could not be executed.\n Param(0)=09/17/2020 8:55:00 PM\n Param(1)=09/16/2020 8:55:00 PM (iRc=-2006)
Every time the tracking workflow starts, it pulls all target mappings and updates the data based on the configuration.
In this case, one target mapping is causing the issue. Please see below:
Basically, NMS: tracking log schema should NOT be used for monitoring logs, because the schema is not related to a database (SQL) table.
In conclusion, the issue has nothing to do with the upgrade, but more with a bad target mapping configuration.