Server Manager - Installing Extensions - Error null value in column link_type_str of relation delta_link violates not-null constraint

Created by Sumit Kumar Sinha, last modified by Alex Thottunkel on Feb 17, 2023
Description

 This page will help to solve the incident when the error  "Error null value in column "link_type_str" of relation "delta_link" violates not-null constraint" is generated while installing extensions either through servman manually or while running install extension step in AIP Console.



Observed in CAST AIP

Release

Yes/No

8.3.x(tick) 
Observed on RDBMS

RDBMS

Yes/No

CSS(tick) 
Step by Step Scenario
  • Add a version in AIP Console
  • Errors out at the Install extension step while running File: c:\program files\cast\8.3\installscripts\caststorageservice\deltatools\CollectDeltaAnalysisBefore.sql

The error is SQL Error: ERROR: null value in column "link_type_str" of relation "delta_link" violates not-null constraint.



LOGS
files\cast\8.3\installscripts\caststorageservice\deltatools\CollectDeltaAnalysisBefore.sql 0 ; 0 0 0 [Module name] 0 0 files\cast\8.3\installscripts\caststorageservice\deltatools\CollectDeltaAnalysisBefore.sql 0 ; 0 0 0 [Module name] 0 0  2023-01-24 06:13:40.461266 Information MODULMSG ; Job execution Playing File : c:\program files\cast\8.3\installscripts\caststorageservice\deltatools\CollectDeltaAnalysisBefore.sql (DELTATOOL_ANALYSIS_DATA,Refresh) 0 ; 0 0 0 [Module name] 0 0  2023-01-24 06:13:45.564568 Error MODULMSG ; Job execution SQL Error: ERROR:  null value in column "link_type_str" of relation "delta_link" violates not-null constraint.
0 ; 0 0 servman\smsqlstart.cpp 139 [Module name] 0 0  2023-01-24 06:13:45.565574 Error MODULMSG ; Job execution SQL Error: DETAIL:  Failing row contains (12, B, 137746, 2099200, 0, 367, 1, Javascript Client Side Method, null, Struts Action Mapping, WEB)..
0 ; 0 0 servman\smsqlstart.cpp 139 [Module name] 0 0  2023-01-24 06:13:45.565574 Error MODULMSG ; Job execution SQL Error: CONTEXT:  SQL statement "insert into DELTA_LINK (ID,TYPE,CALLER_TYPE,LINK_TYPE_LO,LINK_TYPE_HI,CALLED_TYPE,VALUE,CALLER_TYPE_STR,LINK_TYPE_STR,CALLED_TYPE_STR,LANGUAGE).
0 ; 0 0 servman\smsqlstart.cpp 139 [Module name] 0 0  2023-01-24 06:13:45.566568 Error MODULMSG ; Job execution SQL Error:   select p_id,p_delta_type,o.CALLER_TYPE,o.LINK_TYPE_LO,o.LINK_TYPE_HI,o.CALLED_TYPE,o.VALUE,t1.objtypstr.
0 ; 0 0 servman\smsqlstart.cpp 139 [Module name] 0 0  2023-01-24 06:13:45.567567 Error MODULMSG ; Job execution SQL Error:     ,(select COALESCE(staticdesc,'xxx') from csv_linktype LT where LT.acctyplo = o.LINK_TYPE_LO and LT.acctyphi = o.LINK_TYPE_HI).


  • In ServMan: Add extensions and you will get the below message in the servman logs:




Impact of the Problem

The impact of the problem is :

The install extension step fails in AIP Console, causing add a version to fail.

The install extension step is blocked in Servman.

Action Plan

To fix the problem, proceed as follows:

  1. Please run the query below, it should return no rows for mngt, central, and knowledge base schemas 

    select *
  from sys_package_version
where package_name = '/com.castsoftware.labs.deltareportanalysis'
Here is below the query to execute for unregistering the extension when the query above returns a row
 
delete
  from sys_package_version
where package_name = '/com.castsoftware.labs.deltareportanalysis'
  2. Now, delete the extension using AIP Console / Blacklist the extension in AIP Console. Please refer Blacklisting extensions
  3. Remove the extension from the extensions tab.

  4. Now run the below query to recreate the function in the knowledge base: 

    Set search_path to <schemaname_local>
    CREATE OR REPLACE FUNCTION delta_analyis_report (p_id integer, p_delta_type character varying)
RETURNS void as
$body$
declare
L_ID_MAX integer := 0;
Begin
  if (p_delta_type <> 'M') then  
    insert into DELTATOOL_ID (ID, TYPE, NAME, VALUE)
    select p_id, p_delta_type, 'idkey', COALESCE(max(idkey), 0) from keys;

    insert into DELTATOOL_ID (ID, TYPE, NAME, VALUE)
    select p_id, p_delta_type, 'idacc', COALESCE(max(idacc), 0) from acc;

    select max(idkey)
    into L_ID_MAX
    from keys;
  else
    select VALUE
    into L_ID_MAX
    from DELTATOOL_ID
    where ID = p_id and TYPE = 'B' and NAME = 'idkey';
  end if;

  perform droptemporarytable('deltatool_wk_object');
  create temporary table deltatool_wk_object (OBJECT_TYPE integer not null, VALUE integer not null );

  insert into DELTATOOL_WK_OBJECT (OBJECT_TYPE, VALUE)
  select k.objtyp, count(1)
  from keys k
  where k.idkey <= L_ID_MAX
  and exists (select 1 from objpro op where op.idobj = k.idkey)
  group by k.objtyp;

  perform UPDATEOBJECTSTATISTICS ('DELTATOOL_WK_OBJECT', 2);

  insert into DELTATOOL_OBJECT (ID, TYPE, OBJECT_TYPE, VALUE, OBJECT_TYPE_STR, LANGUAGE)
  select p_id, p_delta_type, o.OBJECT_TYPE, o.VALUE, t.objtypstr, t.lngstr
  from DELTATOOL_WK_OBJECT o
    join objtypstr t on (t.objtyp = o.OBJECT_TYPE);

  perform droptemporarytable('deltatool_wk_object');  

  perform droptemporarytable('deltatool_wk_props');
  create temporary table DELTATOOL_WK_PROPS (OBJECT_TYPE integer not null, PROP_TYPE integer not null, PROP_SUB_TYPE integer not null, VALUE integer not null);

  insert into DELTATOOL_WK_PROPS (OBJECT_TYPE, PROP_TYPE, PROP_SUB_TYPE, VALUE)
  select k.objtyp, T1.inftyp, T1.infsubtyp, count(1)
  from keys k
    join objdsc T1 on (T1.idobj = k.idkey)
  where k.idkey <= L_ID_MAX
  and exists (select 1 from objpro op where op.idobj = k.idkey)
  group by k.objtyp, T1.inftyp, T1.infsubtyp;

  create index DELTATOOL_WK_PROPS_idx2 on DELTATOOL_WK_PROPS(PROP_TYPE, PROP_SUB_TYPE);
  perform UPDATEOBJECTSTATISTICS ('DELTATOOL_WK_PROPS', 2);

  insert into DELTATOOL_PROPS (ID, TYPE, OBJECT_TYPE, PROP_TYPE, PROP_SUB_TYPE, VALUE, OBJECT_TYPE_STR, PROP_TYPE_STR, LANGUAGE)
  select p_id, p_delta_type, o.OBJECT_TYPE, o.PROP_TYPE, o.PROP_SUB_TYPE, o.VALUE, t.objtypstr, d.dsc, t.lngstr
  from DELTATOOL_WK_PROPS o
    join objtypstr t on (t.objtyp = o.OBJECT_TYPE)
    join objdscref d on (d.inftyp = o.PROP_TYPE and d.infsubtyp = o.PROP_SUB_TYPE);

  perform droptemporarytable('deltatool_wk_props');

  perform droptemporarytable('deltatool_wk_propn_excluded');
  create temporary table DELTATOOL_WK_PROPN_EXCLUDED (OBJECT_TYPE integer not null, PROP_TYPE integer not null, PROP_SUB_TYPE integer not null);
  insert into DELTATOOL_WK_PROPN_EXCLUDED (OBJECT_TYPE, PROP_TYPE, PROP_SUB_TYPE) 
  select k.objtyp, T1.inftyp, T1.infsubtyp
  from keys k
    join objinf T1 on (T1.idobj = k.idkey)
  where k.idkey <= L_ID_MAX
  and not ((T1.inftyp = 3 and T1.infsubtyp = 0) or (T1.inftyp = 4 and T1.infsubtyp = 0))
  and exists (select 1 from objpro op where op.idobj = k.idkey)
  group by k.objtyp, T1.inftyp, T1.infsubtyp
  having sum(T1.infval) > 2147483647 or sum(T1.infval) < -2147483648;

  create index deltatool_wk_propn_excluded_idx on DELTATOOL_WK_PROPN_EXCLUDED(OBJECT_TYPE, PROP_TYPE, PROP_SUB_TYPE);
  perform UPDATEOBJECTSTATISTICS ('DELTATOOL_WK_PROPN_EXCLUDED', 2);

  perform droptemporarytable('deltatool_wk_propn');
  create temporary table DELTATOOL_WK_PROPN (OBJECT_TYPE integer not null, PROP_TYPE integer not null, PROP_SUB_TYPE integer not null, VALUE integer not null, TOTAL integer not null); 

  insert into DELTATOOL_WK_PROPN (OBJECT_TYPE, PROP_TYPE, PROP_SUB_TYPE, VALUE, TOTAL)
  select k.objtyp, T1.inftyp, T1.infsubtyp, count(1), 0 -- sum is higher or lower than max value for integer
  from keys k
    join objinf T1 on (T1.idobj = k.idkey)
    join DELTATOOL_WK_PROPN_EXCLUDED T2 on (T2.OBJECT_TYPE = k.objtyp and T2.PROP_TYPE = T1.inftyp and T2.PROP_SUB_TYPE = T1.infsubtyp)
  where k.idkey <= L_ID_MAX
  and not ((T1.inftyp = 3 and T1.infsubtyp = 0) or (T1.inftyp = 4 and T1.infsubtyp = 0))
  and exists (select 1 from objpro op where op.idobj = k.idkey)
  group by k.objtyp, T1.inftyp, T1.infsubtyp;

  insert into DELTATOOL_WK_PROPN (OBJECT_TYPE, PROP_TYPE, PROP_SUB_TYPE, VALUE, TOTAL)
  select k.objtyp, T1.inftyp, T1.infsubtyp, count(1), sum(T1.infval)
  from keys k
    join objinf T1 on (T1.idobj = k.idkey)
  where k.idkey <= L_ID_MAX
  and not ((T1.inftyp = 3 and T1.infsubtyp = 0) or (T1.inftyp = 4 and T1.infsubtyp = 0))
  and exists (select 1 from objpro op where op.idobj = k.idkey)
  and not exists (select 1 from DELTATOOL_WK_PROPN_EXCLUDED T2 where T2.OBJECT_TYPE = k.objtyp and T2.PROP_TYPE = T1.inftyp and T2.PROP_SUB_TYPE = T1.infsubtyp)
  group by k.objtyp, T1.inftyp, T1.infsubtyp;

  create index DELTATOOL_WK_PROPN_idx2 on DELTATOOL_WK_PROPN(PROP_TYPE, PROP_SUB_TYPE);
  perform UPDATEOBJECTSTATISTICS ('DELTATOOL_WK_PROPN', 2);

  perform droptemporarytable('deltatool_wk_propn_excluded');

  insert into DELTATOOL_PROPN (ID, TYPE, OBJECT_TYPE, PROP_TYPE, PROP_SUB_TYPE, VALUE, TOTAL, OBJECT_TYPE_STR, PROP_TYPE_STR, LANGUAGE)
  select p_id, p_delta_type, o.OBJECT_TYPE, o.PROP_TYPE, o.PROP_SUB_TYPE, o.VALUE, o.TOTAL, t.objtypstr, d.dsc, t.lngstr
  from DELTATOOL_WK_PROPN o
    join objtypstr t on (t.objtyp = o.OBJECT_TYPE)
    join objdscref d on (d.inftyp = o.PROP_TYPE and d.infsubtyp = o.PROP_SUB_TYPE);

  perform droptemporarytable('deltatool_wk_propn');

  perform droptemporarytable('deltatool_wk_linktyp');
  create temporary table deltatool_wk_linktyp (acctyplo integer not null, acctyphi integer not null);

  insert into deltatool_wk_linktyp (acctyplo, acctyphi)
  select acctyplo, acctyphi
  from objtypstr o 
  join linktyp l on (l.linktyp = o.objtyp);

  perform droptemporarytable('DELTATOOL_WK_LINK_SUB');

  create temporary table DELTATOOL_WK_LINK_SUB (objtyp INTEGER not null, acctyplo INTEGER not null, acctyphi INTEGER not null, objtyp2 INTEGER not null); 

  INSERT INTO DELTATOOL_WK_LINK_SUB(objtyp, acctyplo, acctyphi, objtyp2) 
  select k1.objtyp, a.acctyplo, a.acctyphi ,k2.objtyp 
  from keys k1
    join acc a on (a.idclr = k1.idkey and a.accknd = 0 and a.prop = 0)
    join keys k2 on (k2.idkey = a.idcle)
  where k1.idkey <= L_ID_MAX
  and k2.idkey <= L_ID_MAX
  and exists (select 1 from objpro op where op.idobj = k1.idkey)
  and exists (select 1 from objpro op where op.idobj = k2.idkey);

  create index DELTATOOL_WK_LINK_SUB_idx1 on DELTATOOL_WK_LINK_SUB(acctyplo, acctyphi);

  perform droptemporarytable('deltatool_wk_link');

  create temporary table DELTATOOL_WK_LINK (CALLER_TYPE INTEGER not null, LINK_TYPE_LO INTEGER not null, LINK_TYPE_HI INTEGER not null, CALLED_TYPE INTEGER not null, VALUE INTEGER not null); 

  insert into DELTATOOL_WK_LINK (CALLER_TYPE, LINK_TYPE_LO, LINK_TYPE_HI, CALLED_TYPE, VALUE)
  select a.objtyp, l.acctyplo, l.acctyphi, a.objtyp2, count(1)
  FROM DELTATOOL_WK_LINK_SUB as a
	join deltatool_wk_linktyp l on (a.acctyplo & l.acctyplo = l.acctyplo and a.acctyphi & l.acctyphi = l.acctyphi)
  group by a.objtyp, l.acctyplo, l.acctyphi, a.objtyp2;

   perform droptemporarytable('deltatool_wk_linktyp');
   perform droptemporarytable('DELTATOOL_WK_LINK_SUB');

  create index DELTATOOL_WK_LINK_idx1 on DELTATOOL_WK_LINK(CALLER_TYPE);
  create index DELTATOOL_WK_LINK_idx2 on DELTATOOL_WK_LINK(CALLED_TYPE);
  create index DELTATOOL_WK_LINK_idx3 on DELTATOOL_WK_LINK(LINK_TYPE_LO, LINK_TYPE_HI);
  perform UPDATEOBJECTSTATISTICS ('DELTATOOL_WK_LINK', 2);

  insert into DELTATOOL_LINK (ID, TYPE, CALLER_TYPE, LINK_TYPE_LO, LINK_TYPE_HI, CALLED_TYPE, VALUE, CALLER_TYPE_STR, LINK_TYPE_STR, CALLED_TYPE_STR, LANGUAGE)
  select p_id, p_delta_type, o.CALLER_TYPE, o.LINK_TYPE_LO, o.LINK_TYPE_HI, o.CALLED_TYPE, o.VALUE, t1.objtypstr
    , coalesce((select COALESCE(staticdesc,'xxx') from csv_linktype LT where LT.acctyplo = o.LINK_TYPE_LO and LT.acctyphi = o.LINK_TYPE_HI),'xxx')
    , t2.objtypstr, t1.lngstr
  from DELTATOOL_WK_LINK o
    join objtypstr t1 on (t1.objtyp = o.CALLER_TYPE)
    join objtypstr t2 on (t2.objtyp = o.CALLED_TYPE);

  perform droptemporarytable('deltatool_wk_link');

  perform droptemporarytable('deltatool_wk_dynlink');
  create temporary table DELTATOOL_WK_DYNLINK (CALLER_TYPE INTEGER not null, LINK_TYPE_LO INTEGER not null , LINK_TYPE_HI INTEGER not null, CALLED_TYPE INTEGER not null, LINK_STATUS character varying(1) not null, LINK_RF character varying(1) not null, VALUE INTEGER not null ); 
  -- TODO: compute the value for LINK_RF
  insert into DELTATOOL_WK_DYNLINK (CALLER_TYPE, LINK_TYPE_LO, LINK_TYPE_HI, CALLED_TYPE, LINK_STATUS, LINK_RF, VALUE)
  select k1.objtyp, a.acctyplo, a.acctyphi, k2.objtyp, case when a.prop = 1 then 'I' else 'E' end, 'Y', count(1)
  from keys k1
    join acc a on (a.idclr = k1.idkey and a.accknd = 0 and a.prop > 0)
    join keys k2 on (k2.idkey = a.idcle)
  where k1.idkey <= L_ID_MAX
  and k2.idkey <= L_ID_MAX
  and exists (select 1 from objpro op where op.idobj = k1.idkey)
  and exists (select 1 from objpro op where op.idobj = k2.idkey)
  group by k1.objtyp, a.acctyplo, a.acctyphi, k2.objtyp, a.prop;

  create index DELTATOOL_WK_DYNLINK_idx1 on DELTATOOL_WK_DYNLINK(CALLER_TYPE);
  create index DELTATOOL_WK_DYNLINK_idx2 on DELTATOOL_WK_DYNLINK(CALLED_TYPE);
  create index DELTATOOL_WK_DYNLINK_idx3 on DELTATOOL_WK_DYNLINK(LINK_TYPE_LO, LINK_TYPE_HI);
  perform UPDATEOBJECTSTATISTICS ('DELTATOOL_WK_DYNLINK', 2);

  insert into DELTATOOL_DYNLINK (ID, TYPE, CALLER_TYPE, LINK_TYPE_LO, LINK_TYPE_HI, CALLED_TYPE, LINK_STATUS, LINK_RF, VALUE, CALLER_TYPE_STR, LINK_TYPE_STR, CALLED_TYPE_STR, LANGUAGE)
  select p_id, p_delta_type, o.CALLER_TYPE, o.LINK_TYPE_LO, o.LINK_TYPE_HI, o.CALLED_TYPE, o.LINK_STATUS, o.LINK_RF, o.VALUE, t1.objtypstr
    , coalesce((select COALESCE(staticdesc,'xxx') from csv_linktype LT where LT.acctyplo = o.LINK_TYPE_LO and LT.acctyphi = o.LINK_TYPE_HI),'xxx')
    , t2.objtypstr, t1.lngstr
  from DELTATOOL_WK_DYNLINK o
    join objtypstr t1 on (t1.objtyp = o.CALLER_TYPE)
    join objtypstr t2 on (t2.objtyp = o.CALLED_TYPE);

  perform droptemporarytable('deltatool_wk_dynlink');

  return;
End;
$body$ 
  LANGUAGE plpgsql VOLATILE
  5. Now, restart the node and perform Add version step again.
  6. If the above points do not help you, contact CAST Technical Support and provide the Relevant Input.

  

Notes/comments
 Ticket # 39921
Related Pages

https://extend.castsoftware.com/#/extension?id=com.castsoftware.labs.deltareportanalysis&version=1.1.0