Community Advisor

Solved

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

Forum|Forum|2 years ago
August 8, 2023
2 replies
1586 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by DavidKangni

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

D

DebTr

Level 6

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

Author

Community Advisor

Hi @debtr ,

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

Regards,

Pravallika.

DavidKangni

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

LakshmiPravallika

Author

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.

DavidKangni

Accepted solution

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

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded