Skip site navigation (1) Skip section navigation (2)

Re: BUG #6258: Lock Sequence

From: Laerson keler <laerson(dot)keler(at)lkmc(dot)com(dot)br>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6258: Lock Sequence
Date: 2011-10-18 09:16:53
Message-ID: CAFk5V3NdPQ-M53TP+_4+TMppiEHR4g30+zwnEFSccJpaTyqZVw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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
>

In response to

pgsql-bugs by date

Next:From: Robert YoungDate: 2011-10-18 10:03:39
Subject: incompatible pointer type
Previous:From: Laerson kelerDate: 2011-10-18 09:15:09
Subject: Re: BUG #6258: Lock Sequence

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group