SQL Error - Admin Console

[ukender]

Dear All

I'm getting the below error while executing the script in admin console. could you please help me on this.

Thanks in Advance

SELECT CreateSequenceIfNecessary('NeoCustomId', '1000', 'cache 1000');

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

[DavidKangni · Accepted solution]

Hi,

Looks like you're trying to use a  script dedicated to Postgresql to create a sequence on SQL database.

You should try this instead

INSERT INTO XtkNewId(sSequence, IdSeed) VALUES ('NeoCustomId', 1);

Thanks,

David

[ukender]

Hi David Thanks for the response. Yes you are right, I am trying to create a custom sequence for one of the table. The reason for custom sequence is xtkNewId had reached its limit and started with negative values. I have found the above code for creating the custom sequence. So as per your suggestion if I insert the record into xtkNewid will it generate sequence unique primary key (same as xtkNewId)?

• Also would like to know, what will be the impact for the existing primary key in the table. Let's say I have a table called XYZ and its Primary key is AB (autopk = true). Now, if I go for the custom sequence will there be any impact for the existing AB records? 

[DavidKangni]

Yes, it will create a new sequence called NeoCustomId in your sequence table which is XtkNewId.

I'm assuming that you have Schemas created with autopk but not specifying sqlSequence.

If you have a recent build (starting from 18.4) of ACC you should be covered.

This feature is implemented through a new API: GenCreateOrUpdateSchemaSequenceDDL.

This API generates the SQL script used to create or update autopk schema sequence:

GenCreateOrUpdateSchemaSequenceDDL(schemaID, newSequenceName, oldSequenceName)

• schemaID (mandatory): "<nameSpace>:<schemaName>"
• newSequenceName (mandatory): new sequence name
• oldSequenceName (optional): old sequence name

example: GenCreateOrUpdateSchemaSequenceDDL("cus:contacts", "myContactsSeq", "XtkNewId")

In this case, the new sequence will begin from the largest ID of the schema.

Also please view webinar - Adobe Campaign Classic - Sequences Exhaustion for more insights

Thanks,

David

[ukender]

Hi David,

I really appreciate it. Thanks a lot for the detailed info. Yes you are perfectly correct. We have used only autopk not pksequence in the schema. We are planning for the build upgrade but it is pending with business approval. The negative key issue in Prod. So, It's making a huge impact. That is the reason looking for a temporary / alternate solution. Hope this will solve the issue temporarily or please suggest if you think of any other possible solution.

One last question from me, Would like to know, Can we use custom schema (autopk +pksequence) in the current application server - version 6.1.1 build 8863? 

[DavidKangni]

Hi,

You can use pkSequence for new schemas but you cannot use the API to switch from an old sequence to a new sequence. 

This start only from 18.4 (build 8931). You will be able to use the API only after a build upgrade.

That being said, negative sequence should not be and issue. You may need to check your retention period in deployment wizard.

Adobe Campaign uses an ID sequence that has to be consumed accordingly: xtkNewId . If the sequence is consumed very quickly (i.e. from 100,000 per day), you must verify that it is consistent with your business requirements, such as sending millions of emails per day. It is possible to define a dedicated sequence for particular tables. You can also setup a workflow to monitor ID usage.
When the sequence reaches more than 2 billion (2,147,483,648 is the exact number), it goes back to zero. It must be avoided and creates issues, which is why this sequence must be monitored.

To prevent this with large tables, consider using a specific sequence. This can be done with the pkSequence attribute in the schema.
High-frequency workflows that create a lot of logs will consume a lot of IDs. So it is highly recommended to avoid too many logs and high frequencies in workflows.
If the sequence has already cycled, the best solution is to switch to negative IDs, starting from -2,147,483,648.

Thanks,

David