Expand my Community achievements bar.

SOLVED

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

Avatar

Community Advisor

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.

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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

  



David Kangni

View solution in original post

5 Replies

Avatar

Community Advisor

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

Avatar

Community Advisor

Hi @Deb_Tripathy ,

 

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

 

Regards,

Pravallika.

Avatar

Community Advisor

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



David Kangni

Avatar

Community Advisor

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.

Avatar

Correct answer by
Community Advisor

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

  



David Kangni