Increased sequences after export schema/user

[thomasschulz2]

Hi,

I noticed, that after exporting the catalog data of an user or a schema, all corresponding sequences are increased by one. Normally I would expect, that an export doesn't change any data. Maybe this is a bug?

Are there others possibilities to export the sequences without changing them?

Tested versions: 7.6.03.15 and 7.6.05.09 on linux (64 bit)

Regards,

Thomas

[lbreddemann · Accepted solution]

Hi Thomas,

> I noticed, that after exporting the catalog data of an user or a schema, all corresponding sequences are increased by one. Normally I would expect, that an export doesn't change any data. Maybe this is a bug?

How did you check this?

Via

select <sequence>.currval from dual?

or via

select sequence_name, last_number from sequences where sequence_name ='<name of your sequence>' ?

Anyhow, when using sequences you should never expect the numbers to be without gaps.

Usually you want to use sequences to have a locking-free mechanism of getting numbers, e.g. for IDs.

They don't rollback if the session that got a number from it rolls back and if the sequence is cached (what should be done for highly accessed sequences), than the cached numbers will be gone with the next restart.

So with sequences you will get gaps.

If you cannot allow this is your application than you'll have to implement a number range table like the infamous SAP NRIV where you actually store your current highest number.

Concerning the export: I was not able to reproduce it.

Can you describe in more detail how you did the export?

> Are there others possibilities to export the sequences without changing them?

Although this is rather meaningless: you may use the "Export DDL" function in DB Studio that is available via right mouse button, when you display the sequence definition.

Anything else would require some custom SQL statements - but not today anmyore...:-)

regards,

Lars

[thomasschulz2 · Accepted solution]

Hi Wolfgang and Lars.

> Hi Thomas,

>

> > I noticed, that after exporting the catalog data of an user or a schema, all corresponding sequences are increased by one. Normally I would expect, that an export doesn't change any data. Maybe this is a bug?

>

> How did you check this?

>

> Via

>

> select <sequence>.currval from dual?
> 

> or via

>

>

> select sequence_name, last_number from sequences where sequence_name ='<name of your sequence>' ?
> 

>

Via

select sequence_name, last_number from sequences

I've tried your first variant with

select <sequence>.currval from dual

now and get unchanged values after export. But with

select <sequence>.nextval from  dual

I get the same increased values as with a select from the sequences table.

>

> Anyhow, when using sequences you should never expect the numbers to be without gaps.

> Usually you want to use sequences to have a locking-free mechanism of getting numbers, e.g. for IDs.

> They don't rollback if the session that got a number from it rolls back and if the sequence is cached (what should be done for highly accessed sequences), than the cached numbers will be gone with the next restart.

>

> So with sequences you will get gaps.

OK - this is no problem for us.

> Concerning the export: I was not able to reproduce it.

> Can you describe in more detail how you did the export?

>

Here are the DDLs:

CREATE TABLE "TESTTAB" ("FIELD1" Integer)
CREATE SEQUENCE TESTSEQ

Export:

export schema testuser catalog outstream 'testuser.cat'

or

export user catalog outstream 'testuser.cat'

> > Are there others possibilities to export the sequences without changing them?

>

> Although this is rather meaningless: you may use the "Export DDL" function in DB Studio that is available via right mouse button, when you display the sequence definition.

>

> Anything else would require some custom SQL statements - but not today anmyore...:-)

>

> regards,

> Lars

No problem.

Regards,

Thomas

Edited by: Thomas Schulz on Jan 26, 2009 7:00 PM

[thomasschulz2 · Accepted solution]

Hi Lars,

>

> Although this is rather meaningless: you may use the "Export DDL" function in DB Studio that is available via right mouse button, when you display the sequence definition.

my version of DB Studio (7.7.04.26 on Linux 64bit with 32bit Java) offers only "Export SQL", which saves the DDL without the start value ("... start with <value not accessible> ...").

Regards,

Thomas

[lbreddemann · Accepted solution]

Hi Thomas,

> my version of DB Studio (7.7.04.26 on Linux 64bit with 32bit Java) offers only "Export SQL", which saves the DDL without the start value ("... start with <value not accessible> ...").

you're right. You've to fill in the start number there yourself - not very automatic...

Anyhow, you may use this statement instead:

select 'CREATE SEQUENCE "' || schemaname || '"."' || sequence_name ||'" increment by ' ||
        increment_by || ' start with ' || last_number || ' minvalue ' || min_value ||
        ' maxvalue ' || max_value ||' '|| decode(cycle_flag, 'N', 'nocylce', 'Y', 'cycle') ||
        ' cache ' || cache_size
from sequences

regards,

Lars

[thomasschulz2 · Accepted solution]

Hi Lars,

this works for our export (small correction: nocycle instead nocylce).

Thanks a lot for your help.

Nevertheless we are wondering, why an export increase the sequence values.

Regards,

Thomas

[lbreddemann · Accepted solution]

> this works for our export (small correction: nocycle instead nocylce).

> Thanks a lot for your help.

Arghs --- always those typos...

> Nevertheless we are wondering, why an export increase the sequence values.

Sure - since Wolfgang Auer is already reading this thread, I'm sure you'll get a good answer for that sooner or later.

I guess this has something to do with the way that sequences are stored internally - but only the developer can tell that for sure.

regards,

Lars

[Former Member]

Hello,

as far as I understood the reason to increase the sequence value is to avoid duplicate key errors when the data is re-imported. As one should never expect the numbers to be without gaps this seems to be more convenient for the user.

Best Regards

Wolfgang

[thomasschulz2]

>

> Hello,
> 
> as far as I understood the reason to increase the sequence value is to avoid duplicate key errors when the data is re-imported. As one should never expect the numbers to be without gaps this seems to be more convenient for the user.
> 
> Best Regards
>   Wolfgang

Hi Wolfgang,

I don't really understand, what problems could appear, if I re-import unchanged data and your answer doesn't explain the bigger gaps after database restart, but I don't know anything about the complex structures behind the sequence mechanism.

For our application the gaps doesn't matter. Therefore I will not dig deeper now into this topic and mark my question as answered.

Best regards and thanks for your investigations,

Thomas

[Former Member]

Hello,

you are right.

After the calling nextval I can observe the increase of the sequence with each export.

Regards

Wolfgang

[Former Member]

Hello Thomas,

In fact I cannot reproduce your problem.

I created a table and a sequence in a schema and I exported it.

I did:

• create sequence test

• create table testtable (i int)

• EXPORT SCHEMA "MONA" CATALOG OUTSTREAM FILE 'MONA.CATALOG'

• select * from sequences => delivers LAST_NUMBER 1

I think the problem is that you use

"select <sequence>.nextval from dual"

This increases the value from the sequence.

Please use "select <sequence>.currval from dual".

Best Regards

Wolfgang

[thomasschulz2]

Hello Wolfgang,

>

> Hello Thomas,

>

> In fact I cannot reproduce your problem.

> I created a table and a sequence in a schema and I exported it.

>

> I did:

> * create sequence test

> * create table testtable (i int)

> * EXPORT SCHEMA "MONA" CATALOG OUTSTREAM FILE 'MONA.CATALOG'

> * select * from sequences => delivers LAST_NUMBER 1

>

I created a new sequence and a table with your described steps and get after first export and select also LAST_NUMBER = 1. But with every repeat of the last two steps (export and select) LAST_NUMBER is increased.

Here some other observations:

- If I restart the database, then I get a gap of something over 10 (12, 19, ... no recognizable system)

- The select of CURRVAL in a database session only works after a select of NEXTVAL in the same session (like described in documentation)

- I've got during my tests sometimes a state, where nothing was changed after an export. If I execute a select of NEXTVAL, then after every following export all sequence values are increased again.

Regards,

Thomas

[Former Member]

Hello,

is the sequence used in a view or trigger definition?

Is it possible you post the (simplified) catalog definition?

Best Regards

Wolfgang