Re: BUG #6258: Lock Sequence

I changed the logic of the trigger, I'm using before and taking the next_val
().
Thank you.

[]Laerson Keler
laerson(dot)keler(at)lkmc(dot)com(dot)br
Cel: 11 9914-4030
Res: 11 3404-4632

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:
> > 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
>