Re: Sequence moves forward when failover is triggerred - Mailing list pgsql-general

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

Andy Chambers <achambers@mcna.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