I have this sequence and a table that has a PK default to a sequence.nextval.
But when i Unloaddb the database and reload it somewhere, an error appears referencing the sequence name.
Is there any work around with this issue? Im using Ingres R2 with this new feature like autoincrement.
Also, It is safe to use sequence as a Primary Key? Is it messed up after unload/reload?
Thanks once again!
JB
ANSWER: Hello JB ;-)
First please excuse for the late, but I'm on hollidays and Internet is not so accessible by this sunny time ;-)
I've done some few tests on Ingres (but R3, ie 9.2.0).
The test I run was :
1/ create sequence
2/ create a 2 columns table (id & lib) using this sequence, choose the column concerned by the sequence as a PK
3/ insert 3 rows (using only the lib column)
4/ unload the database (via unloaddb)
5/ reload the database (with an other name)
I did not fall on any errors ...
The error you have should come from an unloaddb bug. You must check your copy.in content to see when the sequence is created in the script. I guess it was in a wrong order (table before sequence). This is not the case in the version I check (9.2.0).
A work around could be to use copydb to extract the SQL instead of unloaddb, and use separates files for create sequence, create table and so on. Then you can play them in the order you want. copydb accept many options like :
-with_sequences, -with_tables and so on. Use the -outfile=filename to specify an other name than copy.in and copy.out
Check the documentation for copydb options and feel free to share with me the questions or issues you have.
Is it safe to use sequence as a PK ? Good question indeed ;-) Yes, if you know what you do ...
When you insert a row in a table with a column that have a default sequence (seq.nextval), if the insert have a value for this column, then it use the value, if not it use the sequence next value. So here your key is preserved.
Take care when you run unloaddb and the value of your sequence at this moment. Let's have a quick look on the test I run.
- I create my sequence with "create sequence numero start with 1".
- Then I insert 3 rows in my table. Then I run unloaddb.
>> I discover here in my copy.in script my sequence default value captured by unloaddb was 21 (instead of 4, because of the 3 rows inserted).
Why ? Ingres replace some default parameters for me and the SQL sentence extracted by unloaddb was :
"create sequence numero as integer
start with 21 increment by 1
minvalue 1 maxvalue 2147483647
cache 20 no cycle no order"
(please note "start with" value is 21)
Then "cache" parameter has a value of 20 by default, it mean Ingres take sequence 20 by 20, and update the iisequences system catalog in consequence. In my case it mean 18 numbers lost (21 - 3) ... so if you need a continuous numbering, take care.
Hope this is clear for you and my english not so awfull (I'm french).
Feel free to ask any questions you need ...
King regards,
Jean-Pierre Zuate
---------- FOLLOW-UP ----------
QUESTION: Thank you very much for a very helpful reply.
Actually, I already encountered this issue but want to clarify it with you.
I have decided not to use sequence with PK because its too dangerous.
Also, I have this idea to turn off the cache with sequence but it's not recommended, it will degrade performance.
Thanks a lot,
JB
Answer Hello JB,
Thank's for the update. Perhap's turning the cache off will degrade performance but sequence will be always faster than the older method (maintaining a sequence in a "normal" table and update it).
