autopk in the Schema is not incrementing properly when used sqlExec or xtk.session.write

[LakshmiPravallika]

Hi,

In a custom schema, for autopk, if we use dataloading, it is inserting Primary key values with +1.. like 1000,1001,1002,1003 etc..

but when we use sqlExc or xtk seesion write, it increments with 30 difference between each value of the Primary key's.. like 1000, 1030, 1060, 1090 etc.....

Could you please check and let me know why it is happening and how to solve this issue?

Regards,

Pravallika.

[Deb_Tripathy]

Hi @LakshmiPravallika ,

Do you have access to the database to check the definition of the sequence?

It is to do with "increment" in sequence definition. Is this patter causing any issues?

Thanks,

Deb

[LakshmiPravallika]

Hi @Deb_Tripathy ,

I dont have db access to check the definition of sequence.

Regards,

Pravallika.

[DavidKangni]

Hi @LakshmiPravallika,

The gap is in your sequence creation. Per default, I'm assuming you are not defining the sequence so it's using your db sequence

Basically the caching apply to sqlExc or xtk session write to avoid duplicate key constraints. 

-- Log: Creating specific Id generator for NmsRecipient
SELECT CreateSequenceIfNecessary('NmsRecipientId', '1000', 'cache 30');

create or replace function GetNewNmsRecipientIds(integer) returns text as '
declare
  strIds text;
  i integer;
  iId integer;
begin
  strIds = '''';
  for i in 1 .. $1 loop
    select NextVal(''NmsRecipientId'') into iId ;
    if i > 1 then
      strIds := strIds || '','';
    end if;
    strIds := strIds || cast(iId as text);
  end loop;
  return strIds;
end;
' language plpgsql
;

 Thanks,

David

[LakshmiPravallika]

Hi @DavidKangni ,

Should the same script be executed in SQL Code activity.

Looks like its an OOTB Function, can it be replaced?

I didn't understand the solution given completely, could you please explain step by step ?

Regards,

Pravallika.

[DavidKangni · Accepted solution]

the code was a sample. You need to define your own sequence or it will use the db default pattern (xtkNewId pattern).

Is this pattern causing any issues? If not keep it.

by changing to 1 you increase the risk to have duplicate key errors in your executions.

If your question is to understand the difference in behavior:

• data loading know the rows count, pre calculate the next ids using the max id and allocate 
• sqlExec or xtkSessionWrite are SOAP calls then execute 1 by 1. to avoid duplicate keys constraints, it's using the max id + 30 to have a bandwith in case a parallel creation/insertion happen.

Thanks,

David