Re: BUG #6258: Lock Sequence

Laerson keler <laerson(dot)keler(at)lkmc(dot)com(dot)br> writes:
> 2011/10/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>> "Laerson Keler" <laerson(dot)keler(at)lkmc(dot)com(dot)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