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.keler@lkmc.com.br
Cel: 11 9914-4030
Res: 11 3404-4632


2011/10/17 Tom Lane <tgl@sss.pgh.pa.us>

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