Thread: BUG #6258: Lock Sequence

The following bug has been logged online:

Bug reference:      6258
Logged by:          Laerson Keler
Email address:      laerson.keler@lkmc.com.br
PostgreSQL version: 8.4.9
Operating system:   Ubuntu 10.04 - Kernel 2.6.32-34
Description:        Lock Sequence
Details:

Dear Srs, good morning, I did the automatic update postgresql 8.4.9 and
a function stopped working, I used the option select for update
in a sequence and is now giving the following error "can not lock rows in
sequence ...", how can I enable the blocking of the sequence?
Already grateful for the attention.
Thank you.

"Laerson Keler" <laerson.keler@lkmc.com.br> writes:
> Dear Srs, good morning, I did the automatic update postgresql 8.4.9 and
> a function stopped working, I used the option select for update
> in a sequence and is now giving the following error "can not lock rows in
> sequence ...", how can I enable the blocking of the sequence?

Why did you do that, that is what were you trying to accomplish?  It
never did block nextval() on the sequence, for example.

            regards, tom lane

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.
Already grateful for the attention.
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:
> > Dear Srs, good morning, I did the automatic update postgresql 8.4.9 and
> > a function stopped working, I used the option select for update
> > in a sequence and is now giving the following error "can not lock rows in
> > sequence ...", how can I enable the blocking of the sequence?
>
> Why did you do that, that is what were you trying to accomplish?  It
> never did block nextval() on the sequence, for example.
>
>                        regards, tom lane
>

On 17-10-2011 12:52, Tom Lane wrote:
> "Laerson Keler"<laerson.keler@lkmc.com.br>  writes:
>> Dear Srs, good morning, I did the automatic update postgresql 8.4.9 and
>> a function stopped working, I used the option select for update
>> in a sequence and is now giving the following error "can not lock rows in
>> sequence ...", how can I enable the blocking of the sequence?
>
You can't. It was disabled [1].

> Why did you do that, that is what were you trying to accomplish?  It
> never did block nextval() on the sequence, for example.
>
Maybe he is using an old pgpool-II version?


[1]
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=21538377ee6a0ee91f756726bd8b3de6d19fd20a


--
    Euler Taveira de Oliveira - Timbira       http://www.timbira.com.br/
    PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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

On Mon, Oct 17, 2011 at 2:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> There really is not any way to generate guaranteed-hole-free sequences
> using sequence objects. =A0If you have to have that, I'd suggest locking
> the table against other writes and then fetching MAX(id) + 1. =A0It's not
> very fast, and it's not at all concurrent, but that's the price of
> ensuring no holes. =A0Personally I'd rethink how badly you need that
> property.
>

another option is to create a table to use as a sequence, and lock
that table everytime you need a new value... is not concurrent also,
but at least faster... unless i'm missing something

--=20
Jaime Casanova=A0 =A0 =A0 =A0=A0 www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n

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
>

good idea. I will try.
Thank you.

[]Laerson Keler
laerson.keler@lkmc.com.br
Cel: 11 9914-4030
Res: 11 3404-4632


2011/10/17 Jaime Casanova <jaime@2ndquadrant.com>

> On Mon, Oct 17, 2011 at 2:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > 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.
> >
>
> another option is to create a table to use as a sequence, and lock
> that table everytime you need a new value... is not concurrent also,
> but at least faster... unless i'm missing something
>
> --
> Jaime Casanova         www.2ndQuadrant.com
> Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n
>