Re: Sequence moves forward when failover is triggerred

On Tue, Jul 10, 2012 at 6:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andy Chambers <achambers(at)mcna(dot)net> writes:
> > When testing the failover procedure, we noticed that when the new master
> > comes up, some sequences have moved forward (by between 30 and 40). I
> see
> > there's a "cache" option when creating the sequence but we're not using
> > that.
>
> > Is this to be expected?
>
> Yes. This is an artifact of an optimization that reduces the number of
> WAL records generated by nextval() calls --- server processes will write
> WAL records that say they've consumed multiple sequence values ahead of
> where they actually have.
>
> AFAICS this is not distinguishably different from the case where a
> transaction consumes that number of sequence values and then rolls back,
> so I don't see much wrong with that optimization.
>

OK Cool. Thanks for confirming.

I think I made a poor decision by having our application generate checkbook
numbers on demand using sequences. I've since realized (due to this and
other reasons like not being able to see what nextval() would return
without actually moving the sequence forward) that it would probably be
better to generate an entire checkbook's worth of numbers whenever the
checks are physically received from the bank. Then just have the app pull
the next available check.

Andy