Re: BUG #6258: Lock Sequence

Laerson keler <laerson.keler@lkmc.com.br> writes:
> 2011/10/17 Tom Lane <tgl@sss.pgh.pa.us>
>> "Laerson Keler" <laerson.keler@lkmc.com.br> writes:
>> Why did you do that, that is what were you trying to accomplish?  It
>> never did block nextval() on the sequence, for example.

> Tom Lane, good afternoon, I block the sequence not to miss the sequel, for
> it not to be skipped if the insert to fail. My logic involves two triggers,
> one before and one after. I give the first one in last_value select for
> update in the sequence and insert after I run a select next_val ('sequence')
> to place in the next issue, so the sequence in my table is no failure.

Well, that's a cute idea, but the fact is that it was always quite
unsafe because it had no interlock against nextval().  Moreover, you
still did not have a guarantee of no holes in the assigned ID values,
because the transaction could still fail after the AFTER trigger runs.

There really is not any way to generate guaranteed-hole-free sequences
using sequence objects.  If you have to have that, I'd suggest locking
the table against other writes and then fetching MAX(id) + 1.  It's not
very fast, and it's not at all concurrent, but that's the price of
ensuring no holes.  Personally I'd rethink how badly you need that
property.

            regards, tom lane