CODEWITHSUNDEEP
postgresql
yankee
yankee

Reputation: 40860

How to find the next value produced by nextval without advancing the sequence?

I have an app which does have a maintenance menu (which is of course used in only very rare cases). In this menu I also display the next number that a certain sequence will generate next and give the user an option to reset the sequence.

I use the following query to show the next number:

select case when last_value is not null then last_value+increment_by else start_value end
from pg_sequences
where sequencename = 'my_sequence'

And if the user changes the sequence I run:

alter sequence my_sequence restart with $NEW_NUMBER

This usually works EXCEPT right after resetting the sequence with the query above and before any new number was pulled from the sequence. The my query to find out what the next number would be shows "1" which is not necessarily correct.

What can I do to reliably determine the next number that nextval would produce if called without actually calling nextval (to not actually alter the sequence)?

Upvotes: 1

Views: 303

Answers (1)

klin
klin

Reputation: 121889

Use setval() instead of alter sequence..., e.g.

select setval('my_sequence', 110)

The last sequence value written to disk (last_value of pg_sequences) can be set only by nontransactional functions nextval() and setval(). After the sequence is restarted with alter sequence ... the last value is not saved until it is actually used by nextval() or set by setval(). This is because alter sequence ... may be rolled back.

Upvotes: 2

Related Questions