documentation clarifications for "alter sequence" ?

Поиск
Список
Период
Сортировка
От PG Doc comments form
Тема documentation clarifications for "alter sequence" ?
Дата
Msg-id 157436678837.7231.16812037581107684403@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: documentation clarifications for "alter sequence" ?  (Michael Paquier <michael@paquier.xyz>)
Re: documentation clarifications for "alter sequence" ?  (Bruce Momjian <bruce@momjian.us>)
Список pgsql-docs
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.

В списке pgsql-docs по дате отправления:

Предыдущее
От: Liudmila Mantrova
Дата:
Сообщение: Re: GSoD - a patch for Getting Started tutorial
Следующее
От: PG Doc comments form
Дата:
Сообщение: Difference between Bulk Load (Multiple inserts or single inserts) and COPY