Re: Sequences - jumped after power failure

Cheers Tom.
I'll note that down under the section '...things to note should the server crash...' - just under the note that says '...get a UPS...'

On Tue, 2008-04-15 at 10:58 -0400, Tom Lane wrote:

Steve T <steve@retsol.co.uk> writes:
> I have a set of claims tables that cover the claim itself, the customer,
> contact points etc. Yesterday there was a power failure and the server
> suffered an immediate power outage. When the server came back,
> everything seemed fine, apart from the fact that the claim related
> sequences had all jumped and left a gap of 33 (last was 52 before power
> failure, next one allocated after power failure 85). This seems
> consistent across all the tables related to the claim (it may be across
> the   tables in the database - I haven't checked all of them as yet).

> Does this sound feasible and if so, what is the cause?

Yeah, this is intentional behavior designed to reduce the amount of disk
write traffic generated by nextval()s.  From a standing start, a
nextval() actually advances the sequence 33 times (1 + SEQ_LOG_VALS),
so that the next 32 nextval()s won't need to generate their own WAL
records.  I guess you must have crashed before that first nextval()
was able to commit its result into the database ...

			regards, tom lane

Steve Tucknott ReTSol Ltd DDI:         01323 488548 Mobile:     0773 671 5772