AIP Console - Application Scan - Snapshot generation of the application Error - null value in column pathid of relation dss_codepath violates not-null constraint

Purpose (problem description)

The page will help you to resolve the error - "ERROR: null value in column "pathid" of relation "dss_codepath" violates not-null constraint" when trying to run a snapshot from AIP Console.

Error stack logs

Error: Child task on exception
  Error while executing Procedure
    ERROR: null value in column "pathid" of relation "dss_codepath" violates not-null constraint
      Detail: Failing row contains (null, 0, 0, -2580, 58, 58, 389488).
      Where: SQL statement "insert into DSS_CodePath
      ( PathId ,StepIndex, StepDepth, SourceId, StartLine, FinishLine, ContainerId)
      select  distinct T2.Id, inkb.StepIndex, inkb.StepDepth, T01.SOURCE_ID, inkb.StartLine, inkb.FinishLine, inkb.ContainerId
      from   INKB_DataflowResults inkb

      join DIA_CODEPATH T2 on inkb.JobId = T2.JobId and inkb.MetricId= T2.MetricId and inkb.PathId = T2.PathId
      join DSS_CODE_SOURCES T01 on inkb.SourcePath=T01.SOURCE_PATH"
      PL/pgSQL function diag_codepath_dataflow(integer,integer,integer) line 31 at SQL statement
      PL/pgSQL function diag_scope_dataflow(integer,integer,integer,integer) line 16 at assignment
Return value: 1000

Observed in CAST Version

Release	Yes/No
8.3.28

Observed in RDBMS

RDBMS	Yes/No
CSS3
CSS4

Step by Step scenario

Add a new version in AIP Console
Initiate the Analysis and Snapshot

The error stack is as shown below

Using arguments:
GenerateSnapshot
-connectionProfile: <mngt_schema> on CastStorageService 
-logFilePath: D:\AIPCON\AipNode\data\logs\external_logs\92c31c0b-f682-44e7-b9e9-bcb635b50c9e\snapshot\snapshot-20230620-134429.txt
-appli: <appname>
-logRootPath: D:\CAST\Implementation\Logs
-temporaryPath: D:\CAST\Implementation\LSA
-workingPath: D:\CAST\Implementation\LSA
-snapshotInProgress: Override
-captureDate: 202306201344
-snapshot: Snapshot-2023-06-20T13-44-25
-version: Version-2023-06-19T15-01-10
-consolidateMeasures: false
-skipAnalysisJob: true
-ignoreEmptyModule: true
Log file : D:\AIPCON\AipNode\data\logs\external_logs\92c31c0b-f682-44e7-b9e9-bcb635b50c9e\snapshot\snapshot-20230620-134429.txt
Running action: Generate a Snapshot for a given Application, or for each Applications from selected Dashboard Service
starting Task Snapshot generation
starting Task Purge running snapshot in "<central_schema>"
starting Task Synchronize Services
starting Task Update SQL XXL Table Size for <app_name>
starting Task Run Data Flow Security Analysis on <app_name>
starting Task Run J2EE Data Flow Security for <app_name>
starting Task  Save results to database
starting Task Prepare snapshot in "<local_schema>"
starting Task Generate Modules in "<local_schema>"
starting Task Run CSV generation
starting Task Run extensions after prepare snapshot
starting Task Run Path builder on "<local_schema>"
starting Task Update Sources in "<local_schema>"
starting Task Finalize Data Flow Security in "<local_schema>"
starting Task Create Architecture Model in "<local_schema>"
starting Task Create Architecture Model in "<central_schema>"
starting Task Synchronize Assessment Model in <central_schema>
starting Task Create Snapshot Definition in "<central_schema>"
starting Task Compute Snapshot in "<central_schema>" with associated Analysis Services
starting Task Execution Summary
Error: Child task on exception
  Error while executing Procedure
    ERROR: null value in column "pathid" of relation "dss_codepath" violates not-null constraint
      Detail: Failing row contains (null, 0, 0, -2580, 58, 58, 389488).
      Where: SQL statement "insert into DSS_CodePath
      ( PathId ,StepIndex, StepDepth, SourceId, StartLine, FinishLine, ContainerId)
      select  distinct T2.Id, inkb.StepIndex, inkb.StepDepth, T01.SOURCE_ID, inkb.StartLine, inkb.FinishLine, inkb.ContainerId
      from   INKB_DataflowResults inkb

      join DIA_CODEPATH T2 on inkb.JobId = T2.JobId and inkb.MetricId= T2.MetricId and inkb.PathId = T2.PathId
      join DSS_CODE_SOURCES T01 on inkb.SourcePath=T01.SOURCE_PATH"
      PL/pgSQL function diag_codepath_dataflow(integer,integer,integer) line 31 at SQL statement
      PL/pgSQL function diag_scope_dataflow(integer,integer,integer,integer) line 16 at assignment
Return value: 1000

Action Plan

The issue occurs because the DIA_CODEPATH table in the local schema has been corrupted.

To solve the issue you need to run the queries provided below in the local schema and run analysis and snapshot again.

SQL QUERY

drop table if exist TMP_CODEPATH;

create table TMP_CODEPATH as
select *
  from DIA_CODEPATH;

drop table DIA_CODEPATH;

(* For below query minvalue can be fetched using select max(ID) + 1 from TMP_CODEPATH)

create sequence DIA_CODEPATH_ID_SEQ 
increment by 1 
minvalue  <*minvalue from query provided above)
start with < any value after minvalue>;

create table DIA_CODEPATH
            (
             ID integer not null default (nextval('<local_schema>.DIA_CODEPATH_ID_SEQ'::regclass)),
             JOBID integer not null,
             METRICID integer not null,
             PATHID integer not null,
             OBJECTID integer null
            );

insert into DIA_CODEPATH
      (ID, JOBID, METRICID, PATHID, OBJECTID)
select ID, JOBID, METRICID, PATHID, OBJECTID
  from TMP_CODEPATH;

create index IXL_DIA_CODEPATH on DIA_CODEPATH (JOBID asc, PATHID asc);

If the above steps do not solve your issue contact CAST Technical Support. with the following Relevant input for CAST Technical Support to carry out further investigation.

Relevant input

CAST AIP Console Logs CAST Management Studio - Information - How to find logs
Detail of specific steps done
Any applicable screenshots

Notes/comments

Ticket # 42016