Optional RestartSequenceOperation.StartValue

[dmitry-lipetsk]

MSSQL, PostgreSQL and Firebird support "ALTER SEQUENCE {name} RESTART " without "WITH startValue" section

RestartSequenceOperation.StartValue requires definition of startValue:

efcore/src/EFCore.Relational/Migrations/Operations/RestartSequenceOperation.cs

Lines 27 to 30 in 252ece7

	/// <summary>
	/// The value at which the sequence should re-start, defaulting to 1.
	/// </summary>
	public virtual long StartValue { get; set; } = 1L;

Why do not use Nullable<long> for this property?

[dmihaita]

I intend to contribute a fix for this issue by:

• changing the type of the StartValue property from long to long?
• modify the generators as that it should add " RESTART WITH {StartValue} " only in case StartValue has a value (is not null)
• adapt the affected unit tests.

Should I proceed with the implementation?
Thanks.

[ajcvickers]

@dmihaita Looks good. Go for it!

[roji]

I think there's a conceptual design issue here...

There's no problem with allowing a null start value in RestartSequenceOperation; this represents an operation that occurs at some point in a migration.

However, ISequence itself represents the state of the sequence in the model, at a given point in time; the start value of a sequence is always something - it can never be null. The attempt to work around this with InternalStartValue in #29346 also doesn't work: when I go to the sequence metadata and ask what its start value is, I should get the correct response corresponding to the reality in the database. With #29346 as it is, consider the following:

1. I create a new sequence with start value 5 (migration1)
2. I now change my model and set the start value to null, in order to generate a sequence restart (migration2).
   a. When that migration is executed, my database sequence is back to 5 (as I wanted).
   b. But in my model, _startValue is now null. Reading the StartValue will return DefaultStartValue, which is 1 - that's incorrect.

This can have various problematic consequences; for example, when we implement migration squashing (#2174), if you squash all the migrations together, the original start value of 5 disappears, and you're left with a sequence that has a null starting value (so 1).

At the end of the day, the model cannot contain an operation that needs to occur in the database (such as "reset sequence"); operations can only be a result of comparing two model states (source and destination). So I think the only place where we can allow a sequence restart without a start value is if the user inserts that manually into a scaffolded migration.

@dmihaita let me know if that makes sense - I'll also bring this to a quick team discussion to confirm the above.