Thread: documentation clarifications for "alter sequence" ?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/12/sql-altersequence.html Description: postgresql 11.5 I execute this: CREATE TABLE v5processing.just_testing( id SERIAL, some_test text ); -- sequence last value is now 0, min value is 1 then I execute this: insert into v5processing.just_testing(some_test) values ('data'); insert into v5processing.just_testing(some_test) values ('more data'); insert into v5processing.just_testing(some_test) values ('whatever'); -- last value is now 3, min value is 1 But alter sequence behaves thusly: alter sequence just_testing_id_seq minvalue 4 -- ERROR: START value (1) cannot be less than MINVALUE (4) -- it would be helpful if this interaction/ relation were explicitly pointed out, and why it behaves this way. ("MINVALUE cannot be greater than START value" makes more sense to me, since minvalue is the thing I'm attempting to alter. Or even "START value must be at least MINVALUE", if in fact that's the limitation.) alter sequence just_testing_id_seq restart with 4 -- no error, but still has min value 1 -- "Any parameters not specifically set in the ALTER SEQUENCE command retain their prior settings". OK, but this does not seem intuitive, at least to me. What meaning or use does a min value have after a restart? alter sequence just_testing_id_seq minvalue 4 -- ERROR: START value (1) cannot be less than MINVALUE (4) -- clearly my restart had no visible effect But: alter sequence just_testing_id_seq restart with 2 -- no error , but still has min value 1 and last value 3 alter sequence just_testing_id_seq minvalue 2; -- ERROR: START value (1) cannot be less than MINVALUE (2) On the other hand : alter sequence just_testing_id_seq start 2; followed by alter sequence just_testing_id_seq minvalue 2; throws no error, which leads me to believe there is a subtle difference between restart and start. But lastvalue is unchanged, and inserting a new record did not apparently start or restart at 2 since that should have triggered an error(?). Instead the insert succeeded with id = 4. I stumbled on this when trying to reset a test DB to all sequences starting at 1, and finding that there was one the had somehow gotten a start value of 6. I would have expected that 'restart 1' did just that, with no complications. Maybe I'm the only one who's confused. Also, "setval function" is mentioned. A link to that would be helpful. Thanks.
On Thu, Nov 21, 2019 at 08:06:28PM +0000, PG Doc comments form wrote:
> -- it would be helpful if this interaction/ relation were explicitly pointed
> out, and why it behaves this way. ("MINVALUE cannot be greater than START
> value" makes more sense to me, since minvalue is the thing I'm attempting to
> alter. Or even "START value must be at least MINVALUE", if in fact that's
> the limitation.)
Project style is usually to avoid full sentences in user-facing error
messages, so an improvement may be something like that:
cannot set MINVALUE to be higher or equal than START value.
However, these error messages are actually as they are per the state
of the code, whose goal is to be kept simple. If you look at
init_params() in sequence.c, what happens is that an initial state of
the sequence is taken, and then the parameters specified in *all* the
sub-clauses of the query update the state of the sequence. Finally
all the parameters are cross-checked, so you cannot actually know
which parameter is being set. Note that trying to guess what is
getting set and to inform that with more error messages would not be
that helpful either if trying to update both parameters at the same
time. For example what would you do here?
ALTER SEQUENCE goo MINVALUE 4 START 1;
> -- "Any parameters not specifically set in the ALTER SEQUENCE command
> retain their prior settings". OK, but this does not seem intuitive, at least
> to me. What meaning or use does a min value have after a restart?
It simply means that non-updated parameters are left as they are
originally. And minvalue/maxvalue are used depending on if the
sequence is ascending/descending if CYCLE is enabled. If NO CYCLE is
used they are used as sanity checks.
> Also, "setval function" is mentioned. A link to that would be helpful.
You can say the same about currval, lastval and nextval, no?
--
Michael
Attachment
I looked into this report. When we do a CREATE or ALTER sequence, we check that the new sequence row is valid. We don't track which values were changed by ALTER, which is why you are seeing this kind of error message wording. I don't think it is worth tracking this to generate clearer error messages since this is the first complaint I have seen about this. --------------------------------------------------------------------------- On Thu, Nov 21, 2019 at 08:06:28PM +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/12/sql-altersequence.html > Description: > > postgresql 11.5 > > I execute this: > CREATE TABLE v5processing.just_testing( > id SERIAL, > some_test text > ); > > -- sequence last value is now 0, min value is 1 > > then I execute this: > > insert into v5processing.just_testing(some_test) values ('data'); > insert into v5processing.just_testing(some_test) values ('more data'); > insert into v5processing.just_testing(some_test) values ('whatever'); > > -- last value is now 3, min value is 1 > > But alter sequence behaves thusly: > > alter sequence just_testing_id_seq minvalue 4 -- ERROR: START value (1) > cannot be less than MINVALUE (4) > > -- it would be helpful if this interaction/ relation were explicitly pointed > out, and why it behaves this way. ("MINVALUE cannot be greater than START > value" makes more sense to me, since minvalue is the thing I'm attempting to > alter. Or even "START value must be at least MINVALUE", if in fact that's > the limitation.) > > alter sequence just_testing_id_seq restart with 4 -- no error, but still has > min value 1 > > -- "Any parameters not specifically set in the ALTER SEQUENCE command > retain their prior settings". OK, but this does not seem intuitive, at least > to me. What meaning or use does a min value have after a restart? > > alter sequence just_testing_id_seq minvalue 4 -- ERROR: START value (1) > cannot be less than MINVALUE (4) > > -- clearly my restart had no visible effect > > But: > > alter sequence just_testing_id_seq restart with 2 -- no error , but still > has min value 1 and last value 3 > alter sequence just_testing_id_seq minvalue 2; -- ERROR: START value (1) > cannot be less than MINVALUE (2) > > On the other hand : > alter sequence just_testing_id_seq start 2; > > followed by > alter sequence just_testing_id_seq minvalue 2; > > throws no error, which leads me to believe there is a subtle difference > between restart and start. But lastvalue is unchanged, and inserting a new > record did not apparently start or restart at 2 since that should have > triggered an error(?). Instead the insert succeeded with id = 4. > > I stumbled on this when trying to reset a test DB to all sequences starting > at 1, and finding that there was one the had somehow gotten a start value of > 6. I would have expected that 'restart 1' did just that, with no > complications. Maybe I'm the only one who's confused. > > Also, "setval function" is mentioned. A link to that would be helpful. > Thanks. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +