Firebird 5.0 Sequence - Easy to change RDB$INITIAL_VALUE without changing the NEXT value

[AnatolyLoy]

-- Firebird 5.0.1
`-- 1. Prepare a sequence w/o dependencies and try to recreate
CREATE SEQUENCE TEST;
SELECT RDB$INITIAL_VALUE FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'TEST';
-- 1
SELECT GEN_ID(TEST, 10) FROM RDB$DATABASE;
-- 10
-- Attention, look on my hands
-- .... one....
CREATE OR ALTER PROCEDURE GET_NEXT_TEST RETURNS (ID INTEGER)
AS BEGIN ID = GEN_ID(TEST, 1); END;

-- .... two.....

RECREATE SEQUENCE TEST START WITH 0; -- was 1
/*
Cannot commit transaction:
can't format message 13:393 -- message file C:\Program Files (x86)\Firebird\Firebird_50_3055_x32\firebird.msg not found.
unsuccessful metadata update.
cannot delete.
GENERATOR TEST.
there are 1 dependencies.

SQLCODE: -607
SQLSTATE: 42000
GDSCODE: 335544351*/

-- ... three ...
SELECT GEN_ID(TEST, 10) FROM RDB$DATABASE;
-- 20 - it's not changed after exception

-- .... and FOUR!....
SELECT RDB$INITIAL_VALUE FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'TEST';
-- 0
-- !!! It's changed after exception
-- ********************
-- IS IT A BUG OR FEATURE like
-- "Ability to change RDB$INITIAL_VALUE withot dropping dependencies?"?
-- ********************

-- P.S.
-- drop test DDL
DROP PROCEDURE GET_NEXT_TEST ;
DROP SEQUENCE TEST;`

[hvlad]

What tool you use to run script and where is commit's and\or rollback's ?
If it is isql - where is set term ?

hint: you didn't commit (as it is not possible) or rollback (forget ?) transaction with RECREATE SEQUENCE thus see not committed changes.

[AnatolyLoy]

I use IBExper and just run SQL statements one by one.

1. When I rollback transaction after recreate sequence, the result of
   -- .... and FOUR!....
   SELECT RDB$INITIAL_VALUE FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'TEST';
   -- 1 - It works as expected.

However, when I commit the transaction, the initial value changes as described above.
So the one SQL statement is not single: part of the action is NOT applied - sequence not dropped and not re-created,
but the second part of the statement was applied.
Is it normal/good behaviour?

[hvlad]

However, when I commit the transaction, the initial value changes as described above.

That transaction can not be commited.

So the one SQL statement is not single: part of the action is NOT applied - sequence not dropped and not re-created,
but the second part of the statement was applied.
Is it normal/good behaviour?

DDL statements executed in two phases, some work is done at commit only.
This is well known since IB times.

[AnatolyLoy]

Thanks, it's my bad.
I tried to recreate the sequence and got an error on the commit only. And forgot to rollback transaction. And made select RDB$INITIAL_VALUE inside the non-committed transaction.

Can I change RDB$INITIAL_VALUE without changing the actual/next value of the sequence?
After migration from 2.5, all migrated sequences have "0" as INITIAL_VALUE, and those created after migration have "1" as INITIAL_VALUE by default. Can I change it without removing all dependencies?

[mrotteveel]

That does seem like a problem in migration. Since Firebird 4.0 (I think) RDB$INITIAL_VALUE represents the first value generated when calling NEXT VALUE FOR, in older versions it represented the value used to initialize the sequence, and NEXT VALUE FOR would generate the next value after RDB$INITIAL_VALUE. Unfortunately, there is no statement to set RDB$INITIAL_VALUE.

However, this is only problematic if you regularly use ALTER SEQUENCE RESTART and can easily be worked around by instead using ALTER SEQUENCE RESTART WITH 1.

[hvlad]

RDB$INITIAL_VALUE can not be changed without re-creating of sequence.
But why do yo need it ?

[hvlad]

All (except of aafemt) : don't feed the troll

[aafemt]

Don't feed the troll

Ok.

[mrotteveel]

My case is the different result of extracted DDL in Firebird 5.0 after migration: for migrated and new sequences:

1. CREATE SEQUENCE **SEQUENCE_CREATED_IN_FB25** START WITH **0** INCREMENT BY 1;

2. CREATE SEQUENCE **SEQUENCE_CREATED_IN_FB50** START WITH **1** INCREMENT BY 1;

And I still can't understand why you compare it. New sequences created in migration process ? If yes, what (old?) sequences you compare against new sequences ?

@hvlad The problem is that if you have create SEQUENCE in Firebird 2.5 (which will start at 1!), and then upgrade to Firebird 5.0 (or 4.0), and you then create a SEQUENCE in Firebird 5.0 (or 4.0) (which also starts at one), then there are two problems:

1. Extracting DDL with ISQL will report START WITH 0 for the sequence created in 2.5, and START WITH 1 for the one create in 5.0 (or 4.0)
2. Executing ALTER SEQUENCE <name> RESTART will make the sequence created in 2.5 restart at 0, while the one created in 5.0 (or 4.0) will restart at 1

This happens because GBAK doesn't fix the metadata (specifically RDB$INITIAL_VALUE) when upgrading from 3.0 or older to 4.0 or higher to address the change in behaviour (meaning of RDB$INITIAL_VALUE) introduced in Firebird 4.0 for the correct behaviour of START WITH, especially in conjunction with an increment other than 1.

[mrotteveel]

I guess this problem could be worked around by introduce on non-standard extension like ALTER SEQUENCE <name> SET START WITH VALUE <n> which will only set RDB$INITIAL_VALUE

And as a reminder, so we don't get this regression again, ALTER SEQUENCE RESTART WITH <n> should not touch RDB$INITIAL_VALUE in any way, it should only touch the sequence value so it generates <n> for the next invocation of NEXT VALUE FOR.

[hvlad]

I guess this problem could be worked around

So far I see no problem at all.

[hvlad]

@mrotteveel : I perfectly understand difference in exctracted DDL due to values in RDB$INITIAL_VALUE.
I don't understand why one want to compare new objects, created after migration, with ... what ?
Thats new objects doesn't exists before migration. If something is created after migration, it is not part of migration process.
Old objects have the same DDL before and after migration. What else is needed ?

[mrotteveel]

@mrotteveel : I perfectly understand difference in exctracted DDL due to values in RDB$INITIAL_VALUE. I don't understand why one want to compare new objects, created after migration, with ... what ? Thats new objects doesn't exists before migration. If something is created after migration, it is not part of migration process. Old objects have the same DDL before and after migration. What else is needed ?

An object created with CREATE SEQUENCE <name>; before such migration should IMHO be indistinguishable from an object created with CREATE SEQUENCE <name>; after the migration, and exhibit the same behaviour when restarted. That is, both should have RDB$INITIAL_VALUE equal to 1.

[AnatolyLoy]

@hvlad Firstly, the distinction ruins the KISS approach. We have about 700 sequences in the database and expect that old and new sequences look the same.
Secondly, you are right; I should have explained how the distinction affects our product and development process.
Sorry if it looks very complex...
Our customers use different versions of the product. We support old versions by adding new features and merging them into new versions too. For instance,

1. The product versions 25.1 uses Firebird 2.5, version 50.1 uses Firebird 5.0 (during update from version 25.x product migrate the customer database from Firebird 2.5 to 5.0)
2. We add some features to 25.2 that contain CREATE SEQUENCE X and merge it to 50.2
3. Some customers will have sequence X created directly by Firebird 5.0

• Customer A: updated from 25.1 to 50.2
• Customer B: updated from 50.1 to 50.2
• Customer C: started product using from 50.2

6. And Customer D that:

• updated from 25.1 to 25.2 have sequence X created by Firebird 2.5
• after testing and some months, updated from 25.2 to 50.2, the migrated sequence X has distinctions discussed on many pages above :)

7. And the last effort: We use DDL extraction to validate that the updated database state correlates to the DB version template. The database DDL of customer D will differ from the template and A, B, C.