Re: Sequence moves forward when failover is triggerred

On 07/10/2012 04:23 PM, Andy Chambers wrote:
> On Tue, Jul 10, 2012 at 6:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Andy Chambers <achambers(at)mcna(dot)net <mailto: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
>

That approach does more accurately model a checkbook (predefined bunch
of numbered checks) but will potentially lead to some other problems.

If multiple users are interacting with the database and a transaction
rolls back you could still have a hole in your "checkbook." Depending on
your overall design you would have to either "void" that check or re-use
the now blank check. Non-sequential consumption of checks is a common
situation in real life as well, of course (spouses using different books
of checks from the same account, keeping "emergency" checks in a
purse/wallet, etc), so it's best to plan for it.

The stickier issue is queuing. You have multiple users and need to
ensure that you grab an unused check from the book but each concurrent
user needs to get a different check. "Select from checkbook where not
check_used order by check_no limit 1 for update" seems like a reasonable
approach but if two users run it simultaneously the first user will get
1 check and succeed while the second user will attempt to lock same
check record, block until the first user completes then recheck and find
the selected record no longer
meets the check_used criteria so the second user will see zero records
returned.

This site has one approach for dealing with the queuing situation using
advisory locks:
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Taking_first_unlocked_row_from_table

Cheers,
Steve